MySQL Query Cache

MySQL has a very useful functionality - query cache . Query cache is available in MySQL since version 4.0.
Many DBMSs have similar functionality, but unlike MySQL, they cache query execution plans, whereas MySQL caches query results.
Further on how the query cache works, how to configure it and make optimal use of it.

How does the query cache.
The query cache can be thought of as a hash, the keys of which are queries, and the values ​​are the results of queries.
If query cache usage is enabled, then, upon receiving a query, MySQL determines whether the first three characters of the query are "SEL". If yes, then MySQL looks to see if there is an entry in the query cache with a key equal to the query. From here follow two important rules:

• MySQL performs byte-by-byte comparison, so queries that differ in at least one character (for example, SELECT * FROM table and select * from table) will be treated as two different queries. Therefore, it is necessary to write queries in the same style;
• In MySQL to version 5.0, queries that have a space at the beginning or a comment is written will never be taken from the cache.

In addition to the results, MySQL stores in cache the list of tables, a selection of which is cached. If any of the tables, the sample of which is in the cache, undergo changes (insert or change rows), then MySQL removes such samples from the cache. This approach speeds up the work of MySQL, but may be ineffective for systems with a large number of requests for changing tables.

Optimization for such tables - to carry out frequently changing data in a separate table. For example, there is a table of articles, which also stores the number of comments to it. Since the number of comments changes frequently, and the article itself hardly changes, it makes sense to put the number of comments in a separate table, while avoiding the frequent invalidation of the articles cache.

Using the query cache is completely transparent to the client. The client does not know whether he executed the MySQL request, or returned it from the cache.
')
MySQL caches the results of SELECT queries only. MySQL does not cache queries whose results may change. For example, queries that use functions related to the current time (NOW (), CURDATE (), etc.), to the current connection (CURRENT_USER (), CONNECTION_ID (), etc.) and others. A complete list of such features can be found in the manual. In addition, MySQL does not cache queries that include the use of user-defined functions, stored procedures, samples from the mysql or INFORMATION_SCHEMA database, samples from tables for which privileges are defined for columns.

Optimization for queries using the current time functions (NOW (), CURDATE (), etc.) - replacing such functions with a string with a date. For example: request
SELECT * FROM table WHERE create_date> NOW () - INTERVAL 1 DAY
which will not be cached can be replaced with a request that is cached:
SELECT * FROM table WHERE create_date> '2009-10-14' - INTERVAL 1 DAY

Caching samples from InnoDB tables is handled separately. MySQL deletes the results of the samples from the cache when any changes are made to the table inside the transaction (although it could not be deleted until the transaction is committed). In addition, all samples from this table will not be cached until the transaction is committed.

Using the query cache.
You can view statistics on the use of the query cache by running the command:
SHOW GLOBAL STATUS LIKE 'Qcache%'

• Qcache_free_blocks shows how many free blocks are in the cache;
• Qcache_total_blocks - the number of occupied blocks;
• Qcache_free_memory tells you how much free memory is left in the cache;
• Qcache_hits - the number of queries whose results were taken from the cache;
• Qcache_inserts - the number of requests that were added to the cache;
• Qcache_lowmem_prunes - the number of requests that were deleted from the cache due to lack of memory;
• Qcache_not_cached - the number of requests that were not written to the cache due to the use of time management functions, etc .;
• Qcache_queries_in_cache - the number of requests that are in the cache.

The following configuration variables are responsible for using the query cache:

• query_cache_type = (ON, DEMAND, OFF) - determines whether caching is enabled or not (ON, OFF). When using DEMAND, only queries that have the SQL_CACHE directive will be cached;
• query_cache_size - size of the query cache. query_cache_size = 0 disables the use of the cache;
• query_cache_limit - the size of the maximum sample stored in the cache;
• query_cache_min_res_unit - the minimum size of the block stored in the cache;
• query_cache_wlock_invalidate - determines whether data will be taken from the cache if the table to which they belong is locked for reading.

When you start MySQL, it allocates a block of query_cache_size size in memory. When the query is executed, as soon as the first result lines are received, the server starts to cache them: it allocates a block of memory equal to query_cache_min_res_unit in the cache, writes the result of the selection to it. If not all of the sample fit into the block, the server allocates the next block, and so on. After the entire result is written to the cache, MySQL allocates the free part of the last block into a separate block and marks it as free. If there is a free block around such a block, MySQL merges them into one.

At the time of the start of the recording, MySQL does not know about the size of the resulting sample. If the sample size recorded in the cache is larger than query_cache_limit, then the recording stops and the space occupied is freed (therefore, if you know in advance that the result will be large, it is recommended to execute it with SQL_NO_CACHE directive). If MySQL caches several queries in parallel, the blocks allocated to different queries can alternate. In addition, after the request is removed from the cache, the free space may not be sufficient to record new requests. This leads to cache fragmentation. To defragment the cache, you can run the FLUSH QUERY CACHE command. (FLUSH QUERY CACHE transfers all requests stored in the cache to its beginning and marks the remaining memory as one free block). In addition, cache fragmentation can be reduced by correctly selecting the query_cache_min_res_unit parameter. If the query_cache_min_res_unit value is small, then the fragmentation will decrease, however, MySQL will have to create more blocks in the cache. If the value is large, then fragmentation will be large.

The value of query_cache_min_res_unit should be equal to the average size of the cache value. Its approximate value can be calculated by the formula query_cache_min_res_unit = (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache. However, for sites whose sample size varies greatly, it is recommended to use query_cache_type = DEMAND and an explicit indication that the query should be cached using SQL_CACHE directive. In addition, it is necessary to restrict writing to the cache of large samples by specifying the variable query_cache_limit or the SQL_NO_CACHE directive.

Determine how fragmented the cache is by the value of the variable Qcache_free_blocks. For an ideal unfragmented cache, the value is equal to one, at worst - Qcache_total_blocks / 2. You can also determine that your query cache is highly fragmented if the value of Qcache_lowmem_prunes is constantly increasing, while the value of Qcache_free_memory is far from zero.

You can evaluate the efficiency of using the cache using the formula Qcache_hits / (Qcache_hits + Com_select). What value is sufficient for your site is up to you. If the queries stored in the cache take a long time, then efficiency even at 10% may be useful. However, if the efficiency of use is low and it cannot be increased, it is possible that the nature of the load on your system is such that the query cache is not at all effective for you. In such cases, it is more useful to disable caching requests altogether and use client-side caching.

Read the original article at MySQL Consulting.

PS write in lichku topics on MySQL articles that you would like to read.