Pythian Group

At the 2008 MySQL Conference and Expo, The Pythian Group gave away EXPLAIN cheatsheets. They were very nice, printed in full color and laminated to ensure you can spill your coffee* on it and it will survive.

For those not at the conference, or those that want to make more, the file is downloadable as a 136Kb PDF at explain-diagram.pdf

* or tea, for those of us in the civilized world.

The “sla” in mysqlsla stands for “statement log analyzer”. This does a much better job than mysqldumpslow of analyzing your slow query log. In fact, you can sort by many different parameters — by sheer number of times the query shows up in the slow query log, by the total or average query time, by the lock time, etc. This is really good for weeding out pesky entries in the slow query log that you do not care about. In this case, our client was using log-queries-not-using-indexes, so there was a lot of junk in the slow query log as well (for instance, every time a mysqldump backup was run, the slow query log got plenty of entries). In this case, I’m using –slow to read the slow query log at the filename specified, –flat to flatten all the text to lowercase (basically case-insensitive matching) and –sort at to sort by “average time”.

> ./mysqlsla --flat --slow ~mysql/var/mysql-slow.log --sort at
Reading slow log '~mysql/var/mysql-slow.log'.
33274 total queries, 658 unique.
Sorting by 'at'.
__ 001 _______________________________________________________________________

Count         : 107 (0%)

Read the rest of this entry . . .

Today I’ve been checking out a new client environment. My mission is to figure out (cold) some of the characteristics of the queries being run, and particularly if they’re “good” or “bad”. In my arsenal of “tools I really want to check out” has been Maatkit’s Query Profiler.

They’re very different tools. Maatkit’s query profiler profiles a batch of queries, without granularity (at least not the way I ran it) to see what query is doing what. So I ran this against a production machine:
(I ran the query profiler for a while and then hit “enter” (apparently after about 17 minutes))

> ./mk-query-profiler --external --host localhost --user <user> --password <password>
 Press <enter> when the external program is finished
 +----------------------------------------------------------+
 |                     1 (1028.2091 sec)                    |
 +----------------------------------------------------------+

__ Overall stats _______________________ Value _____________
 Total elapsed time                     1028.209
 Questions                               882

Read the rest of this entry . . .