When MySQL 8.0 shipped, its release notes did something unusual: they deleted a feature outright. The query cache — deprecated through 5.7 — was gone, code and all, after years as the single most notorious scalability bug in the server. MariaDB never removed it. The query cache still ships in MariaDB 10.11 and the 11.x releases, still answers to the same variables, and still turns itself on whenever someone applies a 2012-era my.cnf template. I have been paged for it twice: a healthy-looking 32-core box, load average through the roof, and a processlist full of sessions waiting on the query cache lock.
This post is why that happens, how to prove it with counters instead of vibes, the configurations that are actually sane, and when the right query cache size is zero.
What the query cache actually is
The query cache is a server-side result cache keyed by the exact byte text of the query plus context — the default database, the character set, and a handful of session state. Same bytes, same stored result, served from RAM without parsing, optimizing, or touching InnoDB. When it hits, it is the fastest path the server has. The fatal design decision is invalidation granularity: any write to a table invalidates every cached query that references that table. Not the rows that changed — every result touching the table. For a lookup table written once a minute, that is brilliant. For an orders table written five hundred times a second, the cache is a garbage collector that runs on every write.
The mutex problem
The entire structure is guarded by one global lock. Every cacheable SELECT acquires it to look up its text — SQL_NO_CACHE and uncacheable queries skip the machinery entirely — every cacheable result acquires it again to insert, and every write acquires it to invalidate. On four cores at moderate traffic, nobody notices. On a 32- or 64-core box pushing tens of thousands of queries per second, that lock becomes the hottest line in the process, and threads begin to queue: SHOW PROCESSLIST fills with sessions in the state Waiting for query cache lock. Worse, invalidation holds the lock while it walks every cached query referencing the written table — so the busier your writes, the longer the lock is held, precisely when the reads are queueing behind it. This specific pathology is why MySQL deleted the feature rather than fix it, and the physics do not change because the fork kept the code.
SHOW VARIABLES LIKE 'query_cache%';
SELECT id, user, host, command, time, state, left(info, 80) AS query
FROM information_schema.processlist
WHERE state LIKE '%query cache%';If the second query returns more than a stray row or two during a slowdown, you have your smoking gun. Do not reach for more cores — cores are exactly what makes it worse.
Reading the Qcache counters honestly
The query cache grades its own homework in SHOW GLOBAL STATUS, and the honest grade comes from deltas over time, not raw values. Qcache_hits counts served results; Com_select counts SELECTs — and in MariaDB, results served from the cache increment Com_select too, so the real hit rate is simply Qcache_hits against Com_select, trended as rates. Watch Qcache_inserts next: when inserts track Com_select closely while hits stay low, nearly every result is being cached and then invalidated before it is ever reused — you are paying the insert cost, the invalidation cost, and the lock cost for nothing.
SHOW GLOBAL STATUS
WHERE Variable_name IN
('Qcache_hits', 'Qcache_inserts', 'Qcache_not_cached',
'Qcache_lowmem_prunes', 'Qcache_free_blocks',
'Qcache_queries_in_cache', 'Com_select');Two more counters close the loop. Qcache_lowmem_prunes counts results evicted to make room; a steadily climbing value means the cache is too small or too fragmented to hold its working set. Qcache_free_blocks only means something as a fraction of Qcache_total_blocks — thousands of free blocks inside a gigabyte cache is a non-event, the same count inside a sixteen-megabyte cache is fragmentation — and every result is rounded up to query_cache_min_res_unit (4 KB by default), so a cache full of small results wastes blocks and prunes early. And remember query_cache_limit, 1 MB by default: results larger than that are never cached at all, which is usually the right call anyway. My rule of thumb from the incidents, not the manual: if the measured hit rate is under roughly a quarter and the workload writes at all, the cache is a tax, not a feature.
The configurations that are actually sane
Option one, and my default: off. Set query_cache_type=OFF and query_cache_size=0, both in the config file and at runtime, and change them together — leaving one enabled while zeroing the other has historically produced half-active states that still charge overhead. Watch the Qcache counters go quiet and the lock waits vanish from the processlist.
Option two, the defensible middle: demand mode. query_cache_type=DEMAND caches only queries that explicitly carry the SQL_CACHE hint, and SQL_NO_CACHE remains available as the explicit opt-out in the other modes. This turns the cache into a scalpel: the three expensive report queries that hammer a slowly-changing reference table get cached, and everything else bypasses the machinery instead of paying the per-query lock tax for a hit that will never come.
Option three, if you truly must keep it broadly on: keep it small. A bigger cache is not a better cache here — invalidation walks every cached query for the written table under the same global lock, so a giant cache lengthens lock hold time exactly when it hurts. The configurations that survive contact with production live in the tens of megabytes, not the gigabytes, and they live alongside write rates low enough that invalidation is an event, not a storm.
When to just turn it off
Turn it off on write-heavy OLTP, on many-core servers, and on any workload whose query text is mostly unique. The classic offender is client-side interpolation — ORMs and drivers that inline literal parameters into the SQL text emit byte-for-byte different statements that can never hit. Server-side prepared statements are a separate story: MariaDB caches them in their own namespace, so repeated executions of one prepared statement can still hit. Either way, unique query text is one more way the query cache and modern application patterns despise each other. If you genuinely need result caching, put it where it scales: in the application with Redis or Memcached, or at the proxy — MariaDB's own MaxScale has a cache filter that does selective, TTL-based caching outside the server's global lock, which is where result caching belongs. The server-side cache has exactly one advantage, zero client changes, and exactly one fatal flaw, the lock. Choose accordingly.
From counters to tooling: the MonPG disclosure
Everything in this post was counter work, and that is the point — the query cache convicts itself if you graph the right numbers. Trend Qcache_hits, Qcache_inserts, Qcache_lowmem_prunes, and Com_select as rates, sample the processlist for query-cache-lock waits, and treat query_cache_type flipping on in any environment as a config-drift event worth a ticket. Now the disclosure: MonPG monitors PostgreSQL today, not MariaDB — MariaDB monitoring is on the way and being built now, and counter-first workflows like this one are exactly what it is being built around. If PostgreSQL is part of your estate, the same evidence-first workflow is live there today — see the PostgreSQL overview and the engine comparison, or browse the rest of the series on the blog.