9

Why mysql remove query cache in 8.0 version ?

  • 4
    This was the BEST favor anyone could do for your server's reduction in CPU cycles used every minute. – Wilson Hauck Sep 14 '18 at 16:40
  • @WilsonHauck For small databases, yes. And great when mysql returns are more predictable in cpu/time it takes. However, when a count(*) on a large table can take 2-3 hours to complete query cache could cut that time down to a few milliseconds. Man in the middle caches are no replacement, they don't know if the data has changed. Given that it was a near fatal decision for huge databases. – John Feb 6 at 13:40
  • @john Post text results of SHOW CREATE TABLE (yourtablename); and I will suggest something that will be well under 2-3 hours to return your row count. – Wilson Hauck Feb 6 at 13:45
  • @WilsonHauck I have dozens such tables on many servers, it barely matters what structure you use. Here an example pastebin.com/MHVACAny Fill it up with 1 billion rows and try make a quick count using an index without query caching. With query caching it's a matter of milliseconds without it's probably about half an hour to an hour each count. Now update one of the columns at random places 1 billion times with different values and you'll notice its index degrades down to 4-5 hours (linear performance loss with updates). (the second part is a distinct issue of innodb, no fair here:) – John Feb 6 at 15:57
  • @John What are the results and nnnn sec with this query? SELECT COUNT(scs_id) from x_full; ? – Wilson Hauck Feb 6 at 19:07
9

There is a detailed blog from the MySQL server team about this, where Matt Lord says:

The query cache has been disabled-by-default since MySQL 5.6 (2013) as it is known to not scale with high-throughput workloads on multi-core machines.

We considered what improvements we could make to query cache versus optimizations that we could make which provide improvements to all workloads.

While these choices themselves are orthogonal, engineering resources are finite. That is to say that we are shifting strategy to invest in improvements that are more generally applicable to all workloads.

8

Good riddance !!!

It is a challenge for most database developers to correctly estimate the size the most common result sets in their applications. Having a large query cache was just a big bandage for that.

There is a bigger reason that foreshadowed the demise of the query cache. Four years ago, I answered the post Why query_cache_type is disabled by default start from MySQL 5.6?. Is short, the query cache was always inspecting the InnoDB Buffer Pool for changes. You can find this on Pages 209-215 of High Performance MySQL (2nd Edition).

I mentioned this over the years:

RIP Query Cache !!!

  • 1
    I agree that query cache was a bandage mostly for counts being so slow on innodb. It made queries and applications be completely unpredictable in response time, you can see milliseconds to hours on the same query depending if it was cached or not. However, at least it was a bandage. Now you're left bleeding :) – John Feb 6 at 16:02
  • The QC existed before InnoDB existed. The old story I heard (perhaps around 2002) was that the QC was added to win a benchmark with MyISAM. – Rick James Feb 6 at 16:36
  • @RickJames Hah sounds plausible, though it's cheating Mysql needs one thing more than anything else in my opinion: full multithreading on all types of queries. They seem to work in that direction (a bit here a bit there) bit but it should have been implemented long ago. That also would have been a killer feature to not just win but 'own' many types of benchmarks – John Feb 7 at 17:53
  • @John - MySQL 8.0.17(?) have a few parallel queries. I'm not excited yet. A product called "shard query" has been around for a long time; it demonstrates that parallelism typically gives only half the theoretical capacity. (eg, 8 cores yields only 4x speedup.) Also, keep in mind that most I/O has no parallelism unless you have certain RAID setups. – Rick James Feb 8 at 1:23
  • @John - and... "Long ago" servers had one, maybe two CPU cores. So parallelism was not very useful. Once there were multiple cores, there was a flurry of activity to clean up the Mutexes so that multiple connections could work in parallel. – Rick James Feb 8 at 1:25
4

(I agree with the other Answer, but here is my 2-cents-worth.)

As implemented, ...

  • The QC cannot work with Galera or Group Replication, both of which are getting more traction in the HA arena.

  • When query_cache_size got big, it got less efficient. This is due to inefficiencies in "pruning". (Note: Aurora reimplemented it, and seems to have fixed this issue.)

  • There is an overhead in every SELECT because it does not know whether the QC will come into play. Decades ago, this was estimated at 11%. Until getting rid of the QC, the only workaround was to do both query_cache_size = 0 and query_cache_type = 0 in the config file. (Few people realized both were needed.)

  • In the typical Production server, inserts are happening frequently. Since each insert caused a pruning of all entries for that table(s), the QC was virtually useless for such tables.

  • Perhaps 95% of the hundreds of systems I have reviewed for performance problems are better off without the QC.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.