MySQL Caching

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.

• getting the text, picture, etc from the internet is the slowest way to get that information and takes the longest for the web page to load
• getting the text, picture, etc from a cache store which is on your local hard disk greatly improves the web page loading as it does not need to use the slow internet
• getting the text, picture, etc from a cache store in the computers memory is the fastest way to load a web page it does not use the very slow internet or the slow hard disk

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

• accessing data from the local hard disk is like using a horse and cart
• accessing data from cache (memory) is like using a ferrari car

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

• the SQL query itself
• the data that needs to be retrieved

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

• use of non-deterministic functions
• most subqueries
• use of user-defined functions
• use of temporary tables
• select statements in stored functions, triggers, views and events
• using lock in share mode or for update
• result sets larger than the query_cache_limit (1MB by default)
• using sql_no_cache

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
query_cache_type	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

• the first cause is that the result sets do not always fit exactly into a block of memory and thus have to be split up
• the second cause is the invalidation of queries which causes the deletion of queries in the cache leaving memory holes after the deletion

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