Very often MySQL is much less stable than we realize. In this video I explain how to detect and dianose these MySQL stalls that last for 5-10 seconds or more.

The reason nobody knows about these issues is that they’re:
a) rare
b) intermittent
c) monitoring software can’t catch them

You can use pt-stalk to detect and diagnose such issues. All of the tools I have mentioned in this video are part of Percona Toolkit (no wonder they all start with PT), you can find the toolkit here:

Percona Toolkit

Here’s the main commands I have discussed in this video:

Starting pt-stalk in foreground

Starting pt-stalk in background with email notification:

Watching the level of Threads_running:

Specifying a different Threshold (for Threads_running by default)

Number of cycles before collectors trigger:

Interval between the checks (0.1s in this case):

Using another status variable (e.g. Threads_connected):

Using plugins – 1. slave-lag-plugin.sh

Using plugins – 2. vm-mon.sh (not a very good example, but it did the trick for me)

Notice that this last eample also enables gdb collector – that’s because in this specific situation that was exactly what I needed.

To sift through the collected data, use pt-sift, less (I use less most of the time) or me if you need some help.

To verify that the sample is good (i.e. Threads_running was indeed high):

Here’s an example of a good sample:

To open the diskstats file, use pt-diskstats:

To specify MySQL access, either define it in ~/.my.cnf or use:

By the way, sometimes it’s a good idea to use pt-stalk to collect some data when you are not around the system. Here’s how to collect 15 minutes worth of performance data with cron’s help:

Just be sure to remove it from the crontab the next day 😉

I hope you will enjoy the video. Thanks a lot for stopping by!