Slow Query Log Extended Statistics
Overview
- Added extra logging to slow log of 'Thread_id, Schema, Query Cache hit, Rows sent and Rows examined'
- Added optional logging to slow log, through log_slow_verbosity, of query plan statistics
- Added new session variables log_slow_rate_limit, log_slow_verbosity, log_slow_filter
- Added log-slow-file as synonym for 'slow-log-file', as most slow-log variables starts with 'log-slow'
- Added log-slow-time as synonym for long-query-time.
New Session Variables
log_slow_verbosity
You can set the verbosity of what's logged to the slow query log by setting the the log_slow_verbosity variable to a combination of the following values:
Query_plan
- For select queries, log information about the query plan. This includes "Full_scan", "Full_join", "Tmp_table", "Tmp_table_on_disk", "Filesort", "Filesort_on_disk" and number of "Merge_passes during sorting"
explain
(Starting from 10.0.5)- EXPLAIN output is logged in the slow query log. See explain-in-the-slow-query-log for details.
Innodb
(Starting from 10.6.15. Before that this option did nothing)- Kept for compatibility. Same as
engine
.
- Kept for compatibility. Same as
engine
(Starting from 10.6.15)- Writes statistics from the storage engine. This includes:
Option | Description | Engine |
---|---|---|
Pages_accessed | Number of pages accessed from page buffer (innodb-buffer-pool / key cache) | InnoDB |
Pages_updated | Number of pages updated in memory | InnoDB |
Pages_read_time | Milliseconds spend reading pages from storage | InnoDB |
Old_rows_read | Number of retrieval of old versions of rows in the engine (versioning) | InnoDB |
Engine_time | Milliseconds spent inside engine calls (read_row / read_next_row etc) | All |
full
- Shortcut to enable all the verbosity options
The default value for log_slow_verbosity
is ' ', to be compatible with MySQL 5.1.
Multiple options are separated by ','.
log_slow_verbosity is not supported when log_output='TABLE'.
In the future will add more engine
statistics and also support for other engines.
log_slow_filter
You can define which queries to log to the slow query log by setting the variable log_slow_filter to a combination of the following values:
admin
- Log administrative statements (create, optimize, drop etc...)
filesort
- Log statement if it uses filesort
filesort_on_disk
- Log statement if it uses filesort that needs temporary tables on disk
full_join
- Log statements that doesn't uses indexes to join tables
full_scan
- Log statements that uses full table scans
query_cache
- Log statements that are resolved by the query cache
query_cache_miss
- Log statements that are not resolved by the query cache
tmp_table
- Log statements that uses in memory temporary tables
tmp_table_on_disk
- Log statements that uses temporary tables on disk
Multiple options are separated by ','. If you don't specify any options everything will be logged.
log_slow_rate_limit
The log_slow_rate_limit variable limits logging to the slow query log by not logging every query (only one query / log_slow_rate_limit is logged). This is mostly useful when debugging and you get too much information to the slow query log.
Note that in any case, only queries that takes longer than log_slow_time or long_query_time' are logged (as before).