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.

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:

  • All (From MariaDB 10.6.16)
    • Enable all verbosity options.
  • 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
  • Innodb (From MariaDB 10.6.15. Before that this option did nothing)
    • Kept for compatibility. Same as engine.
  • engine (From MariaDB 10.6.15)
    • Writes statistics from the storage engine. This includes:
OptionDescriptionEngine
Pages_accessedNumber of pages accessed from page buffer (innodb-buffer-pool / key cache)InnoDB
Pages_updatedNumber of pages updated in memoryInnoDB
Pages_read_timeMilliseconds spend reading pages from storageInnoDB
Old_rows_readNumber of retrieval of old versions of rows in the engine (versioning)InnoDB
Engine_timeMilliseconds spent inside engine calls (read_row / read_next_row etc)All
  • Warnings (From MariaDB 10.6.16)
    • Print all errors, warnings and notes related to statement, up to log_slow_max_warnings lines.
  • full.
    • Old shortcut to enable all the verbosity options

The default value for log_slow_verbosity is ' ', to be compatible with MySQL 5.1.

The possible values for log_slow_verbosity are innodb,query_plan,explain,engine,warnings. Multiple options are separated by ','.

log_slow_verbosity is not supported when log_output='TABLE'.

In the future we 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).

log_slow_max_warnings

MariaDB starting with 10.6.16

If one enables the warning option for log_slow_verbosity, all notes and warnings for a slow query will also be added to the slow query log. This is very usable when one has enabled warnings for Notes when an index cannot be used. log_slow_max_warnings limits the number of warnings printed to the slow query log per query. The default value is 10.

Credits

Part of this addition is based on the microslow patch from Percona.

See also

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.