MySQL / MariaDB: Waiting for query cache lock

Query cache might seem like a good idea at first glance. When you execute a query, it computes the hash of the query, tags the tables the data was fetched from, and stores the result set in the cache. If the query appears again before any data in the underlying tables has changed, we don’t have to execute the query again, we just fetch it from the cache. What could possibly go wrong?

Well, it turns out that there is a flaw in this plan, and as is usually the case with most caches, it is to do with detecting staleness and purging stale data.

Every time a write happens to a table, the query cache is scanned for any cached query output that is at least partially dependent on data in that table. If you have more than minimal write concurrency, this process very quickly becomes the bottleneck. Each write then has to queue up to scan and purge the query cache of any queries it is about to taint. Unfortunately, this is slow and requires serialisation, and very quickly results in a situation where the number of running threads goes through the roof and they are all stuck in a “Waiting for query cache lock” state. And the database grinds to a halt.

So – how do we fix this? We disable the query cache.

Add the following to the [mysqld] section of of your config file:

query_cache_type = 0
query_cache_size = 0

And restart the mysql service.

The pileup of queries stuck in “Waiting for query cache lock” state will be gone for good.

This is a rather common problem, and our MariaDB and MySQL support specialists encounter it all the time when they carry out performance optimisation on clients’ systems. Because the query cache typically causes more problems than it solves, the feature was completely removed in MySQL 8.0.