≡ Menu

How to enable slow query logging in MySQL?

MySQL comes with a built in feature to enable slow query logging. This feature takes in a few arguments and logs all queries that take more than a threshold number of seconds. For example, you can configure MySQL to log all queries that take more than 5 seconds to complete. This is a very useful debugging tool. You can figure out the bottlenecks in your database and can figure out solutions to these bottlenecks, like adding more indexes, adding multi-column indexes, changing the way your queries do joins and so on.

To optimize MySQL queries, you could read this article.

To enable slow query logging, first find your my.cnf file:

mysql --help | grep my.cnf

This outputs:

/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf

So MySQL first checks /etc/mysql/my.cnf and then /etc/my.cnf and then my.cnf in the home directory. On my system, the file is located in the location /etc/my.cnf.

Now, open the my.cnf file:

vi /etc/my.cnf

Then add the following lines to the file:

slow-query-log = 1
long_query_time = 5
slow-query-log-file = /var/log/slowqueries
log-queries-not-using-indexes

The above statements will enable logging of all queries that take more than 5 seconds to the file /var/log/slowqueries. Each log entry contains the user, the host, and the query that took more than 5 seconds to complete.

Here is an example:

/usr/sbin/mysqld, Version: 5.1.63-cll (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: (null)
Time                 Id Command    Argument
# Time: 120918  6:26:36
# User@Host: dbuser@localhost []
# Query_time: 30.444010  Lock_time: 0.000062 Rows_sent: 60412  Rows_examined: 120832
use database;
SET timestamp=1347963996;
SELECT DISTINCT domain FROM emails WHERE email="support@namecheap.com";

The bolded lines in the above log entry shows that the query took about 6 and a half seconds and the query was just a SELECT DISTINCT query.

Note that you should use the slow query logging feature only for debugging. Keeping it on just wastes resources. So I suggest that you turn it on for a new site, keep it on for 15 days and then disable it.

The command mysqldumpslow

MySQL comes with a way to parse and read the slow queries log file which is called mysqldumpslow. It takes a number of parameters which you can see on the man page:

man mysqldumpslow

To output the slowest 5 queries from the slow queries log file, use the following command:

mysqldumpslow -s t -t 5 /var/log/slowqueries

The -s t argument instructs the mysqldumpslow to sort the queries by their average execution time and the -t 5 argument says that it should display only the top 5 queries.

This command can take a number of other arguments from which the -g option is quite useful:

-g  - This argument displays only those queries that match the pattern (grep style)

This was a tutorial on how to log slow queries using MySQL and how to parse and read the slow queries log file. I hope this was beneficial to you in your endeavors on optimizing your MySQL database performance!

{ 0 comments… add one }

Leave a Comment