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

To see how useful your query cache is

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.