[MDEV-6631] No cache directive(SQL_CACHE) query still wait for "query cache lock" on query_cache_type=DEMAND mode Created: 2014-08-22  Updated: 2022-11-08

Status: Open
Project: MariaDB Server
Component/s: Query Cache
Fix Version/s: None

Type: Task Priority: Minor
Reporter: Seunguck Lee Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 2
Labels: patch

Issue Links:
Relates
relates to MDEV-7132 SQL_NO_CACHE / SQL_CACHE only handled... Closed

 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;
 
  }
 
 
 
  ...
 
}
 
========================================================================================



 Comments   
Comment by roberto spadim [ 2014-09-23 ]

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)

Comment by roberto spadim [ 2014-10-18 ]

hi guys, could review this and commit in the main line?

Comment by Daniel Black [ 2014-11-19 ]

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

Comment by roberto spadim [ 2014-11-19 ]

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

Comment by roberto spadim [ 2014-11-19 ]

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"

Comment by roberto spadim [ 2014-11-19 ]

i created a sub task just to this problem: MDEV-7132

Comment by roberto spadim [ 2014-11-19 ]

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)

Comment by roberto spadim [ 2015-09-03 ]

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

Comment by roberto spadim [ 2015-09-03 ]

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

Generated at Thu Feb 08 07:13:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.