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
Continue reading →