April 18, 2014

Tools

This page contains links to various tools we found helpful to use in practice.
Some tools are written by us, others by third parties, yet another ones may be shipped with your operating system you just need to find they are there.

Percona Toolkit
Percona Toolkit is a great set of tools for MySQL Performance Analyses and maintainence. Must know and have for any MySQL User.

mysqladmin
mysqladmin extended -i100 -r is very nice way to look how MySQL performance counters increment and it can tell you a lot about server run status.

mysqlreport
MySQL Report is a tool which would look at status variables same as mysqladmin extended but will group them together nicely and provide some hints on what are good and bad values.

vmstat
vmstat 10 is a great tool to run and understand what system load is looking up. It is not too detailed but great for the glance view.

mysqlsla
This is nice tool to analyze slow query log. It reads bunch of different log formats and has various stats, and it was there before pt-query-digest appeared.

innotop
Innotop is great top like tool for MySQL and is helpful even if you do not use Innodb tables. Very nice to watch what happens to server in the real time.

filefrag
filefrag is the fool to check fragmentation of the file. Ever wondered how fragmented your tablespace or table on the file level ?

iostat
iostat tool is a great help investigating/troubleshooting IO problems – it will show you amount of IOs happening per device, their type, request size as well as queue size and response time.

mpstat
mpstat is a great tool to see details on CPU usage – how are different CPUs are being used, how many interrupts the handle etc. Helps to better understand and fine tune the load.

oprofile
oprofile is usually used for advanced MySQL tuning when the load is CPU bound – it will tell you where exactly inside MySQL or Kernel CPU time is spent.

iohist
iohist is a little tool to show histogram for IO response time. The main use for it is to see how response time is split for read and write request rather than seeing the average reported by iostat. These can be very different.

drtace
Dtrace is a great tool for system level performance analyses, with a catch of it being unavailable on Linux which limits it practical use dramatically.

fincore
fincore is a tool to check how well given file is cached in operation system cache. Very helpful to analyze caching of MyISAM tables for example.

sysbench
sysbench is a tool to check performance of system and MySQL. Helpful to check different hardware and OS characteristic.