5.4.4 The Slow Query Log

The slow query log consists of SQL statements that took more than long_query_time seconds to execute. The minimum and default values of long_query_time are 1 and 10, respectively.

By default, administrative statements are not logged, nor are queries that do not use indexes for lookups. This behavior can be changed using --log-slow-admin-statements and --log-queries-not-using-indexes, as described later.

The time to acquire the initial locks is not counted as execution time. mysqld writes a statement to the slow query log after it has been executed and after all locks have been released, so log order might differ from execution order.

To enable the slow query log, start mysqld with the --log-slow-queries[=file_name] option.

If the slow query log file is enabled but no name is specified, the default name is host_name-slow.log and the server creates the file in the same directory where it creates the PID file. If a name is given, the server creates the file in the data directory unless an absolute path name is given to specify a different directory.

To include slow administrative statements in the statements written to the slow query log, use the --log-slow-admin-statements server option. Administrative statements include ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, and REPAIR TABLE.

To include queries that do not use indexes for row lookups in the statements written to the slow query log, use the --log-queries-not-using-indexes server option. See Section 5.1.3, “Server Command Options”. When such queries are logged, the slow query log may grow quickly.

The server uses the controlling parameters in the following order to determine whether to write a query to the slow query log:

  1. The query must either not be an administrative statement, or --log-slow-admin-statements must have been specified.

  2. The query must have taken at least long_query_time seconds, or --log-queries-not-using-indexes must have been specified and the query used no indexes for row lookups.

The server does not write queries handled by the query cache to the slow query log, nor queries that would not benefit from the presence of an index because the table has zero rows or one row.

Replication slaves do not write replicated queries to the slow query log, even if the same queries were written to the slow query log on the master. This is a known issue. (Bug #23300)

The slow query log should be protected because logged statements might contain passwords. See Section, “Passwords and Logging”.

The slow query log can be used to find queries that take a long time to execute and are therefore candidates for optimization. However, examining a long slow query log can become a difficult task. To make this easier, you can process a slow query log file using the mysqldumpslow command to summarize the queries that appear in the log. See Section 4.6.8, “mysqldumpslow — Summarize Slow Query Log Files”.

User Comments
  Posted by Andrea Gangini on February 8, 2005
Please note that the mysql slow query log will not show the SQL of your slow queries if your application uses prepared statement.
You may want to turn on General Query Log instead.
  Posted by Karen Owen on March 30, 2006
Here are instructions on getting mysqldumpslow to work in your environment if you have my.cnf in a non-standard (eg, non /etc) locale. Please note this is for Unix only.

1. Make sure it can find perl in /usr/local/bin/perl
2. Make sure you've got the slow log running first
3. Copy the slow log to datadir and name it specifically: servername-slow.log (eg, servername-slow.log). This assumes your actual slowlog is located somewhere else. If it is located by default in datadir then you still must make a copy of it, named hostname-slow.log in datadir directory.
4. Make a copy of mysqldumpslow in the $MYSQL_HOME/bin directory, and name it: mysqldumpslow_new and be sure it is chmod 750
5. Edit the mysqldumpslow_new as described in the next item.
6. Assuming you don't place your server's my.cnf in /etc, you must tell my_print_defaults where datadir and basedir are because they can't be set directly in mysqldumpslow nor can they be passed as a parameter on the command line to mysqldumpslow.
This is done by altering the command line option to my_print_defaults, the results of which are fed into the mysqldumpslow perl script to establish datadir, basedir, and a few other variables. The following line in
mysqldumpslow_new should be changed.
Change from:
my $defaults = `my_print_defaults mysqld`;

my $defaults = `my_print_defaults -c /location/where/config_file_lives/my.cnf mysqld`;

Make sure the my.cnf pointed to in the -c option has datadir and basedir set under [mysqld] section.
7. To run the program now, simply type:

./mysqldumpslow_new -s c -t 3

8. You have now displayed output from the slow query log telling you the top 3 slow queries on the system.
  Posted by Christopher Egner on July 19, 2006
Andrea Gangini posted that slow queries made via prepared statements do not post to the slow query log. This is not exactly true.

In the case of MySQL 5.0.22 when accessed through JDBC (Java 1.5.0), slow queries constructed via PreparedStatement always appear in the slow query log. Slow queries constructed normally (via Statement), sometimes appear. I suspect that PreparedStatements bypass the query cache and so are always reported in the log whereas regular Statements are checked against the query cache. If the results are taken from the query cache and not by accessing the tables, the query does not appear in slow query log. Testing regular Statements with the SQL_CACHE and SQL_NO_CACHE hints seems to confirm this. (Remember MySQL does not expend much effort trying to recognise queries it's already seen - if the query strings aren't exactly the same, don't expect to get help from the query cache.)

  Posted by lvaro G. Vicario on December 20, 2011
Syntax for /etc/my.cnf in Red Hat 9:


You must create the file manually and change owners this way:

mkdir /var/log/mysql
touch /var/log/mysql/log-slow-queries.log
chown mysql.mysql -R /var/log/mysql

  Posted by Dan Franklin on December 8, 2008
If you are loading a large database dump created using mysqldump in the normal way, be aware that if an insert statement takes longer than long_query_time, the ENTIRE huge insert statement (with all its individual rows) will be logged to the slow query file. This can significantly extend the time it takes to load the database.
  Posted by Roel Van de Paar on December 8, 2008
An example:

log-slow-queries = slow.log
long_query_time = 20

- The first line under [mysqld] turns on slow query log and logs all slow queries to slow.log in the MySQL data directory.
- The second line indicates that any queries that took more than 20 seconds to execute need to be logged.
- The last line tells MySQL to log *any* queries that do not use indexes regardless of the setting in the second line.

If you like you can set the long_query_time very high so that only the queries that do not use indexes are logged (and no 'general' slow queries).
  Posted by Robert Brisita on February 20, 2015
Updated example for 2015 MySQL 5.6:

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 10
log_queries_not_using_indexes = 1

Change permissions so that mysqld can write to the specified log file. Giving write permissions to the 'other' group should suffice.
Sign Up Login You must be logged in to post a comment.