Query Cache : Best Friend or your Enemy

Note

The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0.

Introduction to Query Cache

The MySQL query cache is a query results cache. It compares incoming queries that start with SEL to a hash table, and if there is a match returns the results from the previous execution of the query. There are some restrictions:

  • The query must match byte-for-byte (the query cache avoids parsing)
  • Use of non-deterministic features will result in the query not being cached (including temporary tables, user variables, RAND()NOW() and UDFs.)
  • The query cache was designed to not serve stale results.  Any modification to the underlying table(s) results in all cache being invalidated for those tables.
  • There are some restrictions on if the cache can be used for InnoDB (to respect MVCC; as you have a transaction open, the ‘cache’ might not represent the data in your expected view.)

The ideal scenario for the query cache tends to be largely read-only, where there are a number of very expensive queries which examine millions of rows only to return a few. A hypothetical example might be a complex query to build a list of values for a drop-down list that always appears on a webpage form. In a situation like this, the query cache can mask performance problems caused by missing indexes, which makes it helpful for novice users.

Reference : https://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-cache/

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 Percentage

This 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 Ratio

The 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_limit

This 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_cache

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s