MySQL has a really useful feature that allows it to log slow queries where slow is a minimum time defined by you in micro seconds. It helps a lot is diagnosing website outages or slow responsiveness issues after the fact.
Unfortunately I couldn’t find any nice graphical tools for analysing these but there are a few command line tools:
mysqldumpslow
MySQL’s own tool, mysqldumpslow, which aggregates queries and allows you to sort them by: query time or average query time; lock time or average lock time; rows sent or average rows sent; or the number of queries.
Percona’s MySQL Slow Query Log Analyser
Dating from 2006, Percona’s Peter Zaitsev wrote about their own version of a slow query log analyser (local copy) which has given me good results. Note that their micro time patch has since been incorporated into MySQL mainstream.
One of the main differences over MySQL’s own version is that as well as printing the aggregated query (with number and string literals wildcarded), it also prints a real example of the query allowing a copy and paste to MySQL for execution with EXPLAIN.
Example output with query details redacted:
### 230 Queries
### Total time: 4708.948293, Average time: 20.4736882304348
### Taking 0.093420 to 203.693466 seconds to complete
### Rows analyzed 0 - 141008
SET timestamp=XXX;
SELECT ... FROM ... AS A
INNER JOIN ... AS C ON C.item_id = A.item_id
WHERE XXX AND C.item_lang = 'XXX' AND ...
ORDER BY CATALOG.item_sort LIMIT XXX;
SET timestamp=1348032761;
SELECT ... FROM ... AS A
INNER JOIN ... AS C ON C.item_id = A.item_id
WHERE 1 AND C.item_lang = '1' AND ...
ORDER BY C.item_sort LIMIT 1;