Slow Query Log Overview

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

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

The number of rows affected by the slow query are also recorded in the slow query log.

Enabling the Slow Query Log

The slow query log is disabled by default.

To enable the slow query log, set the slow_query_log system variable (or, from MariaDB 10.11, log_slow_query) to 1. It can be changed dynamically with . For example:

It can also be set in a server in an prior to starting up the server. For example:

Configuring the Slow Query Log Filename

By default, the slow query log is written to ${hostname}-slow.log in the directory. However, this can be changed.

One way to configure the slow query log filename is to set the system variable (or, from , ). It can be changed dynamically with . For example:

It can also be set in a server in an prior to starting up the server. For example:

If it is a relative path, then the is relative to the directory.

However, the system variable can also be an absolute path. For example:

Another way to configure the slow query log filename is to set the option, which configures MariaDB to use a common prefix for all log files (e.g. slow query log, , , , etc.). The slow query log filename will be built by adding -slow.log to this prefix. This option cannot be set dynamically. It can be set in a server in an prior to starting up the server. For example:

The cannot be an absolute path. The log file name is relative to the directory.

Choosing the Slow Query Log Output Destination

The slow query log can either be written to a file on disk, or it can be written to the table in the database. To choose the slow query log output destination, set the system variable.

Writing the Slow Query Log to a File

The slow query log is output to a file by default. However, it can be explicitly chosen by setting the system variable to FILE. It can be changed dynamically with . For example:

It can also be set in a server in an prior to starting up the server. For example:

Writing the Slow Query Log to a Table

The slow query log can either be written to the table in the database by setting the system variable to TABLE. It can be changed dynamically with . For example:

It can also be set in a server in an prior to starting up the server. For example:

Some rows in this table might look like this:

See for more information.

Disabling the Slow Query Log for a Session

A user can disable logging to the slow query log for a connection by setting the system variable (or, from , ) to 0. For example:

Disabling the Slow Query Log for Specific Statements

It is possible to disable logging to the slow query log for specific types of statements by setting the system variable. This option cannot be set dynamically. It can be set in a server in an prior to starting up the server. For example:

Configuring the Slow Query Log Time

The time that defines a slow query can be configured by setting the system variable (or, from , ). It uses a units of seconds, with an optional milliseconds component. The default value is 10. It can be changed dynamically with . For example:

It can also be set in a server in an prior to starting up the server. For example:

Logging Queries That Don't Use Indexes

It can be beneficial to log queries that don't use indexes to the slow query log, since queries that don't use indexes can usually be optimized either by adding an index or by doing a slight rewrite. The slow query log can be configured to log queries that don't use indexes regardless of their execution time by adding the option "not_using_index" to or setting the system variable to 1. It can be changed dynamically with . Some examples:

It can also be set in a server in an prior to starting up the server. For example:

As a significant number of queries can run quickly even without indexes, you can use the system variable (or, from , ) with to limit the logged queries to those having a material impact on the server.

Excluding Queries That Examine Fewer Than a Minimum Row Limit

It can be beneficial to exclude queries that examine fewer than a minimum number of rows from the log. This can be done by setting the system variable, or, from , . It can be changed dynamically with . For example:

It can also be set in a server in an prior to starting up the server. For example:

Logging Slow Administrative Statements

By default, the Slow Query Log logs administrative statements. To disable logging of administrative statements, remove "admin" from the system variable or alternatively set the system variable to OFF. The Slow Query Log considers the following statements administrative: , , , , , , and . In and later, this also includes statements.

You can dynamically enable this feature using a statement and setting it for just the current connection with LOCAL. Some examples:

It can also be set in a server in an prior to starting up the server. For example:

Enabling the Slow Query Log for Specific Criteria

It is possible to enable logging to the slow query log for queries that meet specific criteria by configuring the system variable. It can be changed dynamically with . For example:

It can also be set in a server in an prior to starting up the server. For example:

You can find all options for log_slow_filter at or at .

Throttling the Slow Query Log

The slow query log can create a lot of I/O, so it can be beneficial to throttle it in some cases. The slow query log can be throttled by configuring the system variable. It can be changed dynamically with . For example:

It can also be set in a server in an prior to starting up the server. For example:

Configuring the Slow Query Log Verbosity

There are a few optional pieces of information that can be included in the slow query log for each query. This optional information can be included by configuring the system variable. It can be changed dynamically with . For example:

It can also be set in a server in an prior to starting up the server. For example:

It is possible to have .

Viewing the Slow Query Log

Slow query logs written to file can be viewed with any text editor, or you can use the 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 or by making .

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 table.

Variables Related to the Slow Query Log

• - enable/disable the slow query log. Renamed to from .

• - how the output will be written

• . Whether to log OPTIMIZE, ANALYZE, ALTER and other administrative statements to the slow log. Deprecated from , use without admin.

• - name of the slow query log file. Renamed to

Rotating the Slow Query Log on Unix and Linux

Unix and Linux distributions offer the utility, which makes it very easy to rotate log files. See for more information on how to use this utility to rotate the slow query log.

_{This page is licensed: CC BY-SA / Gnu FDL}