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:
Here’s the main commands I have discussed in this video:
Starting pt-stalk in foreground
1 |
# pt-stalk |
Starting pt-stalk in background with email notification:
1 |
# pt-stalk --daemonize --notify-by-email your@email |
Watching the level of Threads_running:
1 |
# mysqladmin ext -i1 | grep Threads_running |
Specifying a different Threshold (for Threads_running by default)
1 |
# pt-stalk --threshold=10 |
Number of cycles before collectors trigger:
1 |
# pt-stalk --cycles=5 |
Interval between the checks (0.1s in this case):
1 |
# pt-stalk --interval=.1 |
Using another status variable (e.g. Threads_connected):
1 |
# pt-stalk --variable=Threads_connected |
Using plugins – 1. slave-lag-plugin.sh
1 2 3 4 5 6 |
# pt-stalk --function=slave-lag-plugin.sh --variable=Seconds_Behind_Master --threshold=300 # cat slave-lag-plugin.sh trg_plugin() { mysql $EXT_ARGV -e "SHOW SLAVE STATUS\G" \ | grep Seconds_Behind_Master | awk '{if ($2 == "NULL") print 86400; else print $2}' } |
Using plugins – 2. vm-mon.sh (not a very good example, but it did the trick for me)
1 2 3 4 5 |
# ./pt-stalk --function=vm-mon.sh --variable=vmstat_blocked --threshold=1 --cycles=4 --interval=.01 --notify-by-email=aurimas@percona.com --collect-gdb # cat vm-mon.sh trg_plugin() { vmstat 1 2 | tail -1 | awk '{if ($2 > 1 && $1 < 1) print 2; else print 0}' } |
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):
1 |
# grep Threads_running TIMESTAMP-mysqladmin |
Here’s an example of a good sample:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
# grep Threads_running 2016_11_05_22_24_45-mysqladmin | Threads_running | 98 | | Threads_running | 110 | | Threads_running | 123 | | Threads_running | 131 | | Threads_running | 134 | | Threads_running | 139 | | Threads_running | 144 | | Threads_running | 165 | | Threads_running | 56 | | Threads_running | 5 | | Threads_running | 3 | | Threads_running | 5 | | Threads_running | 3 | | Threads_running | 3 | | Threads_running | 3 | | Threads_running | 3 | | Threads_running | 4 | ... |
To open the diskstats file, use pt-diskstats:
1 |
# pt-diskstats TIMESTAMP-diskstats |
To specify MySQL access, either define it in ~/.my.cnf or use:
1 2 |
# pt-stalk --<all-pt-stalk-options-first> -- -h mysqlhost -u user -ppassword # pt-stalk --<all-pt-stalk-options-first> -- --login-path=X |
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:
1 |
0 4 * * * /usr/bin/pt-stalk --no-stalk --iterations=1 --run-time=900 |
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