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:
Three individual options can be set:
- 0 or OFF: This stops the query cache by not caching query results or retrieving results from the query cache.
- 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.
- 2 or DEMAND: Caches results only for cacheable queries that begin with SELECT SQL_CACHE …
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.
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.
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.
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:
Displays the number of times that results were delivered from the query cache
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.
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.
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.
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.
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.
Displays the number of query result sets that are currently stored in the cache.
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.