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

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.1, 5.5.29
    • 10.0.3, 5.5.31
    • None
    • 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;

      Attachments

        Activity

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

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

          Not reproducible on maria/5.3.

          elenst Elena Stepanova added a comment - Not reproducible on maria/5.3.

          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.

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

          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!

          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.

          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.

          I have updated https://mariadb.com/kb/en/slow-query-log-extended-statistics/ to include all log_slow_query options

          monty Michael Widenius added a comment - I have updated https://mariadb.com/kb/en/slow-query-log-extended-statistics/ to include all log_slow_query options

          People

            wlad Vladislav Vaintroub
            seletit Sergiy Tytarenko
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.