Enable slow query log mysql without restart

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

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

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 effect 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

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.

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.

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

long_query_time

The long_query_time variable specify 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 before hand 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 does 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;

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. This option can only be set in the MySQL configuration file and it requires a restart of MySQL for the setting to take effect.

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 the MySQL configuration file. It requires a restart of MySQL for the change to take effect.

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). See Document 1024159.1 for more details about the Query Analyzer.

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

In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.