The Geek Diary

MySQL : What is the Slow Query Log and How to Control Where the Log is Stored and What is Logged

by

As the name indicates, the slow query log records queries that takes a long time to execute or is not using indexes and this may be slower than needed. The following general properties apply to the slow query log:

  • The query time is measured from after any initial table locks have been acquired.
  • Queries are written to the slow query log after all locks have been released. This reduces the time locks are held, but means that two queries requiring the same table locks may end up in the slow query log in opposite order.
  • The execution time measured for the query is the real-time (wall clock time) it takes to execute the query. So a query requiring just a little CPU time can still end up in the slow query log if the system is heavily loaded.

Enabling & Disabling Slow Query Log

How you enable and disable the slow query log depends on which version of MySQL you are using.

Note that per default queries executed through a slave thread will not be logged. To enable logging slave updates, use the log-slow-slave-statements option in the MySQL configuration file.

5.1 and Later

In MySQl 5.1 and later, you enable and disable the slow query log with the slow_query_log option. You can set this variable dynamically like:

SET GLOBAL slow_query_log = OFF;

The variable can only be set globally, so it will affect all connections.

5.0 and Earlier

If you are using MySQL 5.0 or earlier, you will have to set the log_slow_queries option in the MySQL configuration file. Optionally you can specify the file to log the slow queries to (default is host_name-slow.log in the data directory), for example:

[mysqld]
log_slow_queries

It requires a restart of MySQL before the change takes effect.

Controlling the Destination of log

5.1 and Later

You can control the destination of the log with the following options:

    slow_query_log_file: Set this option to the file name of the log file. The log file is in plain text, and you can use the mysqldumpslow script (see below) to generate summaries.
  • log_output: This option controls whether to log to a table and/or a file (see also the slow_query_log_file option above). When you have chosen to log to a table, the slow query log can be found in the table mysql.slow_log.
Note that the option also applies to the general query log.

You can change the value dynamically, and you can specify either a single value or a comma separated list of values, for example:

SET GLOBAL log_output = 'TABLE,FILE';

Allowed values are:

  • NONE
  • FILE
  • TABLE
  • TABLE,FILE

It is important to be aware of the restrictions that apply to the mysql.slow_log table. Some of the restrictions are:

  • CREATE TABLE, ALTER TABLE, and DROP TABLE are valid operations on a log table. For ALTER TABLE and DROP TABLE, the log table cannot be in use and must be disabled, as described later.
  • INSERT, DELETE, and UPDATE cannot be used on a log table. These operations are permitted only internally to the server itself.

These restrictions have implications if you, for example, try to reload the slow_log table as you have to explicitly disable both the general query log and the slow query log to be able to DROP (including dropping the mysql database) and/or CREATE the table, and you have to ensure that you do not try to inserts rows into the table while reloading.

5.0 and Earlier

In MySQL 5.0 and earlier you can specify the destination for the slow query log by specifying a file name to the log_slow_queries option, for example:

[mysqld]
log_slow_queries = /var/log/mysql/slow_queries.log

It is not possible to change the destination dynamically, so a restart of MySQL is required before the change takes effect.

Controlling the Timestamp Time Zone

In MySQL 5.7.2 and later, the time zone used for the timestamps in the Slow Query Log can be controlled using the log_timestamps option. It only affects queries written to a file based log. The allowed values are:

  • UTC
  • SYSTEM

The default in 5.7.2 and later is UTC. In earlier versions, the timestamps used the system time zone. The option also controls the time zone used for the timestamps in the General Query Log.

Controlling What is Logged

MySQL has several options to control what is included in the slow query log. The server uses the controlling parameters in the following order to determine whether to write a query to the slow query log:

  • The query must either not be executed by the SQL slave thread, or log-slow-slave-statements must be enabled.
  • The query must either not be an administrative statement, or log-slow-admin-statements must be enabled.
  • 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.
  • The query must have examined at least min_examined_row_limit rows.
Note: 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.

The options will be discussed in details below.

long_query_time

The long_query_time variable specifies the threshold in seconds. If a query takes longer than this, the query will be logged. The variable can be set at the session level, so for example in case you beforehand know a query will take a long time, you can set the threshold appropriately to avoid logging queries you already know cannot be made faster. Similarly, if you are in a part of the application that must be fast, you can lower the threshold.

In MySQL 5.1.21 and 5.5 and later, you can specify a value of 0 to log all queries, and microsecond resolution is supported. In earlier versions, the minimum value is 1 and only integers are supported.

log_queries_not_using_indexes

When setting log_queries_not_using_indexes to ON, all queries that do full table or index scans will be logged. When this option is enabled it is often an advantage to set the long_query_time very high so only queries not using indexes are logged.

Note that some queries inherently cannot avoid a table or index scan, for example: SELECT * FROM t1;

log_throttle_queries_not_using_indexes

When the log_queries_not_using_indexes option is enabled, the number of queries logged to the slow query log may be too much. The log_throttle_queries_not_using_indexes option can be used to throttle this by setting the maximum number of queries per minute to log for queries not using indexes. The default is 0 which means not to throttle. The option is available in 5.6.5/5.7 and later.

min_examined_row_limit

min_examined_row_limit is the minimum number of rows to examine before a query will be logged in the slow query log. Using this setting together with log_queries_not_using_indexes can prevent logging queries on very small tables where it does not matter that all rows are scanned.

log_slow_admin_statements

The log_slow_admin_statements controls whether administrative statements such as OPTIMIZE TABLE, ANALYZE TABLE and ALTER TABLE will be logged in the slow query log. In MySQL 5.6.10/5.5 and earlier this can only be set in the MySQL configuration file and requires a restart to take effect. In 5.6.11/5.7 and later, the option can be set dynamically.

log_slow_slave_statements

Per default queries executed through a slave, thread will not be logged. To enable logging slave updates, use the log_slow_slave_statements option. In MySQL 5.6.10/5.5 and earlier this can only be set in the MySQL configuration file and requires a restart to take effect. In 5.6.11/5.7 and later, the option can be set dynamically.

Monitoring

You can monitor the number of queries logged as a slow query due to taking longer than long_query_time seconds using the Slow_queries status variable, for example:

mysql> SHOW GLOBAL STATUS LIKE 'Slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 8     |
+---------------+-------+
1 row in set (0.00 sec)

You can also monitor this status variable using the Slow Queries graph in MySQL Enterprise Monitor (MEM). MySQL Enterprise Monitor also allows you to record slow queries using the Query Analyzer (QUAN).

mysqldumpslow

To help analyzing the slow query log, MySQL ships with the mysqldumpslow script. The slow query log records all of the queries that are fulfills the criteria specified by the slow query log settings. However in order to determine which queries require most urgent attention, it is an advantage to summarize the slow query log; for example, a query executed hundreds of times per second may be more important to optimize than a query only executed once daily even if the latter takes many times longer. By creating aggregate statistics for each query present in the slow query log, you can get the overview of how often a query appears in the slow query log, what the average query time is, etc. The mysqldumpslow script can provide these kinds of summaries.

You May Also Like