5.2.5. The Slow Query Log

The slow query log consists of all SQL statements that took more than long_query_time seconds to execute and required at least min_examined_row_limit rows to be examined. The time to acquire the initial table locks is not counted as execution time. mysqld writes a statement to the slow query log after it has been executed and after all locks have been released, so log order might be different from execution order. The default value of long_query_time is 10. The minimum value is 0, and a resolution of microseconds is supported when logging to a file. However, the microseconds part is ignored and only integer values are written when logging to tables.

By default, the slow query log is disabled. Use --slow_query_log[={0|1}] to specify the initial slow query log state explicitly. With no argument or an argument of 1, --slow_query_log enables the log. With an argument of 0, this option disables the log. You can use --slow_query_log_file=file_name to specify a log file name. You can also use --log-output to specify the log destination (as described in Section 5.2.1, “Selecting General Query and Slow Query Log Output Destinations”). The older option to enable the slow query log file, --log-slow-queries, is deprecated.

If you specify no name for the slow query log file, the default name is host_name-slow.log. The server creates the file in the data directory unless an absolute path name is given to specify a different directory.

To control the slow log at runtime, use the global slow_query_log and slow_query_log_file system variables. Set slow_query_log to 0 (or OFF) to disable the log or to 1 (or ON) to enable it. Set slow_query_log_file to specify the name of the log file. If a log file already is open, it is closed and the new file is opened.

When the slow query log is enabled, the server writes output to any destinations specified by the --log-output option or log_output system variable. If you enable the log, the server opens the log file and writes startup messages to it. However, further logging of queries to the file does not occur unless the FILE log destination is selected. If the destination is NONE, no queries are written even if the slow query log is enabled. Setting the log file name has no effect on logging if the log destination value does not contain FILE.

The slow query log can be used to find queries that take a long time to execute and are therefore candidates for optimization. However, examining a long slow query log can become a difficult task. To make this easier, you can process a slow query log file using the mysqldumpslow command to summarize the queries that appear in the log. See Section 4.6.8, “mysqldumpslow — Summarize Slow Query Log Files”.

In MySQL 5.5, queries that do not use indexes are logged in the slow query log if the --log-queries-not-using-indexes option is specified. See Section 5.1.2, “Server Command Options”.

In MySQL 5.5, the --log-slow-admin-statements server option enables you to request logging of slow administrative statements such as OPTIMIZE TABLE, ANALYZE TABLE, and ALTER TABLE to the slow query log.

Queries handled by the query cache are not added to the slow query log, nor are queries that would not benefit from the presence of an index because the table has zero rows or one row.

A replication slave does not write replicated queries to the slow query log, unless it is run using the --log-slow-slave-statements option.

The slow query log should be protected because logged statements might contain passwords. See Section, “Administrator Guidelines for Password Security”.

Copyright © 2010-2023 Platon Technologies, s.r.o.           Home | Man pages | tLDP | Documents | Utilities | About
Design by styleshout