MySQL – Query Profiling Shows ‘Waiting for Query Cache Lock’ with Query Cache Size 0

MySQLquery-cachereplication

We have a statement based replication server that has been experiencing slowdowns and during the event SHOW FULL PROCESSLIST showed the replication query stuck on "Waiting for query cache lock" which was surprising because the server query_cache_size is set to 0.
Profiling the offending query does show this step in every update to the table.

Is this typical to see in the profile for an update even if the query_cache_size is 0? Is this really just the check for query caching and not the query waiting to obtain a true lock?

Best Answer

The message means that you are trying to acquire a Query Cache Mutex even though the query_cache_size is 0 (Note that akuzminsky mentioned that the MySQL for Oracle still allows this) You will need to set query_cache_type to 0 as well because as the MySQL Documentation says:

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.

In MySQL 5.6, query_cache_type is 0 by default. It's 1 for MySQL 5.0, 5.1, and 5.5

Regardless of the MySQL version, make sure my.cnf has these

[mysqld]
query_cache_size = 0
query_cache_type = 0

You do not need to restart mysql. Just login to mysql and run the following:

mysql> SET GLOBAL query_cache_size = 0;
mysql> SET GLOBAL query_cache_type = 0;

Give it a Try !!!

Related Question