Details
-
Task
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
None
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
- relates to
-
MDEV-7132 SQL_NO_CACHE / SQL_CACHE only handled/optimized by qc, if used in the right order
-
- Closed
-
- links to