Bug #65336 MySQL query_cache_type set to 0 online will not turn off query cache
Submitted: 16 May 2012 16:04 Modified: 22 May 2015 14:07
Reporter: Zhang Lu Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Query Cache Severity:S3 (Non-critical)
Version:5.5.24 OS:Any
Assigned to: CPU Architecture:Any

[16 May 2012 16:04] Zhang Lu
Description:
According to the manual, query_cache_type can be changed online.
However, changing it online will not disable the query cache, e.g. no effect.
This will greatly slow down the replication if the underline DB has too many tables or partitions.

How to repeat:
Set up a replication pair and use ROW based format.
Start updating the master server.
Run "SHOW PROCESSLIST" on the replicate
You will see (from time to time, depending on the transaction volume) "invalidating query cache" on the replicate for the system user, which is the slave SQL thread.
[17 May 2012 6:55] Valeriy Kravchuk
Looks like a duplicate of Bug #60696. Please, check.
[17 May 2012 12:03] Zhang Lu
No. 60696 seems to be fixed already. 
This is purely for query_cache_type setting.
If it is not set in confer file and set nonlinear, the code still thinks query cache is enabled and invalidating query cache routine continues.
[17 May 2012 12:03] Zhang Lu
No. 60696 seems to be fixed already. 
This is purely for query_cache_type setting.
If it is not set in confer file and set nonlinear, the code still thinks query cache is enabled and invalidating query cache routine continues.
[30 May 2012 17:53] Sveta Smirnova
Thank you for the report.

How do you change this variable? Please send us the query. Did you restart slave after change?
[30 May 2012 18:15] Zhang Lu
To change the variable, I ran the SQL command
SET GLOBAL query_cache_type = 0;

The slave did get restarted after the change.
[30 May 2012 19:00] Sveta Smirnova
Thank you for the feedback.

You are doing right.

But according to http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_query_cache_typ...: "0 or OFF 	Do not cache results in or retrieve results from the query cache. Note that this does not deallocate the query cache buffer. To do that, you should set query_cache_size to 0."

So is interesting if Qcache_inserts increases. If it does not and Qcache_free_blocks increases this is not a bug: query cache invalidation can happen for queries which were in cache before you set query_cache_type. Please check.
[30 May 2012 19:27] Sveta Smirnova
Sorry, better watch Qcache_queries_in_cache and Qcache_total_blocks.

I run tests and they decreased after few inserts on slave and did not increase after selects.
[30 May 2012 19:35] Zhang Lu
if you look at file sql/sql_cache.cc  
Function: void Query_cache::invalidate_locked_for_write(TABLE_LIST *tables_used)

I added a line to print out return of is_disabled() in the function and found that is_disabled() only returns TRUE when the parameter is set in config file and the server is restarted. 
If the parameter is set on line, is_disabled() will still return false and the invalidate_table routine is called.
[30 May 2012 19:37] Zhang Lu
And by the way, query_cache_size is always set to 0 in my config file during all tests.
[31 May 2012 20:11] Sveta Smirnova
Thank you for the feedback.

Verified as described.

To repeat:

Change source code:

bzr diff sql/sql_cache.cc 
=== modified file 'sql/sql_cache.cc' (properties changed: -x to +x)
--- sql/sql_cache.cc	2011-06-30 15:46:53 +0000
+++ sql/sql_cache.cc	2012-05-31 19:57:45 +0000
@@ -1781,6 +1781,9 @@
   DBUG_ENTER("Query_cache::invalidate (table list)");
   if (is_disabled())
     DBUG_VOID_RETURN;
+else
+fprintf(stderr, "Enabled\n");
+
 
   using_transactions= using_transactions && thd->in_multi_stmt_transaction_mode();
   for (; tables_used; tables_used= tables_used->next_local)

Run attached test case, then count how many times word "Enabled" occurs in the slave error log. I suspect about 1000 (for first 1000 inserts), but it was actually 2004.

This behavior should be documented at least. "Does not free the buffer" does not mean query cache is enabled.
[31 May 2012 20:12] Sveta Smirnova
test case for MTR

Attachment: rpl_bug65336.test (application/octet-stream, text), 915 bytes.

[9 Aug 2012 2:52] liu hickey
Two things need point out:
1. It's not correct to add some log after is_disabled() to indicate the query_cache_tye, as that function returned the value set at startup of server, not the dyanmic changed value.

2. I did not find any thing wrong with the testcase. Query cache is really disabled after set query_cache_type=0.

Another issue with the extra lock overhead is patched, more info could refer to Launchpad: https://bugs.launchpad.net/percona-server/5.5/+bug/1021131
[6 Dec 2013 7:48] James Day
It's known that not all query cache overhead is stopped when query_cache_type is changed to 0 with the server running. This is summarised in the documentation as "reduced overhead in query execution" if it is done at startup:

"If the server is started with query_cache_type set to 0, it does not acquire the query cache mutex at all, which means that the query cache cannot be enabled at runtime and there is reduced overhead in query execution."

Changing the setting while the server is running does reduce the cost of the query cache because queries aren't cached and much checking time is reduced but there is still overhead compared to having it off at startup. That overhead is greater with row-based replication.

This bug is really a duplicate of Bug #60696. Same cause and effect, not having QC type 0 at startup has more overhead than having it 0 at startup.

It is very likely that people using row-based replication should have the query cache disabled at startup on the replication slave. That is also likely to be the best setting for high concurrency.

James Day, MySQL Senior Principal Support Engineer, Oracle