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
Activity
Daniel Black, the only "100%" solution should execute a parser of query, before checking query cache, any other method will implement a 'parser like' feature, this implemented a single search, i think we should leave this 'problem' open here, and leave it to the other mdev (7132), with more time we could check how much buffer should we check (or not)
Elena, this one i think should be merged, since this have performace loss, the MDEV-7132 don't report performace loss, just "what should be done to SQL_NO_CACHE/SQL_CACHE if we don't execute a parser before query cache check?" (i think the best solution is report a warning when using SQL_NO_CACHE / SQL_CACHE not after first SELECT hint
from https://dev.mysql.com/doc/refman/5.7/en/select.html
With SQL_NO_CACHE, the server does not use the query cache. It neither checks the query cache to see whether the result is already cached, nor does it cache the query result.
that's not true
and i think this could only be true if we execute parser before query cache check, since SQL_NO_CACHE can be at a subquery, for example:
select SQL_CACHE ..... from (select SQL_NO_CACHE ....) as tmp
maybe we should consider more than only one MDEV? maybe this one, and another to allow SQL_NO_CACHE / SQL_CACHE with a faster return from query cache? i vote for a search of about +50~100bytes to handle this SQL_NO_CACHE/SQL_CACHE search, but check that's not a big problem, just change the "has_sql_cache_directive" AND "has_no_cache_directive"