Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 38.9Mb
PDF (A4) - 39.0Mb
PDF (RPM) - 38.1Mb
HTML Download (TGZ) - 10.8Mb
HTML Download (Zip) - 10.8Mb
HTML Download (RPM) - 9.5Mb
Man Pages (TGZ) - 210.9Kb
Man Pages (Zip) - 320.0Kb
Info (Gzip) - 3.5Mb
Info (Zip) - 3.5Mb
Excerpts from this Manual

5.4.5 The Slow Query Log

The slow query log consists of SQL statements that take more than long_query_time seconds to execute and require at least min_examined_row_limit rows to be examined. 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 be a time-consuming task. To make this easier, you can use the mysqldumpslow command to process a slow query log file and summarize its contents. See Section 4.6.8, “mysqldumpslow — Summarize Slow Query Log Files”.

The minimum and default values of long_query_time are 0 and 10, respectively. The value can be specified to a resolution of microseconds. For logging to a file, times are written including the microseconds part. For logging to tables, only integer times are written; the microseconds part is ignored.

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.

By default, the slow query log is disabled. To specify the initial slow query log state explicitly, use --slow_query_log[={0|1}]. With no argument or an argument of 1, --slow_query_log enables the log. With an argument of 0, this option disables the log. To specify a log file name, use --slow_query_log_file=file_name. To specify the log destination, use --log-output (as described in Section 5.4.1, “Selecting General Query Log and Slow Query Log Output Destinations”).

If you specify no name for the slow query log file, the default name is host_name-slow.log. The server creates the file in the data directory unless an absolute path name is given to specify a different directory.

To disable or enable the slow query log or change the log file name at runtime, use the global slow_query_log and slow_query_log_file system variables. Set slow_query_log to 0 (or OFF) to disable the log or to 1 (or ON) to enable it. Set slow_query_log_file to specify the name of the log file. If a log file already is open, it is closed and the new file is opened.

The server writes less information to the slow query log if you use the --log-short-format option.

To include slow administrative statements in the slow query log, enable the log_slow_admin_statements system variable. 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, enable the log_queries_not_using_indexes system variable. (Even with that variable enabled, the server does not log queries that would not benefit from the presence of an index due to the table having fewer than two rows.)

When queries that do not use an index are logged, the slow query log may grow quickly. It is possible to put a rate limit on these queries by setting the log_throttle_queries_not_using_indexes system variable. By default, this variable is 0, which means there is no limit. Positive values impose a per-minute limit on logging of queries that do not use indexes. The first such query opens a 60-second window within which the server logs queries up to the given limit, then suppresses additional queries. If there are suppressed queries when the window ends, the server logs a summary that indicates how many there were and the aggregate time spent in them. The next 60-second window begins when the server logs the next query that does not use indexes.

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 be enabled.

  2. The query must have taken at least long_query_time seconds, or log_queries_not_using_indexes must be enabled and the query used no indexes for row lookups.

  3. The query must have examined at least min_examined_row_limit rows.

  4. The query must not be suppressed according to the log_throttle_queries_not_using_indexes setting.

The log_timestamps system variable controls the time zone of timestamps in messages written to the slow query log file (as well as to the general query log file and the error log). It does not affect the time zone of general query log and slow query log messages written to log tables, but rows retrieved from those tables can be converted from the local system time zone to any desired time zone with CONVERT_TZ() or by setting the session time_zone system variable.

All log lines contain a timestamp.

The server does not log queries handled by the query cache.

By default, a replication slave does not write replicated queries to the slow query log. To change this, enable the log_slow_slave_statements system variable.

Passwords in statements written to the slow query log are rewritten by the server not to occur literally in plain text. See Section 6.1.2.3, “Passwords and Logging”.

When the slow query log is enabled, the server writes output to any destinations specified by the log_output system variable. If you enable the log, the server opens the log file and writes startup messages to it. However, further logging of queries to the file does not occur unless the FILE log destination is selected. If the destination is NONE, the server writes no queries even if the slow query log is enabled. Setting the log file name has no effect on logging if the log destination value does not contain FILE.

If the slow query log is enabled and the output destination includes FILE, each statement written to the log is preceded by a line that begins with a # character and has these fields (with all fields on a single line:

  • Query_time: duration

    The statement execution time in seconds.

  • Lock_time: duration

    The time to acquire locks in seconds.

  • Rows_sent: N

    The number of rows sent to the client.

  • Rows_examined:

    The number of rows examined by the optimizer.

Each statement written to the slow query log file is preceded by a SET statement that includes a timestamp indicating when the slow statement was logged (which occurs after the statement finishes executing).


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by lvaro G. Vicario on December 20, 2011
Syntax for /etc/my.cnf in Red Hat 9:

[mysqld]
long_query_time=1
log-slow-queries=/var/log/mysql/log-slow-queries.log

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 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.
  Posted by xinyuan yan on April 16, 2016
mysql5.7 has two .cnf files ,one is support-files/my-default.cnf and another is /etc/my.cnf ,actually /etc/my.cnf is an effective one ,here is my configuration
[mysqld]
slow_query_log=1
slow_query_log_file=/tmp/slow.log
long_query_time=2

when you execute 'service mysql restart' command

and it will automatic create the log which you have configuated.

And here is the file he has created.
-rw-r----- 1 mysql mysql 378 Apr 16 23:38 slow.log
  Posted by alpesh Shah on May 6, 2016
I tried giving permissions as mentioned above but it didn't work. I even tried putting slow log file in same location where mysql log file is generated with 755 permission to folder/file but still didn't work. Someone suggested to put log file in /var/lib/mysql where data file exist, but that also didn't work. I was able to open file with mysql user and even able to modify it but getting same error in mysql log file

[ERROR] mysqld: File '/var/lib/mysql/log-slow-queries.log' not found (Errcode: 13)

Finally I just mentioned file name without explicit path in my.conf file like below

slow-query-log-file=log-slow-queries.log

and I started getting log in file under same location i.e. /var/lib/mysql/log-slow-queries.log. It was really surprising but finally it worked like that only.
Sign Up Login You must be logged in to post a comment.