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

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!

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: , , , , , ,

Avoid This When Tuning MySQL Query Cache for Performance

13 Responses

  1. We’re on a similar trajectory right now – how did your experiment turn out?

    Rick Cusick July 17, 2014 at 2:37 am #
  2. I got as high as 512MB (not sure why I used 512 and not just 500… habit ha). Anything higher didn’t improve things.

    Hayden James July 17, 2014 at 3:14 am #
  3. Hi.

    What about `Qcache_hits/Qcache_inserts` and Qcache_inserts/Qcache_lowmem_prunes` ratios? What is adequate values for them?

    username January 13, 2015 at 4:46 pm #
    • There’s no rule. Generally look for a high hit rate 60%+ (80%+ is best).

      To calculate The percentage hit rate use:
      ((Qcache_hits/(Qcache_hits+Qcache_inserts+Qcache_not_cached))*100)

      Hits to Insert Ratio:
      Qcache_hits/Qcache_inserts

      …A low ratio shows that the cache is not being utilized much.

      Hayden James January 16, 2015 at 9:52 am #
  4. My specific experience with mysql has been that your application will depend on if it’s going to make good use of QC or not. If you’ve always had it turned on , and seeing good results, you maybe substituting it for good queries and indexing behaviour.

    imran ahmed February 23, 2015 at 11:33 pm #
    • Thanks. True.

      I’m finding more and more from my tests that when using exclusively InnoDB tables its almost always better to disable QC.

      Hayden James February 23, 2015 at 11:50 pm #
  5. Thanks for this. The formula is handy.

    I wrote a query to calculate it on the fly. (Uncomment line 2 to check the maths)

    SELECT FORMAT(((QCACHE_HITS / (Qcache_hits + Qcache_inserts + Qcache_not_cached))*100),2) AS query_cache_hit_rate_percentage
    — , QCACHE_FREE_BLOCKS, QCACHE_FREE_MEMORY, QCACHE_HITS, QCACHE_INSERTS, QCACHE_LOWMEM_PRUNES, QCACHE_NOT_CACHED, QCACHE_QUERIES_IN_CACHE, QCACHE_TOTAL_BLOCKS
    FROM (
    SELECT
    MAX(IF(VARIABLE_NAME = ‘QCACHE_FREE_BLOCKS’, VARIABLE_VALUE, NULL)) QCACHE_FREE_BLOCKS,
    MAX(IF(`VARIABLE_NAME` = ‘QCACHE_FREE_MEMORY’, VARIABLE_VALUE, NULL)) QCACHE_FREE_MEMORY,
    MAX(IF(`VARIABLE_NAME` = ‘QCACHE_HITS’, VARIABLE_VALUE, NULL)) QCACHE_HITS,
    MAX(IF(`VARIABLE_NAME` = ‘QCACHE_INSERTS’, VARIABLE_VALUE, NULL)) QCACHE_INSERTS,
    MAX(IF(`VARIABLE_NAME` = ‘QCACHE_LOWMEM_PRUNES’, VARIABLE_VALUE, NULL)) QCACHE_LOWMEM_PRUNES,
    MAX(IF(`VARIABLE_NAME` = ‘QCACHE_NOT_CACHED’, VARIABLE_VALUE, NULL)) QCACHE_NOT_CACHED,
    MAX(IF(`VARIABLE_NAME` = ‘QCACHE_QUERIES_IN_CACHE’, VARIABLE_VALUE, NULL)) QCACHE_QUERIES_IN_CACHE,
    MAX(IF(`VARIABLE_NAME` = ‘QCACHE_TOTAL_BLOCKS’, VARIABLE_VALUE, NULL)) QCACHE_TOTAL_BLOCKS
    FROM information_schema.global_status
    WHERE VARIABLE_NAME LIKE “QCACHE%”
    ) AS stats;

    Pip May 17, 2015 at 1:57 am #
  6. Nice Article !
    I have shared this article with my friend.
    I have also worked around this and created my research note on MySQL Query Cache.
    Please visit my blog:
    For basic theory:
    http://www.dbrnd.com/2015/08/mysql-query-cache/
    For basic configuration:
    http://www.dbrnd.com/2015/08/mysql-query-cache-configuration/

    F

    Anvesh Patel September 1, 2015 at 5:43 pm #
    • Lately I’ve been converting table types to Aria storage engine with much success.

      Hayden James August 13, 2016 at 12:02 am #
  7. Thanks for this article. At my company we run a Magento store without query cache at the moment. I’d like to turn it on per your recommendations but, have some concerns regarding query_cache_type value.

    Out MySQL server has multiple cores and in addition, multiple Magento servers share the same DB connection. In this case, should the query_cache_type value be 2? A value of 2 (So I’ve read) ensures synchronization between threads.

    Kobe Attias March 28, 2016 at 4:25 am #
  8. This article has been refreshed.

    Hayden James August 12, 2016 at 8:53 pm #
Trackbacks/Pingbacks
  1. WordPress on Speed: 17 Tweaks to Accelerate your Blog - January 5, 2016

    […] Tweak your query cache. The query cache is designed to save the results of queries, but slight changes in these queries aren’t compiled and can create multiple cache entries for very similar requests. Don’t go too high, though: this guide offers good advice on tweaking incrementally. […]

Leave a Reply