The slow query log (or simply slow log) stores SQL statements that take too long to execute. To enable it, the slow_query_log variable or the --slow-query-log startup option can be set to 1. To explicitly disable the log, the --slow-query-log startup option must be set to 0. Without passing any arguments, the --slow-query-log startup option enables this log. As the slow_query_log variable is dynamic, the slow query log can be enabled or disabled at runtime.

The default filename of the slow query log is the server's hostname followed by the -slow.log suffix.

Just like the general query log, the slow query log can be a file, table, or both. The log output server variable can be used to specify if the log is a table or file. The allowed values to be set are FILE, TABLE, and NONE (which disables both the slow query log and the general query log). A combination of these values (separated by a comma) is allowed. The NONE value overrides other values.

For example, if the hostname is hal, hal-slow.log will be the default name for the slow query log. A different name can be specified using the slow_query_log_file server variable, or using the --slow-query-file option. In a replication environment, using the same file name for all logs is a good practice, because it may be necessary to execute the same scripts on all the servers.

If the slow query log is written into a table, the table is called slow_log and it can be found in the mysql database. In The general query log section in Chapter 2, Debugging, we have described in detail what can or cannot be done with the general_log table. These sections also apply to the slow_log table. As explained previously, in the slow query log we can find the queries that were executed too slowly. But what does this depend on? This depends on our workload, of course. For this reason, the rules that determine what queries are written in the slow query log depend on some of the server's system variables.

First, we may want to log queries that do not use indexes at all. To do this, we can set the log_queries_not_using_indexes variable (or the --log-queries-not-using-indexes startup option) to 1, as its default value is 0. This variable is dynamic, but only exists at the global level.

Even if a query uses indexes, we may want to log it if it exceeds a time-out. This can be done via the long_query_time variable, or the --long_query_time startup option. If the value is 0, there is no time-out and the slow queries are not logged. This value is expressed in seconds. Decimal values are allowed with a precision of microseconds (up to six decimal digits); however, for table-based logs, the decimal part is ignored. The query execution time is counted from the time the thread acquires the necessary locks. This means that, if a query takes too much time just because it is blocked by a slow query, it is not logged. This variable is dynamic and exists at both the global and session levels. This allows connections that execute more complex queries to set a greater time-out.

A query can also have other problems that we may want to log. The classes of problems that must be logged are listed in the log_slow_filter variable, separated by a comma. The allowed values are described as follows:

By default, the log_slow_filter variable contains all these values. It is dynamic and exists at both the session and global levels.

Even if a query matches the criteria defined by the log_queries_not_using_indexes and long_query_time system variables, the min_examined_row_limit variable can prevent it from being logged. Queries that examined fewer than the described number of rows are never written into the slow query log. A value of 0 allows all queries to be logged. The maximum value is platform-dependent, but is always very high. This variable is dynamic and exists at both the global and session levels. This setting is usually useful because, if a query examines only a few rows, it would not benefit from the use of an index in any case (and the optimizer takes this into account). If the query exceeds the time-out, it is still possible that it does not examine many rows, for example, if the query requires complex ordering or grouping operations, or if it calls slow functions (including stored functions).

Sometimes, logging all the queries that do not use indexes can be a heavy task for a busy server. For this reason, it is possible to limit the logging of such queries using the log_throttle_queries_not_using_indexes system variable. If the value of the system variable is 0 (the default), there is no limit. If its value is higher than 0, it will determine the number of queries that do not use indexes and that can be logged in a minute. When this limit is reached, further queries will not be logged. A minute after the last query that does not use indexes is logged, a summary of the suppressed queries is written into the log.

By default, administrative queries (such as CHECK TABLE or ANALYZE TABLE) are not written to the slow query log. To change this behavior, the log_slow_admin_statements variable can be set to 1. This variable is dynamic, but only exists at the global level.

In a replication environment, usually, slaves do not log replicated slow queries, because such queries can be found in the master's slow query log. Normally, a slow query is logged only if it was directly sent to the slave. However, this behavior can be changed by setting the log_slow_slave_statements variable to 1. This variable is dynamic, but only exists at the global level. If the workload on the master is heavy and the workload on the slaves is light, using the slow query log on a slave instead of the master could be an optimization. Of course, it will still be necessary to explicitly enable the slow query on a slave and disable it on the master.

To produce a less verbose slow query logfile, the --log-short-format startup option can be used. In the configuration files, it can be specified as log-short-format. This option also affects the binary log.

The counterpart of the --log-short-format startup option is log_slow_verbosity, which can be used to add information to the slow query logfile. It is a comma-separated list of values, where each value adds some information. The allowed values are described as follows:

To enable profiling and profiling_use_getrusage, it is necessary to use XtraDB. The default value is 'query_plan'. Both log_short_format and log_slow_verbosity affect the file-based slow query log but not the table.

To summarize, the following variables determine the queries that should be written in the slow query log:

The following variables can filter out the variables that match the previous criteria:

The following variables determine what information is logged (and the overhead caused by the slow query log):

Queries that examine tables with no rows or a single row are always excluded from the slow query log.

/usr/local/mysql/bin/mysqld, Version: 10.0.7-MariaDB-log (MariaDB Server). started with:
Tcp port: 0  Unix socket: (null)
Time                 Id Command    Argument.

# Time: 140116 11:19:05
# User@Host: root[root] @ localhost []
# Thread_id: 4  Schema: test  QC_hit: No
# Query_time: 0.059419  Lock_time: 0.000340  Rows_sent: 1  Rows_examined: 66620
SET timestamp=1389867545;
SELECT COUNT(*) FROM t
 WHERE a > b;

# Thread_id: 5  Schema: test  QC_hit: No
# Query_time: 0.230296  Lock_time: 0.000302  Rows_sent: 1  Rows_examined: 263337
SET timestamp=1389869887;
SELECT COUNT(*) FROM t WHERE a > b;