The Geek Diary

Understanding MySQL Query Cache

by

What is MySQL Query Cache

MySQL server features Query Cache Feature for a long time. When in use, the query cache stores the text of a SELECT query together with the corresponding result that is sent to a client. If another identical query is received, the server can then retrieve the results from the query cache rather than parsing and executing the same query again.

It caches the full result set produced from a SELECT query:

  • Queries executed must be identical.
  • Cache is stored in system memory.
  • Cache is checked before the query is parsed.

Associated table updates invalidate query cache results. Prepared statements can be cached, but limitations exist. Some statements that do not use the query cache are:

  • Queries that use nondeterministic functions.
  • Queries that are a subquery of an outer query
  • Queries that are executed within the body of a stored function, trigger, or event

MySQL Query Cache Settings

The query cache adds a few MySQL system variables for mysqld which may be set in a configuration file, on the command-line when starting mysqld:

1. query_cache_type

Three individual options can be set:

  1. 0 or OFF: This stops the query cache by not caching query results or retrieving results from the query cache.
  2. 1 or ON: Caches all cacheable query results except for those that begin with SELECT SQL_NO_CACHE … This is the default option for backward compatibility.
  3. 2 or DEMAND: Caches results only for cacheable queries that begin with SELECT SQL_CACHE …
Note: This option does not deallocate the query cache buffer but does prevent the fast taking and releasing of the query cache mutex.

Starting MySQL server with –query-cache-type=0 does not acquire the query cache mutex, which reduces the overhead during query execution. Up to 13% improvement is possible.

2. query_cache_size

Amount of memory allocated for caching query results:

  • The default value is 0, which disables the query cache.
  • Setting this value too low (anything under approximately 40 KB) results in a warning being issued.
  • Setting this value too large can result in all queries blocked while invalidating results from changed tables.
  • All values should be entered as multiples of 1024. Any other values are rounded down to the nearest multiple.
  • On most production servers, 8 MB is fine. But 128 MB to 200 MB can also be acceptable if there is ample evidence to support using such a large value.

query_cache_limit

The maximum result set size to cache. The default is 1 MB. Keeping this at a reasonable number (1 MB to 2 MB) avoids erasing the entire query cache due to a large query being stored. Actual size depends on application needs and SELECT result set sizes.

When Not to Use the MySQL Query Cache

The query cache should be disabled in a server with many fast queries and many CPU cores. The query cache runs in a single thread and can severely limit throughput in a server. Below is a list of scenarios when ideally query caching should not be used.

Numerous fast queries and multiple CPU cores

Most servers running MySQL use multiple CPU cores. Using the MySQL query cache in these servers would severely degrade performance.

Multiple writes, updates, or deletes

The overhead associated with the query cache will degrade performance.

Numerous different SELECT queries

Every result set needs to be indexed in the query cache. If more complete result sets are being stored in the query cache, performance gains will be smaller.

Locked tables

The query cache is checked before the query is parsed, which can result in a previous result set being outputted before the server ever checks to see if the table is locked. When the query_cache_wlock_invalidatesystem variable is turned on, the query cache is invalidated if any object that it uses has a write lock executed against it.

When to Use the MySQL Query Cache

Below are the situations when you can use the MySQL query cache feature:

  • When the database must repeatedly run the same queries against the same data set. It saves the MySQL server from having to retrieve the same data set over and over again.
  • When the result set is relatively small and manageable by the query cache. Having large result sets in the query cache can have a negative effect on performance.
  • While reading intensive web applications, Blogs, eCommerce applications, advertising applications, and so on.

MySQL Query Cache Status Variables

You can monitor query cache performance in SHOW STATUS:

Qcache_hits

Displays the number of times that results were delivered from the query cache

Qcache_inserts

Displays the number of times that results were stored to the query cache. If the number of query cache hits is low and the number of query cache inserts is high, you should disable the query cache.

Qcache_lowmem_prunes

Displays the number of stored result sets that were deleted from the query cache due to low memory. If this number is high, you can increase the query cache size.

Qcache_free_blocks

Displays the number of free contiguous blocks of memory in the query cache. The allocation time for new queries is proportional to the value that is entered in this system variable.

Qcache_free_memory

Displays the amount of free memory for the query cache. If free memory is small (in relation to the query cache size), you should increase the query cache size.

Qcache_not_cached

Displays the number of SELECT queries that did not have their result sets stored in the query cache. These queries could have been left out of the query cache because they were not cacheable or based on the setting of the query_cache_type setting.

Qcache_queries_in_cache

Displays the number of query result sets that are currently stored in the cache.

Com_select

Displays the number of SELECT statements executed by the MySQL server. This does not include queries that were served from the query cache. Comparing Com_select and Qcache_hits indicates the efficiency of the query cache on your server. Add the Com_select and Qcache_hits status values together to obtain the number of SELECT statements executed against the server.

Improve Query Cache Results

Standardize the queries that are executed against the server. If possible, remove anything that prevents the query cache from being able to store the result set.

Minimize the number and frequency of updates to the data. Batch updates together to minimize the query cache result sets from being invalidated on a frequent basis.

You May Also Like