Inspecting MySQL micro-stalls with pt-stalk

In MySQL by Aurimas Mikalauskas0 Comments

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!

 

Share this Post

About the Author

Aurimas Mikalauskas

Facebook Twitter Google+

Linux hacker since the age of 0xE, deeply passionate about high performance and scalability. Former architect of scalable systems at Percona - largest independent MySQL and MongoDB consultancy company (for 9 years). Audiobook addict, piano & guitar virtuoso, scuba diver, wind surfer and a wannabe glider pilot. Learn more about the author.

Follow Speedemy

I believe that providing the best user experience is the key to business success. Big part of that is making online systems fast, stable and reliable. On Speedemy.com, I am sharing best practices and tested methods that I have used for the last 12 years working as an architect of scalable systems and database performance consultant.

If you too are looking to improve the speed and reliability of your system, let's stay in touch:

No spam, ever! Unsubscribe at any time. Powered by ConvertKit