Query cache might seem like a good one at a glance. When you execute a query, MariaDB computes the hash of the query, tags the tables the data was fetched from, and stores the result in memory. If the query is issued again before any data in the underlying tables changes, MariaDB doesn’t have to execute the query again, it fetches it from the cache.
The non-trivial problem caches have to solve is in establishing when the data is stale and must be expired. 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.
Disabling the query cache fixes this. Add the following to the [mysqld] section 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.