It can also be set in a server option group in an option file 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 datadir directory. However, this can be changed.

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

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

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

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

Another way to configure the slow query log filename is to set the log-basename option, which configures MariaDB to use a common prefix for all log files (e.g. slow query log, general query log, error log, binary logs, 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 option group in an option file prior to starting up the server. For example:

The log-basename cannot be an absolute path. The log file name is relative to the datadir 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 slow_log table in the mysql database. To choose the slow query log output destination, set the log_output 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 log_output system variable to FILE. It can be changed dynamically with SET GLOBAL. For example:

It can also be set in a server option group in an option file 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 slow_log table in the mysql database by setting the log_output system variable to TABLE. It can be changed dynamically with SET GLOBAL. For example:

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

Some rows in this table might look like this:

See Writing logs into tables 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 slow_query_log system variable (or, from MariaDB 10.11, log_slow_query) 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 log_slow_disabled_statements system variable. This option cannot be set dynamically. It can be set in a server option group in an option file 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 long_query_time system variable (or, from MariaDB 10.11, log_slow_query_time). It uses a units of seconds, with an optional milliseconds component. The default value is 10. It can be changed dynamically with SET GLOBAL. For example:

It can also be set in a server option group in an option file 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 log_slow_filter or setting the log_queries_not_using_indexes system variable to 1. It can be changed dynamically with SET GLOBAL. Some examples:

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

As a significant number of queries can run quickly even without indexes, you can use the min_examined_row_limit system variable (or, from MariaDB 10.11, log_slow_min_examined_row_limit) with log_queries_not_using_indexes 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 min_examined_row_limit system variable, or, from MariaDB 10.11, log_slow_min_examined_row_limit. It can be changed dynamically with SET GLOBAL. For example:

It can also be set in a server option group in an option file 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 log_slow_filter system variable or alternatively set the log_slow_admin_statements system variable to OFF. The Slow Query Log considers the following statements administrative: ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, and REPAIR TABLE. In and later, this also includes ALTER SEQUENCE statements.

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

It can also be set in a server option group in an option file 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 log_slow_filter system variable. It can be changed dynamically with SET GLOBAL. For example:

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

You can find all options for log_slow_filter at log_slow_filter system variable or at Slow Query Log Extended Statistics.

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 log_slow_rate_limit system variable. It can be changed dynamically with SET GLOBAL. For example:

It can also be set in a server option group in an option file 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 log_slow_verbosity system variable. It can be changed dynamically with SET GLOBAL. For example:

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

It is possible to have EXPLAIN output printed in the slow query log.

Viewing the Slow Query Log

Slow query logs written to file can be viewed with any text editor, or you can use the mariadb-dumpslow 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. Renamed to log_slow_query from MariaDB 10.11.

• log_output - how the output will be written

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

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

• - time in seconds/microseconds defining a slow query. Renamed to from .

• - whether to log queries that don't use indexes

• - whether to log certain admin statements

• - types of statements that should not be logged in the slow query log

• - minimum rows a query must examine to be slow. Renamed to from .

• - permits a fraction of slow queries to be logged

• - amount of detail in the log

• - limit which queries to log

• - log slow statements executed by replica thread to the slow log if it is open.

Rotating the Slow Query Log on Unix and Linux

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

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