7

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
7

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 !!!

7

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.

3

(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.