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
|
|
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 |
Comments
Post a Comment