Summarizing the MySQL Slow Query log

The MySQL slow query log is great for being able to tell you which queries are intensive on the CPU’s, which is most likely impacting your websites performance. When looking at the slow query log, it can appear like its an unorganized mess of random queries. So how do you summarize this list so you can know what queries to start fixing first? Welcome mysqldumpslow!

By default, MySQL comes with a tool called mysqldumpslow. Its primary purpose is to parse the slow query log and group similar queries together. This allows you to quickly see which queries need your attention in the slow query log.

If the slow query log is not already enabled, follow the older article I have here:

Assuming the slow query log is already enabled, locate the slow query log by running:

[root@db01 ~]# grep slow-query-log-file /etc/my.cnf
slow-query-log-file = /var/lib/mysql/slow-log

Queries that return a lot of rows and run often are typically the cause of CPU contention within MySQL. These CPU heavy queries may be able benefit from either a LIMIT clause, a table index or just needs to be rewritten so it returns less rows.

To display the top 10 queries that returned the highest amount of rows, run:

[root@db01 ~]# mysqldumpslow -a -s c -t 10 /var/lib/mysql/slow-log

Sometimes you just want to see which queries are constantly in the slow query log. Many times this could be from something unexpected and easily fixable by the developers.

To display the top 10 queries without any other sorting options, run:

[root@db01 ~]# mysqldumpslow -a -s r -t 10 /var/lib/mysql/slow-log

The data returned will show:

Count - How many times the query has been logged
Time - Both the average time and the total time in the ()
Lock - Table lock time
Rows - Number of rows returned

Below is one entry returned by the mysqldumpslow command that should give you a rough idea what information will be presented to you:

[root@db01 ~]# mysqldumpslow -a -s c -t 10 /var/lib/mysql/slow-log
Count: 4201  Time=68.34s (278868s)  Lock=0.00s (0s)  Rows=998512 (4194748912), root[root]@localhost
  SELECT PL.pl_title, P.page_title
  FROM page P
  INNER JOIN pagelinks PL
  ON PL.pl_namespace = P.page_namespace
  WHERE P.page_namespace = N