Slow Query Log Extended Statistics - Source

This addition is based on the [[http://www.percona.com/percona-builds/Percona-SQL-5.0/Percona-SQL-5.0-5.0.87-b20/patches/microslow_innodb.patch|microslow]] patch from [[http://www.percona.com/|Percona]]. == 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: * <<fixed>>Query_plan<</fixed>> ** 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" * <<fixed>>Innodb<</fixed>> ** Reserved for future use. The default value is ' ', to be compatible with MySQL 5.1. Multiple options are separated by ','. === 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: * <<fixed>>admin<</fixed>> ** Log adminstrative statements (create, optimize, drop etc...) * <<fixed>>filesort<</fixed>> ** Log statement if it uses filesort * <<fixed>>filesort_on_disk<</fixed>> ** Log statement if it uses filesort that needs temporary tables on disk * <<fixed>>full_join<</fixed>> ** Log statements that doesn't uses indexes to join tables * <<fixed>>full_scan<</fixed>> ** Log statements that uses full table scans * <<fixed>>query_cache<</fixed>> ** Log statements that are resolved by the query cache * <<fixed>>query_cache_miss<</fixed>> ** Log statements that are not resolved by the query cache * <<fixed>>tmp_table<</fixed>> ** Log statements that uses in memory temporary tables * <<fixed>>tmp_table_on_disk<</fixed>> ** Log statements that uses temporary tables on disk Multiple options are separated by ','. If you don't specify any options (default) 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).