Details
-
Type:
Bug
-
Status: Open
-
Priority:
Major
-
Resolution: Unresolved
-
Affects Version/s: 10.0.13, 10.1.0
-
Fix Version/s: 10.0
-
Component/s: None
-
Labels:None
-
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; } ... } ========================================================================================
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
-
- Closed
-
- links to
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
hi guys, could review this and commit in the main line?
Roberto,
I looked at your patch and noticed that it won't catch other SELECT modifiers like
SELECT SQL_CALC_FOUND_ROWS SQL_CACHE....
You may need some more buffer over-read protection in has_sql_cache_directive
humm probably we have this same problem to
SELECT SQL_CACLC_FOUND_ROWS SQL_NO_CACHE ...
since i copy/paste the same code of has_no_cache_directive
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"
i created a sub task just to this problem: MDEV-7132
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
there's one problem since we can include queries to query cache when using query_cache_type=1 and after some time with queries without SQL_CACHE, change the variable to query_cache_type=2
in this case old queries without SQL_CACHE will not be used (i think it's not a problem, but should be docummented)