As of version 8.0, MySQL will be retiring the query cache. Here is the TLDR overview:
What is the query cache?
The MySQL query cache seeks to improve performance at the database level by checking incoming queries against it’s cache and returning pre-stored results when they’re available. On the face of it, this may seem a sensible approach but over time issues with the implementation have become apparent – hence the planned deprecation.
What do I need to be aware of?
MySQL Query cache was meant to improve performance but comes with it’s own set of scalability issues:
- Does not scale well on multi-core machines.
- Each time a table is updated, outdated data needs to be pulled from the cache. Pulling data from the cache results in a global table lock – temporarily blocking queries on that table which can quickly result in a backlog building up on high throughput systems.
- As of MySQL 5.6 (2013) the query cache has been disabled as a default – meaning it’s still available but must be manually enabled.
- If your system is write-heavy, you’re unlikely to see any negative impact on performance. In a read heavy environment, you may want to measure performance with and without query cache.
- ProxySQL offers a TTL based cache which performs better than the soon to be deprecated native query cache. However TTL based caching should only be used in scenarios where stale data is deemed acceptable (the data will/could be stale since the cache is not cleared each time a table is altered, unlike the native query cache).
- Server-side Query Rewrite provides a means of optimising queries at the server level. It does so via pattern based match/replace which means there is a manual process – you’ll have to review expensive queries and identify how they can be optimised.
If you want to read the full story, you can find it on the MySql blog.