One of the most misconfigured MySQL performance features, is the query cache. Just last week 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 seemed to be that more is better and that since the server had free RAM available, setting the query cache very large meant that the cache wouldn’t be pruned constantly, 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 to no more than around 200MB!
A MySQL query cache size of 200 megabytes may even be too large! The key is to start very small (maybe 20mb), 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 can hurt performance when trying to scale with the increased number of cacheable queries. Too large query cache sizes will lead to significant performance degradation because of overhead required to manage the cache. Cacheable queries have to 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 will be used for these flushes, until cache management eventually negates all benefits of MySQL query caching and instead begin to eat away at database performance.
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 hit rate is below 50%.
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 are all different 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 also adjust your “query_cache_limit” because the default of 1 megabyte may be too large and allow your cache to fill up fast causing crippling query cache prunes! Yes, sizing the query cache too small can also be a performance killer. This is the case especially if you have thousands of query cache prunes being performed. Also, have a look at adjusting the “query_cache_min_res_unit” tuning parameter to combat cache fragmentation and reduce prunes if that’s an issue. 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!
With regards to performance of PHP web applications, with WordPress for example, I’m using caching plugins on the blogs I manage that help to reduce the # of queries that make it through to query cache. Next I will be writing about what I think are the best WordPress caching plugins which work well in this regard. Subscribe here.
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%';
If you are 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.
I worked with a busy server today. The client has a Magento based store and lots of custom queries along with over 100,000 items in inventory. The database is very slow on certain queries. Its also running a very old version of Magento which has some know performance bugs (they are in progress of upgrading to 1.8). Long story short, MySQL became unresponsive, I increased MySQL’s query cache size to 256MB and is helping nicely. As noted above, its a fine line to thread when increasing query cache but the thought I had here was that since MySQL just can’t deal with too many heavy/slow queries concurrently, that the performance penalty of using a larger query cache to reduce the number of repeat queries on MySQL was NOT as expensive as having MySQL take 5+ seconds returning repeat queries over and over and that at the least the query cache could help reduce the qps (queries per second) demand on MySQL.
After this change the load and CPU usage as dropped noticeably. As mentioned above “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.” In this case, late tonight I will try 300MB and leave it for a day or two. However, on a different server/database where MySQL has no slow queries this setting of 300mb could actually hurt performance. The long term solution is to address the inefficient queries.
So, your hardware, database, queries, traffic, etc will play a part in determining how large of a cache size will be useful. If MySQL is suffering from overload and is already slow, then it makes a good case to slowly increase query_cache size. This buys you some time to look into addressing slow queries.