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.
Percona has recently released some of their performance patches for Mysql 5.1. Previous to these patches were only available for the 5.0.x codebase.
You can read more about it here. The link also provides from interesting benchmarks as well, pitting these patches vs the standard storage engine setup.
To be clear, these patches work against the built in version of Innodb with comes standard with the Mysql server.
You can xtradb if you wish to use some of the Percona patches that pertain to the Innodb plugin that is offered by Innobase
While doing research into poor write performance with Oracle I discovered that the server was using the LSI SAS1068E. We had a RAID1 setup with 300GB 10K RPM SAS drives. Google provided some possible insight into why we the write performance was so bad(1 2). The main problem with this card is that there is no battery backed write cache. This means that the write-cache is disabled by default. I was able to turn on the write cache using the LSI utility.
This change however did not seem to any difference on performance. At this point I came to the conclusion that the card itself is the blame. I believe that this is an inexpensive RAID card that is good for general use of RAID0 and Raid1, however for anything were write throughput is important, it might be better the spring for a something a little bit more expensive.
When it was all said and done we ended up replacing all the these LSI cards with Dell Perc 6i cards. These cards did come battery backed…which allowed us to then enable the write cache, needless to say the performance improved significantly.
Anyone looking for a free tool to monitor Mysql, should have a look at kontrollbase. I have contributed a few patches to Matt Reid and the project over the past few months. I am currently using it to monitor several Mysql version 5.x sevrers. It is a good alternative to the Mysql Enterprise Dashboard tool that Mysql offers.