MySQL Caching

To make queries faster to respond is to use caching, a cache stores frequently used data in a place that is local where is can be accessed faster. Cached data can be stored either on a computers local hard disk or in the computers internal memory. For example a web browser stores text, images and other objects of recently viewed web pages locally on your hard drive, thus the next time you visit the web and the information is still the same, the browser will retrieve the text, image, etc from the local hard disk instead of having to go out on the internet to get them which is slower. This means that the first time you retrieve data it will always be slower than when you retrieve it the second time as the data will be cached.

MySQL is no different in that the larger the cache the more data that can be stored and hopefully when a user runs a SQL query the information will be cached in memory and not on the local hard drive, the way to think of accessing data when using a database is the following

Thus you want to get that data that the users needs into memory which will greatly give a better performance and experience to your end user, now as databases can be very large it some times impossible to get all the data into cache, however if you can get the most used data into cache then the users experience will be greatly improved, you don't want your user hanging around waiting for information otherwise he/she may go somewhere else which could be a disaster for your business.

MySQL implements caching where frequently used data can be stored, there are two parts to a query

Both parts are cached, if a query is the same executed by two different users then mysqld can use the query cache to obtain the already prepared query without running this process again which costs time, hopefully the data retrieved will be pretty much the same, again this data should still be in the data cache (memory) which means for faster retrieval. By default the query cache is not enabled because it does add more overhead, more memory is used and it does take processing time to check the query cache, if the query cache is checked frequently and matches are rarely found then there is no need to use the query cache, it could hurt performance instead of helping it. So if you think that users are using the same queries over and over again you can turn this option on and then test thoroughly, in some cases it will produce amazing performance improvements.

The MySQL query cache is a simple operation and it only caches select statements and their corresponding result sets and only for deterministic select statements. Deterministic means that the same query will always produce the same set of results no matter when given the same data set, for example select count(*) from table is always the same give the same data set, but select now() is not always the same.

The query cache stores a hash of the query along with the result set, this means that the query must be the same byte for byte for it to match in the cache this includes matching case and whitespaces, etc. Select statements with the following properties are not cached

mysqld uses query cache invalidation to keep a check on the cache, it will remove cached queries if a table is modified that it affects, query cache pruning is when a query is removed because the query cache cannot find a free block in which to store the new query, the query cache prunes older queries entries using a least recently used (LRU) algorithm, this is how it keeps the top queries in the cache.

The query cache for the server is stored completely in a single continuous chunk of memory and does not persist across reboots, this block of memory is then divided up into smaller blocks. These blocks are used to store the result sets of cached queries, each block must be at least the size specified by query_cache_min_res_unit which defaults to 4KB. In addition to these blocks containing result sets of queries there are two hash tables, one stores the query hashes and the other contains the list of databases tables that each cached query references.

Here is a list of the system variables that affect the query cache

Query cache system variables
variable name description
query_alloc_block_size specifies the size of memory blocks allocated by the query cache during query processing, default is 8KB
query_cache_limit results sets larger than this are not cached, default is 1MB
query_cache_min_res_unit specifies the minimum size for blocks in the query cache, default 4KB
query_cache_size total memory allocated by mysqld for the query cache, the default is 0 which disables the query, this is a dynamic variable and can be changed without restarting mysqld

determines the mode of operation, default is ON

  • OFF - turns off the query cache
  • ON - enables the the query cache for select statements, you must set query_cache_size
  • DEMAND - do not cache any queries except for a few chosen ones, must the SQL_CACHE on your statement
query_cache_wlock_invalidate if set to true queries referencing MyISAM tables are invalidated, when a write lock for that table is obtained even if none of the data is changed, default is false
query_cache_prealloc_size specifies the size of the buffer used for query parsing by the cache, default 8KB
Query cache status variables
counter name description
Qcache_free_blocks Number of memory blocks free in the query cache
Qcache_free_memory total bytes of memory memory in the query cache
Qcache_hits number of times a query matched the query cache
Qcache_inserts number of times a query and result set were inserted into the query cache
Qcache_lowmem_prunes number of times a query was removed due to query pruning
Qcache_not_cached number of queries that could not be cached
Qcache_queries_in_cache number of queries currently stored in the query cache
Qcache_total_blocks total number of memory blocks in cache

To see how useful your query cache is

% of select queries that are getting their results from the query cache

This rate is known as the query cache hit ratio, try to get it as high as possible by caching more queries

Qcache_hits / (Qcache_hits + Com_select)

% of select queries that are placed in the query cache

this rate is known as the insert ratio, if high then most of the queries are being put into the query cache

Qcache_inserts / Com_select * 100

% free memory left in the query cache

calculate the free memory in bytes and blocks, if the percentage is high either lower the query_cache_size so that you are not wasting memory or cache more queries, if low you may need to increase the query_cache_size or make the query_cache_limit size smaller

Qcache_free_memory / query_cache_size * 100
Qcache_free_block / Qcache_total_blocks * 100

Lastly we discuss the fragmentation of the query cache, there are two causes of fragmentation

You can defrag the cache by running the below commands, flushing does not clear the cache but compacts the cache by locking the query cache and re-ordering it so there are no gaps between the blocks of memory, this may take a lot of time depending if the cache is large or not, resetting the cache empties it. MySQL recommend a maximum query cache size of 256MB.

defraging the cache ## re-order the query cache
flush query cache

## empty the query cache
reset query cache