Avoid This When Tuning MySQL Query Cache for Performance

One of the most misconfigured MySQL performance features, is MySQL Query Cache Size. Just last month I was optimizing a web server with 32 gigabytes of RAM where the existing config had MySQL’s query cache size set to 4 gigabytes. The thought behind it seems to be that more is better. Since the server has free RAM available, setting MySQL Query Cache Size very large reduces 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, 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.

Too 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 database 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.

MySQL Query Cache monitor - Logicmonitor

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

Related:  81% of all Smartphones are powered by Linux

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.

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 this 200mb 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, set your slow query logging to 1 second or setup full query logging for a few minutes or hours (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!

Related:  Linux server performance: Is disk I/O slowing your application?

MySQL Query Cache config & Monitoring

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

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.


Tags: , , , , , ,