Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-4206

slow query log is not logging when log_slow_filter is empty

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.1, 5.5.29
    • Fix Version/s: 10.0.3, 5.5.31
    • Component/s: None
    • 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;
      

        Gliffy Diagrams

          Attachments

            Activity

            Hide
            elenst Elena Stepanova added a comment -

            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'.

            Show
            elenst Elena Stepanova added a comment - 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'.
            Hide
            elenst Elena Stepanova added a comment -

            Not reproducible on maria/5.3.

            Show
            elenst Elena Stepanova added a comment - Not reproducible on maria/5.3.
            Hide
            seletit Sergiy Tytarenko added a comment -

            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.

            Show
            seletit Sergiy Tytarenko added a comment - 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.
            Hide
            dmitry Dmitry Kuryata added a comment - - edited

            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!

            Show
            dmitry Dmitry Kuryata added a comment - - edited 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!
            Hide
            elenst Elena Stepanova added a comment -

            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.

            Show
            elenst Elena Stepanova added a comment - 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.

              People

              • Assignee:
                wlad Vladislav Vaintroub
                Reporter:
                seletit Sergiy Tytarenko
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 3 hours
                  3h