Update: The MySQL query cache has been deprecated as of MySQL 5.7.20, and removed in MySQL 8.0. , mysql.com
One of the most misconfigured MySQL performance features is MySQL query_cache_size. This post references a web server with 32 gigabytes of RAM where MySQL’s query cache size was incorrectly set to 4 gigabytes in the current configuration. The thinking behind it seemed that more is better. Since the server had free RAM available, setting the MySQL query cache size too large would reduce the cache prune, thus increasing performance. This is wrong!
This is a very common misconception and makes sense, as the query cache size depends on your database size, database query type, ratio of database writes vs writes, database traffic, hardware, etc.
Set the MySQL query cache size to no more than 100 to 200MB!
A MySQL query cache size of 200 megabytes can be huge too! The key is to start out very small (maybe 10MB), then increase in small increments while trying to keep the high ratio of query cache hits and the low amount of query cache low memory prune. This is all set up without making MySQL’s query cache size too big. Why? Because query_cache_size of 4 gigabytes is a good example of how query caching cripples performance when trying to scale.
A large query cache size leads to significant performance degradation. This is due to cache overhead and locking. Cacheable queries take out an exclusive lock on MySQL’s query cache. In addition, any insert, update, delete, or other modification to a table will cause any relevant entries in the query cache to be flushed. This happens even if there is free space available for the query cache, As a result, the larger the query cache, the more system time is used for locks, flushes, and overhead until the cache management eventually negates any advantage of MySQL’s query cache. Instead, it starts eating up on throughput.
On the 32GB server mentioned above, with MySQL Query Cache Size set to 4GB, there were 100, sometimes 1000, queries with a “waiting for query cache lock” status. This causes PHP-FPM spikes as they wait on MySQL. Reducing the MySQL query cache to 100 megabytes and lowering “query_cache_min_res_unit” and “query_cache_limit” fixed serious locking issues. I could disable the query cache completely, but even with the new settings, there’s still a +70% hit rate. However, eventually, the MySQL query cache was completely disabled on that 32GB server for better performance (read):
query_cache_type = 0 query_cache_size = 0
Even with a well-tuned query cache, it still requires about 10% to 15% overhead to maintain. So your query cache hit rate percentage ((Qcache_hits / (Qcache_hits + Qcache_inserts + Qcache_not_cached))*100) should be as close to 100% as possible. It is the percentage of queries served by the cache rather than being executed repeatedly by the database. Of course, less than 10% or 20% means that your query cache is probably hurting performance more than good. I would keep the query cache disabled if the hit rate is less than 50% or… if the prunes/purging rate is still high after increasing the query cache size to a safe limit.
Oracle’s MySQL Query Cache Size Recommendation
Then again, why is it recommended to keep MySQL’s query_cache_size small? well what’s here MySQL Reference Manual About sizing the query cache says:
“Be careful about sizing the query cache too large, which increases the overhead required to maintain the cache, possibly beyond the benefit of enabling it. Sizes in the tens of megabytes are usually beneficial. size in hundreds of megabytes might No yes.,
Also take a look at the MariaDB (alternative MySQL drop-in replacement) help page: MariaDB Memory Allocation – Under Query Cache section,
Now, due to MySQL setup, hardware specs, and the fact that databases vary in demand, query type, etc., you’ll have to play with your configuration and use the 200M max recommendation as a guideline. query_cache_size sweet-spot. A MySQL query cache size of 200 megabytes can speed up on one server and start slowing things down on another. In that case you will have to investigate. Setup slow-query logging and/or set full query logging to a few minutes (depending on how busy your database is) and see how much time is spent in the query cache.
Query caching can deliver significant performance improvements when used correctly and/or in conjunction with memcached either say it again Cash. The key, as mentioned, is that, when you start tuning your MySQL query cache size, start small, You should adjust your “query_cache_limit” as the default of 1 megabyte may be too large. Allowing your cache to fill up too quickly creates a lot of cash prunes. Also, take a look at adjusting the “query_cache_min_res_unit” tuning parameter to deal with cache fragmentation. Many times you won’t be able to store all the cacheable queries in the query cache, but you can still make good use of it. Test, test, test!
MySQL Query Cache Configuration and Monitoring
Here is my query cache configuration I used on another server to host this blog (This server also hosts a few other small to medium WordPress blogs. In total it serves about 2 million page views per month )
This blog is now on its own little host Stacklinux VPSThe database storage engine is InnoDB and the query cache has been disabled (see above two configuration lines to disable if necessary):
query_cache_type = 1 query_cache_limit = 256K query_cache_min_res_unit = 2k query_cache_size = 80M
To make sure the use of MySQL Query Cache is enabled:
mysql> SHOW VARIABLES LIKE 'have_query_cache';
To monitor query cache statistics use:
mysql> SHOW STATUS LIKE 'Qcache%';
Not sure what to do with the returned data? I recommend to use mysqltuner.pl, This script will help you avoid the most obvious MySQL performance pitfalls (including query cache sizing) and will at least get you where your database isn’t poorly configured. See more MariaDB (MySQL) memory allocation,
Here is a screenshot where I used netdata (Listed on the 100 Top Server Monitoring and Application Monitoring Tools) For an overview of the query cache…