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

No cache directive(SQL_CACHE) query still wait for "query cache lock" on query_cache_type=DEMAND mode

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.0.13, 10.1.0
    • Fix Version/s: 10.0
    • Component/s: Query Cache
    • Labels:
    • Environment:
      Darwin Mattui-MacBook-Pro.local 13.3.0 Darwin Kernel Version 13.3.0: Tue Jun 3 21:27:35 PDT 2014; root:xnu-2422.110.17~1/RELEASE_X86_64 x86_64

      Description

      According to the MySQL manual,

      http://dev.mysql.com/doc/refman/5.6/en/query-cache-configuration.html

      "A value of 2 or DEMAND causes caching of only those statements that begin with SELECT SQL_CACHE."

      But, No cache directive query still wait for query cache lock on query_cache_type=2(DEMAND) mode server.

      MariaDB [test]> show variables like 'query_cache_type';
      +------------------+--------+
      | Variable_name    | Value  |
      +------------------+--------+
      | query_cache_type | DEMAND |
      +------------------+--------+
       
      MariaDB [test]> show profiles;
      +----------+------------+---------------------------------------------+
      | Query_ID | Duration   | Query                                       |
      +----------+------------+---------------------------------------------+
      |        1 | 0.00322300 | select * from tb_test limit 10              |
      |        2 | 0.00049600 | select SQL_CACHE * from tb_test limit 10    |
      |        3 | 0.00298600 | select SQL_NO_CACHE * from tb_test limit 10 |
      +----------+------------+---------------------------------------------+
      8 rows in set (0.00 sec)
       
      MariaDB [test]> show profile for query 1;
      +--------------------------------+----------+
      | Status                         | Duration |
      +--------------------------------+----------+
      | starting                       | 0.000115 |
      | Waiting for query cache lock   | 0.000030 | <== **HERE**
      | init                           | 0.000023 |
      | checking query cache for query | 0.000181 |
      | checking permissions           | 0.000049 |
      ...
      +--------------------------------+----------+
      22 rows in set (0.00 sec)
       
      MariaDB [test]> show profile for query 2;
      +--------------------------------+----------+
      | Status                         | Duration |
      +--------------------------------+----------+
      | starting                       | 0.000154 |
      | Waiting for query cache lock   | 0.000032 |
      | init                           | 0.000025 |
      | checking query cache for query | 0.000043 |
      | checking privileges on cached  | 0.000039 |
      | checking permissions           | 0.000064 |
      | sending cached result to clien | 0.000069 |
      | updating status                | 0.000041 |
      | cleaning up                    | 0.000029 |
      +--------------------------------+----------+
      9 rows in set (0.01 sec)
       
      MariaDB [test]> show profile for query 3;
      +----------------------+----------+
      | Status               | Duration |
      +----------------------+----------+
      | starting             | 0.000270 |
      | checking permissions | 0.000042 |
      | Opening tables       | 0.000155 |
      | After opening tables | 0.000075 |
      | System lock          | 0.000077 |
      ...
      +----------------------+----------+
      19 rows in set (0.00 sec)

      In the source code, server only checks if there's SQL_NO_CACHE directive. They don't care what is current query_cache_type.

      == sql/sql_cache.cc(Original) =========================================================
      int
      Query_cache::send_result_to_client(THD *thd, char *org_sql, uint query_length)
      {
        ...
        
        if ((sql_end - sql) > 20 && has_no_cache_directive(sql+6))
        {
          /*
            We do not increase 'refused' statistics here since it will be done
            later when the query is parsed.
          */
          DBUG_PRINT("qcache", ("The statement has a SQL_NO_CACHE directive"));
          goto err;
        }
       
        ...
      }
      ========================================================================================

      So, I think server should check both of query_cache_type and sql cache directive.

      == sql/sql_cache.cc (Modified) =========================================================
      /**
        Helper function for determine if a SELECT statement has a SQL_CACHE
        directive.
       
        @param sql A pointer to the first white space character after SELECT
        @param sql_end A pointer to the last character of sql
       
        @return
         @retval TRUE The character string contains SQL_CACHE
         @retval FALSE No SQL_CACHE directive found.
      */
       
      static bool has_cache_directive(const char *sql, const char* sql_end)
      {
        while (is_white_space(*sql))
          sql++;
       
        if( (sql_end - sql) < 9 ) return FALSE;
       
        if (my_toupper(system_charset_info, sql[0])  == 'S' &&
            my_toupper(system_charset_info, sql[1])  == 'Q' &&
            my_toupper(system_charset_info, sql[2])  == 'L' &&
            my_toupper(system_charset_info, sql[3])  == '_' &&
            my_toupper(system_charset_info, sql[4])  == 'C' &&
            my_toupper(system_charset_info, sql[5])  == 'A' &&
            my_toupper(system_charset_info, sql[6])  == 'C' &&
            my_toupper(system_charset_info, sql[7])  == 'H' &&
            my_toupper(system_charset_info, sql[8])  == 'E' &&
            my_isspace(system_charset_info, sql[9]))
          return TRUE;
       
        return FALSE;
      }
       
      ...
       
      int
      Query_cache::send_result_to_client(THD *thd, char *org_sql, uint query_length)
      {
        ...
       
        if (( thd->variables.query_cache_type==1/*ON*/ && (sql_end - sql) > 20 && has_no_cache_directive(sql+6) ) ||
      		  ( thd->variables.query_cache_type==2/*DEMAND*/ && !has_cache_directive(sql+6, sql_end) ))
        {
          /*
            We do not increase 'refused' statistics here since it will be done
            later when the query is parsed.
          */
          DBUG_PRINT("qcache", ("The statement has a SQL_NO_CACHE(query_cache_type=ON) or not a SQL_CACHE(query_cache_type=DEMAND) directive"));
          goto err;
        }
       
        ...
      }
      ========================================================================================

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              Matt74 Seunguck Lee
              Votes:
              2 Vote for this issue
              Watchers:
              7 Start watching this issue

                Dates

                Created:
                Updated: