Modern systems are complicated beasts with lots of interdependent activities between threads, programs and kernels. Figuring out some problems is nearly impossible without building some time machine and crystal ball mix that tells exactly what happened.

Did your cgroups CPU limit inject a sleep in the middle of mmap_sem acquisition by ‘ps’? Is everyone waiting for a mutex that is held by someone who is waiting for a DNS response? Did you forget to lock in your libnss.* libraries into memory and hence ended up stalling in unexpected place under memory pressure?

I’ve grabbed Brendan Gregg‘s offcpu profiler, gutted it to the point where all it does is record longest waits per stack trace, as well as timestamp of the longest wait.

Some debugging sessions that would’ve taken hours, days or weeks before now are few minute endeavors. It is still quite hacky, so …

When trying to do some Linux tuning for MySQL, there are a few options that will greatly influence the speed of MySQL.  Below are some of the most important of these settings to help you get started.

Swappiness

The first thing to look at is what swappiness is set to.  This will determine the tendency of the kernel to swap out memory pages.  In may cases, you will want to set this to “1” to keep the swapping to a minimum.  A value of “0” will disable it entirely.

You can determine the current value with the following command:

cat /proc/sys/vm/swappiness

If this is not set to “1”, you should consider making the change by using one of the following options:

# Make sure you are root and set swappiness to 1 
echo 1 > /proc/sys/vm/swappiness

# Or, you can use sysctl to do the same sysctl 
vm.swappiness vm.swappiness = 1

If the change helps, you will want to …

Why another article on this Marco?

Deadlocks is a topic covered many times and with a lot of articles on the web, also from Percona.
I suggest you review the reference section for articles on how to identify Deadlocks and from where they are generated.
So why another article?
The answer is that messages like the following are still very common:

User (John): “Marco our MySQL is having problems”
Marco: “Ok John what problems. Can you be a bit more specific?”
John: “Our log scraper is collecting that MySQL has a lot of errors”
Marco: “Ok can you share the MySQL log so I can review it?”
John: “Errors are in the application log, will share one application log”

Marco reviews the log and in it he founds:

“ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction”

Marco reaction is: "Oh my ..." 

…

Why another article on this Marco?

Deadlocks is a topic covered many times and with a lot of articles on the web, also from Percona.
I suggest you review the reference section for articles on how to identify Deadlocks and from where they are generated.
So why another article?
The answer is that messages like the following are still very common:

User (John): “Marco our MySQL is having problems”
Marco: “Ok John what problems. Can you be a bit more specific?”
John: “Our log scraper is collecting that MySQL has a lot of errors”
Marco: “Ok can you share the MySQL log so I can review it?”
John: “Errors are in the application log, will share one application log”

Marco reviews the log and in it he founds:

“ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction”

Marco reaction is: "Oh my ..." 

…

Yves Trudeau and Francisco Bordenave, MySQL experts at Percona, recently published a three-part post: (1) Give Love to Your SSDs – Reduce innodb_io_capacity_max!; (2) InnoDB Flushing in Action for Percona Server for MySQL; (3) Tuning MySQL/InnoDB Flushing for a Write-Intensive Workload. It’s a fantastic read from start to finish, and it made me realize: dirty pages sound bad, but they are good. I suspect the opposite (“dirty pages are not good”) is a misconception due to an incomplete picture. Let’s complete the picture.

Yves Trudeau and Francisco Bordenave, MySQL experts at Percona, recently published a three-part post: (1) Give Love to Your SSDs – Reduce innodb_io_capacity_max!; (2) InnoDB Flushing in Action for Percona Server for MySQL; (3) Tuning MySQL/InnoDB Flushing for a Write-Intensive Workload. It’s a fantastic read from start to finish, and it made me realize: dirty pages sound bad, but they are good. I suspect the opposite (“dirty pages are not good”) is a misconception due to an incomplete picture. Let’s complete the picture.

Back to part 1

Now that we had seen how to setup our environment with PXC8 is time to see how our setup will behave and what can be done in case of issues.

We will now analyse the following common situations:
- Put a node in maintenance or remove a node and insert it back
- node crash
- network failure
- proxysql node crash

The following tests are done using a java application connecting using straight JDBC connection and no connection pooling. I choose to use that more than a …

Back to part 1

Now that we had seen how to setup our environment with PXC8 is time to see how our setup will behave and what can be done in case of issues.

We will now analyse the following common situations:
- Put a node in maintenance or remove a node and insert it back
- node crash
- network failure
- proxysql node crash

The following tests are done using a java application connecting using straight JDBC connection and no connection pooling. I choose to use that more than a …

In real life, there are frequent cases where getting a running application to work correctly is strongly dependent on consistent write/read operations. This is no issue when using a single data node as a provider, but it becomes more concerning and challenging when adding additional nodes for high availability and/or read scaling.

In the MySQL dimension, I have already described it here in my blog Dirty Reads in High Availability Solution.

We go from the most loosely-coupled database clusters with primary-replica async replication, to the fully tightly-coupled database clusters with NDB Cluster (MySQL/Oracle).

Adding components like ProxySQL to the architecture can, from one side, help in improving high availability, and from the other, it can amplify and randomize the negative effect of a …

In real life, there are frequent cases where getting a running application to work correctly is strongly dependent on consistent write/read operations. This is no issue when using a single data node as a provider, but it becomes more concerning and challenging when adding additional nodes for high availability and/or read scaling.

In the MySQL dimension, I have already described it here in my blog Dirty Reads in High Availability Solution.

We go from the most loosely-coupled database clusters with primary-replica async replication, to the fully tightly-coupled database clusters with NDB Cluster (MySQL/Oracle).

Adding components like ProxySQL to the architecture can, from one side, help in improving high availability, and from the other, it can amplify and randomize the negative effect of a …