Slow Query Log Overview

The slow query log is a record of SQL queries that took a long time to perform.

Activating the Slow Query Log

The slow query log is disabled by default. To enable it, set the slow_query_log system variable to 1. The log_output server system variable determines how the output will be written, and can also disable it (see Writing logs into tables for details). By default, the log is written to file, but it can also be written to table. The name of the file is determined by the --slow_query_log_file=file_name option, by default host_name-slow.log, while the table used is the slow_log table in the mysql database.

What's in the Slow Query Log

A slow query is defined as a query that takes longer than long_query_time to run, by default 10 seconds. Microseconds are allowed for logging into file, but not for logging into a table. This value can be adjusted dynamically.

By default only non-administrative statements are logged, as well as queries that use indexes. It can be particularly useful to include queries without indexes in the log (regardless of time taken), and this is set with the log_queries_not_using_indexes setting. Queries on tables containing 0 rows or 1 row will not be logged.

Slow administrative queries, which include ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, and REPAIR TABLE statements, can also be stored by using the --log-slow-admin-statements setting.

The log contains SQL queries that took longer than long_query_time seconds to perform, by default ten seconds, as well as queries that had to examine more than min_examined_row_limit rows. Time to acquire locks is not included in this calculation, only time to resolve the query.

A number of variables can control what's written to, as well as the frequency, of the slow query log. log_slow_rate_limit can allow a fraction of the queries to be logged, reducing excessive I/O or overly large log files. log_slow_verbosity can control detail of information written to the log, while log_slow_filter can limit which queries to log.

Note that, if your queries contain user's passwords, the slow query log may contain passwords too. Thus, it should be protected.

Viewing the Slow Query Log

Slow query logs written to file can be viewed with any text editor, or you can use the mysqldumpslow tool to ease the process by summarizing the information.

Queries that you find in the log are key queries to try to optimize by constructing a more efficient query or by making better use of indexes.

For queries that appear in the log that cannot be optimized in the above ways, perhaps because they are simply very large selects, due to slow hardware, or very high lock/cpu/io contention, using shard/clustering/load balancing solutions, better hardware, or stats tables may help to improve these queries.

Slow query logs written to table can be viewed by querying the slow_log table.

Variables related to the slow query log

• slow_query_log - enable/disable the slow query log
• log_output - how the output will be written
• slow_query_log_file - name of the slow query log file
• long_query_time - time in seconds/microseconds defining a slow query
• log_queries_not_using_indexes - whether to log queries that don't use indexes
• log-slow-admin-statements - whether to log certain admin statements
• min_examined_row_limit - minimum rows a query must examine to be slow
• log_slow_rate_limit - permits a fraction of slow queries to be logged
• log_slow_verbosity - amount of detail in the log
• log_slow_filter - limit which queries to log

Example

SELECT * FROM mysql.slow_log\G
...
*************************** 2. row ***************************
    start_time: 2014-11-11 07:56:28.721519
     user_host: root[root] @ localhost []
    query_time: 00:00:12.000215
     lock_time: 00:00:00.000000
     rows_sent: 1
 rows_examined: 0
            db: test
last_insert_id: 0
     insert_id: 0
     server_id: 1
      sql_text: SELECT SLEEP(12)
     thread_id: 74
...