888-685-3101, ext. 2

MySQL Query Cache – Use it or Disable it?

by | Oct 19, 2015 | Blog, Databases

Having frequently repeated SELECT queries and their results stored in memory can significantly optimize performance on your MySQL database. The query cache will perform such a task however, you must analyze whether your query cache is effective for your workload or if it is causing too much overhead. There are many different factors involved in finding optimal settings or in deciding whether it would be best to just disable it.

Here are a few basics to get you started in determining the effectiveness of your current query cache settings:

  1. By default the query cache is disabled, you must enable it.The following variables should be examined to determine the availability of the query cache. You can find the values of these status variables in MySQL with SHOW GLOBAL VARIABLES;Have_query_cache (YES or NO)Query_cache_type (ON or OFF)

    Query_cache_size (A setting of 0 disables the query cache; A minimum value of 40KB is required.)

  2. Query Cache Hit PercentageThis percentage will give you an idea of the number of SELECT queries that have their results retrieved from the query cache. You can find the values of these status variables with SHOW GLOBAL STATUS;qcache_hits /  (qcache_hits  +  com_select) x 100

    A high percentage is ideal however if complex and long-running queries are repeated and being handled by the query cache, a low hit percentage may still be worth the resources the query cache uses.

  3. Hit to Insert RatioThe ratio of qcache_hits to qcache_inserts is an indicator of whether queries are being found in the cache (a hit) or if they are not found and hence being cached (an insert).Ideally there will be more hits than inserts so, for example, a ratio of 10:1 would be significant. A ratio of hits to inserts that is lower than 10:1 does not necessarily mean that the query cache is ineffective. There may be just a few complex queries that are repeated and requested enough to remain in the cache that are worth the overhead. However, if you are seeing more inserts than hits, it may be best to disable the query cache.
  4. Query_cache_limitThis variable sets the maximum size allowed for a query to be stored. You can calculate the average size of queries in the cache using: (Query_cache_size – qcache_free_memory)/  qcache_queries_in_cacheIf the average size calculated is close to the query_cache_limit, and the variables qcache_lowmem_prunes and qcache_not _cached are increasing, the query_cache_limit may be one variable worth increasing to cache more queries.

Summary

Is your MySQL query cache worth the resources utilized for frequent access and maintenance?

The ideal scenario for utilizing a query cache would be one where there are many identical SELECT statements run on tables that have few INSERT, UPDATE or other changes. If you find that you have a low query cache hit percentage and a low hit to insert ratio, it is worth looking into other query cache settings and variables and also benchmarking the time required to run SELECT statements when the query cache is off. Every server workload differs and taking the time to analyze an active query cache is an area of optimization that is worth the time.