I have written about Maatkit in the past, and more specificallyÂ how to use ‘mk-query-digest’. Â Development on Maatkit has stopped at this point and you should look to use the Percona Toolkit for Mysql going forward.
We use ‘pt-query-digest’ on a regular basis on our servers in order to profile running Mysql instances during periods of high load and at times when general query profiling is required. Â It appears as though there have been some changes to way in which the script works during the transition from ‘mk-query-digest’ to ‘pt-query-profiler’
In order to use it at this point you should use the following example syntax:
# pt-query-digest –user user_name –password pass_word –processlist localhost –interval 0.01 –run-time 10m
You will also notice that there is a new command line paramater ‘–run-time’ that is used to determine how long you would like the profiler to run before producing a report, in this case I would like to run the profiler for 10 minutes.
The output is also slightly different in that the summery report that was normally printed out at the end of the report has been moved toward the beginning of the report as well.
One of the tools that is included with Maatkit is called ‘mk-query-digest’.Â If you have Mysql slow loggingÂ enabled, this perl script can me used to examine these entries, and provide a very useful and user friendly report that will help you understand the queries that are exceeding your current slow log threshold. You can then take the results and determine whether or not you have opportunity to do some schema and/or query optimization.
In order to get the ball rolling all you need to do is execute the following command:
‘perl /path/to/mk-query-digest /path/to/slow.log’
Once you do you will be presented with a report similar to the following:
# 200ms user time, 10ms system time, 9.59M rss, 11.17M vsz
# Overall: 199 total, 8 unique, 0.92 QPS, 3.44x concurrency ______________
#Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â totalÂ Â Â Â minÂ Â Â Â maxÂ Â Â Â avgÂ Â Â Â 95%Â stddevÂ median
# Exec timeÂ Â Â Â Â Â Â Â Â Â 745sÂ Â Â Â Â 1sÂ Â Â Â 15sÂ Â Â Â Â 4sÂ Â Â Â Â 9sÂ Â Â Â Â 3sÂ Â Â Â Â 3s
# Lock timeÂ Â Â Â Â Â Â Â Â Â Â 4msÂ Â Â Â Â Â 0Â Â 159usÂ Â Â 21usÂ Â Â 66usÂ Â Â 18usÂ Â Â 16us
# Rows sentÂ Â Â Â Â Â Â Â 16.35MÂ Â Â Â Â Â 0 381.59kÂ 84.16k 298.06kÂ 81.83kÂ 76.03k
# Rows examÂ Â Â Â Â Â Â Â 16.35MÂ Â Â Â Â Â 0 381.59kÂ 84.16k 298.06kÂ 81.83kÂ 76.03k
# Time rangeÂ Â Â Â Â Â Â 2010-03-04 18:32:44 to 2010-03-04 18:36:21
# bytesÂ Â Â Â Â Â Â Â Â Â Â Â 55.53kÂ Â Â Â Â Â 6Â Â 7.67kÂ 285.75Â 313.99Â 519.23Â 271.23
Maatkit is a group of perl scripts that provide advanced methods ofÂ administration for Mysql.
Here are a few of the more useful things that Maatkit allows you do to:
- Monitor MySQL replication delay
- Make a MySQL slave server lag behind its master.
- Ensuring slaves have the same data as masters
- Find duplicate indexes and foreign keys on MySQL tables.
- Execute SQL statements and print statistics.
- Execute queries on multiple servers and check for differences.
Here is the link to the Maatkit website which provides more details on each of the scripts.Â I will be providing some real world examples of Maatkit usage in another post.