[MDEV-24548] log_slow_filter = not_using_index set automatically log_queries_not_using_indexes = 1 and overwrites long_query_time filter Created: 2021-01-08  Updated: 2022-08-01  Resolved: 2021-02-22

Status: Closed
Project: MariaDB Server
Component/s: Documentation, Variables
Affects Version/s: 10.3, 10.4, 10.5
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Richard Stracke Assignee: Ian Gilfillan
Resolution: Fixed Votes: 1
Labels: slow_query_log

Issue Links:
Relates
relates to MDEV-11165 Documentation about log_slow_filter u... Closed

 Description   

In

https://mariadb.com/kb/en/server-system-variables/#log_slow_filter

is documented

" If a query matches one of the types listed in the filter, and takes longer than long_query_time, it will be logged"

So long_query_time is a mandatory condition for log_slow_filter.

So log_slow_filter and log_slow_filter must be fulfilled.

But this is not the case for option
"not_using_index".

If you add "not_using_index" to log_slow_filter
it logs into slow_log, even if the execution time is less than "log_slow_filter" long_query_time.

The root cause is, that adding
"not_using_index"
to "log_slow_filter"
set implicit log_queries_not_using_indexes = 1.

to reproduce:

set SESSION  log_queries_not_using_indexes = 0;
 
SHOW VARIABLES LIKE 'log_q%';
 
set SESSION log_slow_filter  = "admin,filesort,filesort_on_disk,filesort_priority_queue,full_join,full_scan,not_using_index,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk ";
 
SHOW VARIABLES LIKE 'log_q%';

For "log_queries_not_using_indexes", it is documented,
that query time is not important.

Queries that don't use an index, or that perform a full index scan where the index doesn't limit the number of rows, will be logged to the slow query log (regardless of time taken).

But "not_using_index," as a "log_slow_filter" option should respect "long_query_time".

 Comments   
Comment by Elena Stepanova [ 2021-01-14 ]

The mapping of log_slow_filter / not_using_index to log_queries_not_using_indexes was apparently intentional, done and mentioned in the comments in MDEV-8305.
Since the value of log_queries_not_using_indexes is set, and log_queries_not_using_indexes bypasses long_query_time by design, the result is logical, even though it can be confusing.
This exception is explicitly mentioned in the enterprise documentation.

However, the KB not only doesn't say anything about the exception, but it doesn't list not_using_index switch in log_slow_filter at all. Both points should certainly be addressed in the documentation.

Comment by Ian Gilfillan [ 2021-02-22 ]

Fixed. Note that enterprise docs have a typo and make mention of a non-existent setting, so those should be fixed there.

Generated at Thu Feb 08 09:30:49 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.