Reasons why MySQL cancelled Query Cache

MySQL previously had a query cache, Query Cache. Starting from 8.0, this query cache is no longer used. So what is the reason for abandoning it? This article will introduce it to you.

MySQL query cache is a cache of query results. It compares queries starting with SEL to the hash table and, if there is a match, returns the result of the previous query. When matching, the query must be matched byte by byte. For example, SELECT * FROM t1; is not equal to select * from t1;. In addition, some uncertain query results cannot be cached, and any modification to the table will invalidate all caches of these tables. Therefore, the best scenario for query caching is read-only, especially complex queries that need to examine millions of rows and return only a few. If your query meets such a characteristic, enabling query cache will improve your query performance.

As technology progressed and time went by, the MySQL engineering team found that there were not many benefits to enabling caching.

First, the effect of query cache depends on the cache hit rate. Only queries that hit the cache can be improved, so its performance cannot be predicted.

Second, another big problem with the query cache is that it is protected by a single mutex. On a server with many cores, a large number of queries can result in a lot of mutex contention.

Benchmarking has found that most workloads are best served by disabling the query cache (the default for 5.6): query_cache_type = 0

If you think you would benefit from query caching, test it accordingly.

• The more data you write, the less benefit you get
• The more data you hold in the buffer pool, the less benefit you get.
• The more complex the query, the larger the scan range, the greater the benefit

Another reason why MySQL 8.0 cancels query cache is that research shows that the closer the cache is to the client, the greater the benefit. For more information about this study, please refer to https://proxysql.com/blog/scaling-with-proxysql-query-cache/

The following image is from the URL above:

In addition, MySQL 8.0 has added new tools for performance intervention. For example, you can now use the query rewrite plug-in to insert optimizer hint statements without changing the application. Alternatively, there are third-party tools like ProxySQL that can act as an intermediate cache.

For the above reasons, MySQL 8.0 no longer supports query caching. If you upgrade from 5.7 to 8.0, consider using query rewrite or other caches.

The full text is over.

The above are the details of why MySQL canceled Query Cache. For more information about MySQL Query Cache, please pay attention to other related articles on 123WORDPRESS.COM!