Ever since Peter Z from Percona showed us the awesome that is the mk-query-digest tool, we’ve been using it quite a bit to help identify costly queries. It analyzes the MySQL slow query log and compiles a detailed report which includes tons of useful data – how many times was a particular query called? how much time did MySQL spend executing a particular query during the given timeframe? how long did the query take to execute on average? what about the worst case?
The report is output as a long long text file that you can page through and look at all the queries. All the data you need is there, but it’s a bit difficult to get to, especially if you want to compare multiple reports or if you want to aggregate over a period of time longer than the interval at which you run the reports (we’ve been running it every 15 minutes on one of our shards and are in the process of adding it to all servers).
Enter ishmael
Photo from raphie.
Luckily, the tool can be configured to write most of the data in the report to a database table. Where there’s a database, there’s a former tools developer eager to write a UI on top of it (me). After Timmy, our DBA, showed me a quick prototype of a page he put together for displaying the data, I took it and ran with it. The result is ishmael – a UI on top of mk-query-digest. The name comes from the tools purpose – to help hunt down “whale” queries.
For now, ishmael lets you sort the queries by 3 characteristics – the total amount of time MySQL spent executing the query, the number of times the query was actually executed, and the ratio between the two. It also displays the queries with some highlighting (done using a brutal regex) and lets you click through to see historical data (assuming you’ve run the report more than once) as well as a page that shows the EXPLAIN output for the query and the SHOW CREATE TABLE output for the tables involved (once again, traced back from their aliases using brutal regex hacks).
There are already a bunch of additional features in the pipeline – being able to configure ishmael to switch between different databases, better handling of historical data, etc. We hope other mk-query-digest users find the tool useful and can let us know how the tool can be made better. Patches are welcome.
The docs for mk-query-digest are on the maatkit toolkit website: http://www.maatkit.org/doc/mk-query-digest.html.
The source for ishmael, is on github: http://github.com/mihasya/ishmael
(click “Issues” to view the list of open tickets)