[MDEV-4206] slow query log is not logging when log_slow_filter is empty Created: 2013-02-26  Updated: 2022-12-13  Resolved: 2013-05-08

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.1, 5.5.29
Fix Version/s: 10.0.3, 5.5.31

Type: Bug Priority: Major
Reporter: Sergiy Tytarenko Assignee: Vladislav Vaintroub
Resolution: Fixed Votes: 0
Labels: None


 Description   

Switched to MariaDB 5.5.29 from Percona SQL Server 5.1a and noticed empty slow query log.

It looks like it is logging only when log_slow_filter is set, but it is not logging when log_slow_filter is set empty string.

Having filter set is not resolving the issue, since optimized, but still slow queries are not logged.

log_slow_filter = 'admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk'

Test case:

SET long_query_time=1;
SET log_slow_filter='';
SET GLOBAL slow_query_log=1;
SET GLOBAL log_output='TABLE';
 
SELECT SLEEP(2);
SELECT * FROM mysql.slow_log;



 Comments   
Comment by Elena Stepanova [ 2013-02-26 ]

I didn't get the part about "Having filter set is not resolving the issue, since optimized, but still slow queries are not logged", but I agree with the point about log_slow_filter being an empty string. The parameter description in Percona says:
"If the string is empty, then the filter is disabled"
http://www.percona.com/doc/percona-server/5.1/diagnostics/slow_extended.html?id=percona-server:features:slow_extended_51&redirect=2

In Percona server it is indeed so, but in MariaDB, if the parameter is empty, no slow logging is performed – see the test case added to the description.
To make SELECT SLEEP appear in the slow log, log_slow_filter has to be set to something containing 'query_cache_miss'.

Comment by Elena Stepanova [ 2013-02-26 ]

Not reproducible on maria/5.3.

Comment by Sergiy Tytarenko [ 2013-02-26 ]

From MariaDB documentation:

"Multiple options are separated by ','. If you don't specify any options (default) everything will be logged."

Once we switched to mariadb, log_slow_filter was set to be empty (to log all slow queries), but we noticed no queries in slow log.

https://kb.askmonty.org/en/slow-query-log-extended-statistics/

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 adminstrative 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 (default) everything will be logged.

Comment by Dmitry Kuryata [ 2013-02-26 ]

Looks like documentation has been adjusted

Still, this sentence doesn't seem to be correct
" The default value is ' ', to be compatible with MySQL 5.1. "
First, the default value is actually a list of all available options.
Second, if it's doesn't work in the same fashion as 5.1 then it's not compatible at all!

Comment by Elena Stepanova [ 2013-05-23 ]

The problem with the empty value is fixed in 5.5.31. The default value in 5.5 remains the full list of values, so I updated the text in the following way:

was:
Multiple options are separated by ','. If you don't specify any options (default) everything will be logged.

now:
Multiple options are separated by ','. If you don't specify any options everything will be logged.

The text quoted in the previous comment ("The default value is ' ', to be compatible with MySQL 5.1") relates to log_slow_verbosity (not to log_slow_filter) and remains true.

Generated at Thu Feb 08 06:54:36 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.