MySQL Query Cache for Performance, Avoid This When Tuning

Update: MySQL query cache is deprecated as of MySQL 5.7.20, and is 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 the existing config had MySQL’s query cache size set incorrectly to 4 gigabytes. The thought behind it seemed to be that more is better. Since the server had free RAM available, setting MySQL Query Cache Size very large would reduce cache prunes, thus increasing performance. THAT’S WRONG!

This is an extremely common misconception and understandably so, because query cache sizing depends a lot on your database size, database query types, the ratio of database reads vs writes, database traffic, hardware, etc.

Set MySQL Query Cache Size no larger than 100 to 200MB!

A MySQL query cache size of 200 megabytes may even be too large! The key is to start very small (maybe 10mb), then increase in small increments while trying to keep a high ratio of query cache hits and also a low amount of query cache low memory prunes. All of this without setting MySQL’s query cache size too large. Why? Because a 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 because of cache overhead and locking. Cacheable queries take out an exclusive lock on MySQL’s query cache. In addition, any insert, update, delete, or other modifications to a table causes any relevant entries in the query cache to be flushed. This happens even when there’s free query cache space available. As a result, the larger the query cache, the more system time is used for locks, flushes, and overhead until cache management eventually negates any benefit of MySQL’s query cache. Instead, it begins to eat away at throughput.

On the 32GB server mentioned above, with MySQL Query Cache Size set to 4GB, there were 100’s, sometimes 1000’s of queries with status “Waiting for query cache lock”. This causes PHP-FPM spikes as they wait on MySQL. Reducing MySQL Query Cache to 100 megabytes and lowering “query_cache_min_res_unit” and “query_cache_limit” solved the severe locking issues. I could have disabled Query Cache completely, but with the new settings, there’s still a +70% hit rate. However, eventually, MySQL Query Cache was completely disabled on that 32GB server for improved performance (read on):

query_cache_type                = 0
query_cache_size                = 0

MySQL Query Cache monitor by Logicmonitor

Even with a nicely tuned query cache, there’s still around 10% to 15% overhead required to maintain it. 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. That being the percentage of queries served by cache instead of being re-executed by the database repeatedly. Of course, anything below 10% or 20% means your query cache is probably doing more harm to performance than good. Usually, I’ll keep query cache disabled if the hit rate is below 50% or… if prunes/purging rate is still high after increasing query cache size to a safe limit.

Oracle’s MySQL Query Cache sizing recommendation

So again, why is it recommended to keep MySQL’s query_cache_size small? Well, here’s what MySQL’s reference manual says about sizing the query cache:

“Be cautious about sizing the query cache excessively large, which increases the overhead required to maintain the cache, possibly beyond the benefit of enabling it. Sizes in tens of megabytes are usually beneficial. Sizes in the hundreds of megabytes might not be.“

Also have a look at MariaDB’s (alternative MySQL drop-in replacement ) help page: MariaDB Memory Allocation – under the Query Cache section.

Now, due to all the variations of MySQL setups, hardware specs, and the fact that databases differ in demand, query types, etc etc, you will have to play around with your config and use the 200M max recommendation as a guideline to finding your query_cache_size sweet-spot. A MySQL query cache size of 200 megabytes may be fast on one server and begin to slow things down on another. As such, you’ll need to investigate. Setup slow-query logging and/or set up full query logging for a few minutes (depending on how busy your database is) and see how much time is spent in the query cache.

Query caching can give significant performance improvements when used correctly and/or in conjunction with Memcached or Redis cache. As mentioned, the key is, when you start tuning your MySQL query cache size, start small. You should adjust your “query_cache_limit” because the default of 1 megabyte may be too large. Allowing your cache to fill up too fast creates lots of cache prunes. Also, have a look at adjusting the “query_cache_min_res_unit” tuning parameter to combat cache fragmentation. Many times you won’t be able to store all cacheable queries in the query cache, but you can still make good use of it. Test, test, test!

MySQL Query Cache config & Monitoring

Here’s my query cache config used on another server to host this blog (This server also hosts a few other small to medium WordPress blogs. In total serves about 2 million page views per month)

This blog is now on hosted on its own tiny StackLinux VPS, database storage engine is InnoDB and query cache has been disabled (see above two config lines to disable if needed):

query_cache_type = 1
query_cache_limit = 256K
query_cache_min_res_unit = 2k
query_cache_size = 80M

To make sure MySQL Query Cache is enabled use:

mysql> SHOW VARIABLES LIKE 'have_query_cache';

To monitor query cache stats use:

mysql> SHOW STATUS LIKE 'Qcache%';

Not sure what to do with the stats returned? I recommend using mysqltuner.pl. This script will help you to avoid the most obvious MySQL performance pitfalls (including query cache sizing) and get you to where at least your database isn’t poorly configured. Also, see MariaDB (MySQL) Memory Allocation.

Here’s a screenshot where I used Netdata (listed on the 100 Top Server Monitoring & Application Monitoring Tools) for query cache overview…