The query cache on our busiest 5.7 box reported a 38 percent hit rate, and the server was still drowning. That contradiction took me an embarrassing amount of time to take seriously: the dashboard said a third of all SELECTs were served from cache, yet reads dominated the slow log and CPU sat at 70 percent with no single expensive query to blame. The resolution was that both things were true. The 38 percent was computed against all SELECT statements, including the trivially fast ones, and the cache's global mutex was adding latency to every single statement that touched it — hits and misses alike — while any write to a table invalidated every cached result for that table, which on our write-heavy schema meant the cache was being flushed in waves thousands of times a minute. We were paying a synchronization tax on every query to accelerate a subset of the cheap ones. When we later planned the 8.0 upgrade, the query cache's removal turned out to be the least scary part of the migration, precisely because we had already measured how little it did for us. Here is the full picture: why it hurt, what exactly 8.0 removed, how to find the queries that secretly depended on it, and what actually replaces it.
Why did the query cache slow down write-heavy servers?
Because one global mutex guarded the entire cache, every statement had to pass through it, and every write to a table invalidated that table's entire cached result set — so the busier and more write-heavy the workload, the more the cache cost and the less it retained. The mechanics: the query cache stored the byte-identical text of a SELECT mapped to its result set. Before executing any SELECT, the server took the mutex and checked the text; on a hit it returned the result under the same mutex; on a miss it executed, then took the mutex again to store the result. Any INSERT, UPDATE, or DELETE against a table invalidated all cached queries referencing that table — not the affected rows, the whole table's cache entries. On a read-mostly lookup table, this is a genuine win. On our schema, where the hot tables took writes every few milliseconds, cached entries had a median lifetime of seconds, which meant we paid mutex acquisition and invalidation sweeps on a structure that rarely got to answer anything. The tell in the processlist was threads piling in states like "Waiting for query cache lock" during write bursts — not a lock on data, a lock on the cache itself. If you are still on 5.7 and see that state, the fix is query_cache_type=0 and query_cache_size=0 together; setting only the size to zero does not fully disable the code path.
What exactly did MySQL 8.0 remove, and what breaks at upgrade time?
MySQL removed the entire feature — engine code, SQL_CACHE and SQL_NO_CACHE hints, the Qcache_* status variables, and the configuration variables themselves — and the removal of the variables is the part that bites at upgrade time. The query cache was deprecated in 5.7.20 and deleted in 8.0.3, and because the options are gone rather than ignored, a my.cnf that still sets query_cache_size or query_cache_type makes mysqld refuse to start with [ERROR] unknown variable 'query_cache_size=64M' — which is how several teams meet the removal: not as a performance discussion but as a failed upgrade boot at 2 AM. Two practical notes. First, this is a config-file cleanup task to do before the upgrade window, alongside the other removed-variable sweep the 5.7-to-8.0 upgrade notes walk through; mysql --verbose --help against the 8.0 binary with your old config will enumerate every unknown variable in one pass. Second, SQL statements containing SQL_CACHE hints do not fail — the hint is parsed and ignored — but anything monitoring Qcache_hits or alerting on hit rates goes silent, so clean up the dashboards before someone pages on a flatlined metric that no longer exists.
-- on 5.7, before the upgrade: what is the cache really doing?
SHOW GLOBAL STATUS LIKE 'Qcache%';
-- Qcache_hits, Qcache_inserts, Qcache_lowmem_prunes,
-- Qcache_queries_in_cache, Qcache_free_memory
-- the honest hit rate: hits over (hits + misses that could have been cached)
-- Qcache_hits / (Qcache_hits + Com_select) is the number marketing quotes;
-- compare it against the cost side: Threads in "Waiting for query cache lock"
-- simulate 8.0 on a 5.7 staging box before you migrate:
SET GLOBAL query_cache_type = 0;
SET GLOBAL query_cache_size = 0;
-- now run production replay traffic and watch what actually gets slowerHow do you find the queries that secretly depended on it?
Replay production traffic against a 5.7 staging instance with the cache disabled — that box is now an 8.0 simulator — and the queries that regress are your dependency list, which will be shorter and more specific than you fear. The fear is "everything gets slower"; the reality, in our migration and in others I have helped with, is that the query cache only ever helped a narrow profile: byte-identical SELECTs, repeated at high frequency, against tables with low write rates, returning small-to-medium results. A homepage config lookup, a permissions check, a product catalog read on a table updated hourly — that profile. The digest workflow from the slow log digest notes finds candidates without a replay: rank normalized SELECTs by calls-per-day, filter to tables with low write churn, and anything at the top of that list with a tiny avg execution time is a likely cache beneficiary — high-frequency, individually cheap, repeated verbatim. In our case the whole dependency list was eleven query patterns, and ten of the eleven were hitting the same two reference tables. That specificity is what makes replacement tractable: you are not rebuilding "a cache," you are fixing eleven call sites.
What actually replaces the query cache in 8.0?
Nothing inside the server — and that is the honest answer — so replacement means three moves in order of preference: make the query fast enough not to need caching, cache in the application tier, or put a dedicated caching proxy in front. The first move covers more than it should, because a well-indexed lookup against a warm buffer pool answers in well under a millisecond, and many cached queries were only "slow" because of a missing index or a needless full result set; fixing the query deletes the problem permanently instead of maintaining a cache invalidation strategy. The second move — Redis or Memcached beside the app, keyed by whatever the app actually needs — is the right answer for genuinely hot, genuinely repeated reads, and it beats the query cache on every axis the old feature lost: explicit TTLs, invalidation scoped to the entity rather than the whole table, and no mutex shared with your database's execution path. The third move is ProxySQL's result-set cache: query rules in its admin interface with cache_ttl per pattern, which is architecturally the closest thing to the old feature — digest-matched SELECT caching in front of MySQL — and it survives topologies the server-side cache never could, since it caches across a pool of replicas. I would reach for it when you cannot touch application code and have a proven hot pattern; the rules live in ProxySQL's admin tables:
-- ProxySQL admin: cache a hot lookup pattern for 5 seconds
INSERT INTO mysql_query_rules
(rule_id, active, digest, cache_ttl, apply)
VALUES
(10, 1, '0x8F3A...', 5000, 1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
-- and on the MySQL side, confirm the pattern's real cost first
SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT/1e9 AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY COUNT_STAR DESC
LIMIT 10;One trap to name: a five-second TTL means five-second-stale reads, so the proxy answer requires the same staleness conversation as any cache. The stale-reads discussion frames that trade well, even though it is aimed at replica reads.
What did the query cache teach us that still applies?
That a hit rate is a vanity metric when the cache costs something on every operation, and that server-side magic caches are a bet against your own write growth. The query cache was designed for a read-mostly web workload from twenty years ago, and it died because real workloads grew writes and cores — both of which punish a single global mutex and full-table invalidation. The durable lesson transfers to every caching decision since: measure the cost side (mutex time, invalidation churn, staleness incidents) with the same rigor as the hit side, and be suspicious of any cache whose benefit you cannot state in milliseconds removed from a specific query pattern. Our 38 percent hit rate was real, and removing the cache dropped p99 read latency by 40 milliseconds during write bursts, because the misses and the mutex were the workload. If you are approaching the 8.0 upgrade and the query cache is on your worry list, measure it the staging way, expect an eleven-item dependency list rather than an apocalypse, and spend the migration energy on the buffer pool and index work the buffer pool sizing guide covers — that is where the reads actually live.
Where MonPG stands on MySQL
I build MonPG, so the honest line: MonPG monitors PostgreSQL today, and MySQL support is in active development, not shipped. The signals in this piece — digest-level query frequency to size a cache decision, mutex wait states surfaced as evidence, and pre/post-upgrade latency comparison on the same timeline — are exactly what the MySQL work is designed to surface, so a feature removal becomes a measured change instead of a leap. The MySQL monitoring (coming soon) page tracks that work as it lands. Until it ships, the same evidence-first approach runs on the PostgreSQL side today, and the rest of these MySQL field notes live on the blog.