Analysing MySQL slow queries

Ouvir com webReader

While I’m engaged in my MySQL DBA mode, I usually come across the hard task of surfing around the slow query log.

Everybody trying to trace MySQL performance problems will hit the slow query log to keep track of those unperformant queries that seem to be hogging the system. The traditional tool to analyze the log is mysqldumpslow (provided with standard MySQL distribution) which aggregates the queries by pattern (fingerprint) and calculates some aggregated statistics. I personally find the tool very limited, and I don’t seem to be the one (see below).

Although since MySQL 5.1.6 it is possible to log these queries to tables, and better, with microsecond resolution (since 5.1.21), I didn’t tested it yet, however, nor didn’t read much about it (I wonder about it’s performance, though). Until I do, I’ll have to stick with my rudimentary patch to mysqldumpslow so that I can work on the queries and statistics in an SQL fashion. You can, for instance, sort the slow queries by whatever parameter you want, without parsing the whole log again.

Simply apply this mysqldump-db.patch against a copy of mysqldumpslow:

cp `which mysqldumpslow` /tmp/mysqldumpslowdb
wget -q -O - | patch /tmp/mysqldumpslowdb

Now, using the –sql option, you should be able to get the necessary SQL to work with:

/tmp/mysqldumpslowdb –sql /var/log/mysql-slow-queries.log | grep ^DUMPSLOWSQ | sed ’s,^DUMPSLOWSQL: ,,’ | mysql analysisdb

Replace analysisdb by whatever DB you want the results in (test is a good candidate|). This patch will allow you to dump the slow query log in MySQL statements (by parsing the prefix DUMPSLOWSQL) to be fed to the server and just that. You can then perform any kind of queries in it. The schemas should be self-explanatory, otherwise drop me a note!

Here is an example I usually run after loading the query log into MySQL - list the top 10 slow queries by total/aggregated time:

SELECT * from agg_queries ORDER BY t DESC LIMIT 10

With this result, you can easily get an example query from a specific fingerprint shown in this list. Just ask for:

SELECT * from queries WHERE agg_id = XXX ORDER BY t DESC LIMIT 10

Where XXX is one of the id’s shown in the first query. This way you get the slowest ten queries matching the previous aggregated fingerprint, so you can use an observed slow query for your analysis.

Of course, you should always check the queries used more often as well - because many many small queries also load the server:

SELECT * from agg_queries ORDER BY c DESC LIMIT 10

These examples should be enough to get you started. Enjoy!

If you’re looking for more advanced tools, have a look at mysql-tools provided by Jan Willamowius, or the famous maatkit by Baron Schwartz, namely mk-query-digest.

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

AddThis Social Bookmark Button

Leave a Reply