Tools for parsing a slow query log

Issue

How can I evaluate a slow query log?

Resolution

One serious performance-impacting area on websites is queries, because slow queries cause slow page loads. This article provides several methods that you can use to log and examine your website's slow queries.

Enable logging of slow queries

On Acquia Cloud, queries running longer than one second are logged by default. Read about downloading a slow query log in our documentation. Otherwise, refer the site's database administrator to MySQL's documentation regarding slow query logging.
 

Typical slow query log entry

This is an example of a typical entry from a MySQL slow query log:

# Time: 130323  8:41:20
# User@Host: username[username] @ database-1234.prod.hosting.acquia.com [127.0.0.1]
# Thread_id: 738333  Schema: databasename  Last_errno: 0  Killed: 0
# Query_time: 1.459942  Lock_time: 0.000000  Rows_sent: 0  Rows_examined: 0  Rows_affected: 1  Rows_read: 0
# Bytes_sent: 11  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0
# InnoDB_trx_id: 14AE3A4
use databasename;
SET timestamp=1364028080;
INSERT INTO semaphore (name, value, expire) VALUES ('variable_init', '2082304334514d6aaf7a92c8.53638468', '1364028080.4921');

Lines beginning with the pound sign (#) provide meta information about a query, including when the query was logged, the user that executed the query, and the time it took for the query to complete. After the meta information, the log might include an optional use database statement and an optional set timestampquery, and then the slow query itself.
 

Analyze logs using Percona Toolkit for MySQL

The Percona Toolkit for MySQL (formerly known as Maatkit) is a collection of useful command-line utilities for database administration. It includes the pt-query-digest utility, which can produce a summary of the entries in a slow query log. You can download this utility individually using the following command:

wget http://percona.com/get/pt-query-digest

In order to execute the script, set the executable bit on the file:

chmod +x ./pt-query-digest

Finally, run the tool with the slow query log as an argument, being sure to redirect its output to a separate file:

./pt-query-digest /path/to/slow-query-log.sql > digest-output.txt

By default, the digest produced by pt-query-digest will have a summary of the top 5 to 10 slowest queries based on the total response time. The summary table shows how many times the query was performed and the average time per query. Following the summary are detailed statistics of each slow query, with representative examples of the queries.

 

Review slow query logs in other ways

The MySQL server installation includes a rudimentary utility, mysqldumpslow , that you can use to review slow query logs.

There is also a MySQL slow query parser on GitHub that converts your slow query logs into a more usable format.