MySQL Query cache detailed

One: Caching conditions, principle

MySQL query cache is used to cache the SELECT statement we execute and the result set of the statement, and MySQL is similar to typical KV storage in the specific technical details of implementing the query cache, is to make a hash map of the SELECT statement and the result set of the query statement and save it in a certain area of memory. When the client initiates the SQL query, the lookup logic of query cache is that the SQL is appropriately authenticated and then the query cache is followed to find the result (note that it must be exactly the same, even if more than one space or case is different, even the exact same SQL , if different character sets, different protocols, and so on are considered to be different queries and are cached separately). It does not need to go through the optimizer module to perform the analysis optimization of the plan, more do not need to occur with any storage engine interaction, reduce a lot of disk IO and CPU operation, so sometimes very efficient.

The workflow for query caching is as follows:

1: Hit condition

The cache exists in a hash table, by querying SQL, querying the database, the client protocol, and so on as key. Before deciding whether to hit, MySQL will not parse SQL, but directly use SQL to query the cache, any character on the SQL, such as spaces, comments, will cause cache misses.

If there are indeterminate data in the query, such as the Current_date () and now () functions, then the query will not be cached after completion. Therefore, queries that contain indeterminate data are sure not to find the available caches

2: Work Flow

1. server receives SQL, with SQL and some other criteria for key Lookup cache table (additional performance consumption)

2. If the cache is found, return the cache directly (performance boost)

3. If the cache is not found, execute the SQL query, including the original SQL parsing, optimization, and so on.

4. After executing SQL query results, put SQL query results into the Cache table (additional performance consumption)

Two: Cache parameters

1: View current query cache related parameter status:

SHOW VARIABLES like '%query_cache% ';

2: Cache configuration parameter explanation

1. Query_cache_type: Query cache type, whether to open cache

Options available

A, 0 (off): Turn off the query cache function, do not use the query cache under any circumstances;
B, 1 (ON): The query cache function is turned on, but when the sql_no_cache hint is used in the SELECT statement, query cache is not used;
C, 2 (DEMAND): Opens the query cache feature, but uses the query cache only if the Sql_cache hint is used in the SELECT statement.

Note 1:

If Query_cache_type is on and you do not want to take advantage of the data in the query cache, you can use the following sql:

SELECT Sql_no_cache * from my_table WHERE condition;

If the value is 2, you need to use the Sql_cache switch parameter to use the cache:

SELECT Sql_cache * from my_table WHERE condition;

2. Query_cache_size: The total amount of memory used by the cache, in bytes, this value must be an integer multiple of 1024, or MySQL will automatically adjust the minimum amount to achieve a multiple of 1024; (This should be related to the file system's Blcok size)

3. Query_cache_min_res_unit: The smallest unit size when allocating memory blocks, sets the minimum space size of the query cache for each allocation of memory, that is, the minimum amount of memory space for each query cache;

4. Query_cache_limit: The maximum capacity of a single query result set that is allowed to be cached by default is 1MB, and the query result set that exceeds this parameter setting will not be cached;

5. Query_cache_wlock_invalidate: If a data table is locked, the data is still returned from the cache, the default is off, which means that you can still return

Controls whether a write lock occurs at a time on the table if the query cache associated with the table is invalidated first, and if set to 1 (TRUE), all query caches related to the table will be invalidated at the same time as the write lock, if set to 0 (FALSE) The query Cache associated with the table is still allowed to be read at the time of the lock.

The parameter explanation for the cache in GLOBAL Staus:

Qcache_free_blocks: Number of free blocks in the cache pool

Qcache_free_memory: Amount of free memory in cache

Qcache_hits: Cache Hit count

Qcache_inserts: Number of Cache writes

Qcache_lowmen_prunes: Cache count deleted due to insufficient memory

Qcache_not_cached: The number of times the query has not been cached, such as the query results exceeding the cache block size, the query contains variable functions, etc.

Qcache_queries_in_cache: Number of SQL cached in the current cache

Qcache_total_blocks: Cache Total block count

3: Set Configuration parameters:

SET GLOBAL query_cache_size = 134217728;

4: View Cache Hit count (is a cumulative value):

SHOW STATUS like ' qcache_hits '

Third: Cache data failure time

1:

When the structure or data of a table changes, the data in the query cache is no longer valid. Having these insert, UPDATE, DELETE, TRUNCATE, ALTER table, drop table, or drop database causes the cache data to be invalidated. So the query cache is suitable for applications with a large number of identical queries and is not suitable for applications with large amounts of data updates.

When a table is writing data, the cache of the table (hit check, cache write, and so on) will be in the failed state. In InnoDB, if a transaction modifies a table, the table's cache is invalidated before the transaction commits, and the related query for that table cannot be cached until the transaction commits.

2: Clean up query cache & reduce fragmentation policy

FLUSH Query_cache; Cleanup Query Cache memory fragmentation

RESET Query_cache; Remove all queries from the query cache

FLUSH TABLES; Close all open tables, and the operation will empty the contents of the query cache

1. Select the appropriate block size

2. Use the FLUSH query cache command to defragment the fragment. This command will cause other connections to not use the query cache during the collation of the cache

PS: Empty the cached command RESET QUERY cache

Four: Memory management of the cache

The cache creates a chunk of memory (query_cache_size) in memory to maintain the cached data, where approximately 40K of space is used to maintain the cached metadata (what is metadata: http://www.cnblogs.com/Alight/p/3982086. HTML), such as spatial memory, mapping of data tables and query results, mapping of SQL and query results, and so on.

MySQL divides this large memory block into small chunks of memory (query_cache_min_res_unit), each of which stores its own type, size, and query result data, as well as pointers to front and back memory blocks.

MySQL needs to set the size of a single small storage block, and when the SQL query starts (not getting the results) to request a piece of space, so even if your cache data does not reach this size, you also need to use this size of data block to save (this is the same as the Linux file system block). If the result exceeds the size of this block of memory, then you need to request a memory block. When the query finishes discovering that the requested memory block is redundant, it frees up the excess space, which can cause memory fragmentation problems, see

Here query 1 and the white space between query 2 is memory fragmentation, this part of the free memory is released after the query 1 query, assuming that the space is large and small in the size of the memory block MySQL set, it can no longer be used, causing fragmentation problems

To request allocation of memory at the start of a query the block needs to lock the entire free memory area, so allocating memory blocks is very resource-intensive. Note that the allocated memory is allocated on the block of memory that was opened up when MySQL was initialized.

V: Cache Usage Time & performance

It is a difficult topic to measure whether opening the cache has a performance boost to the system.

1. Cache Hit Rate = Cache Hit count (qcache_hits)/number of queries (Com_select)

2. Through cache write rate, write rate = cache write count (qcache_inserts)/number of queries (Qcache_inserts)

3. By hit-write rate judgment, ratio = hit count (qcache_hits)/write Count (Qcache_inserts), high performance MySQL is called the comparison can reflect performance improvement of the index, generally reach 3:1 is the query cache is valid, and preferably can reach 10:1

Anything is too much, especially for some systems that write frequently, turning on the query cache function may not allow the system to improve, and sometimes it will fall. The reason is that MySQL ensures that the content of the query cache is exactly the same as the actual data, and when a data table is updated, deleted, and inserted, MySQL forces the query cache for all queries that refer to the table to be invalidated. For dense writes, enabling query caching is likely to cause frequent cache failures, indirectly triggering memory spikes and CPU spikes, which is a huge burden on already busy database systems.

Six: Query cache problem Analysis

Seven: InnoDB and query caching

InnoDB sets a transaction counter for each table, which stores the current maximum transaction ID. When a transaction commits, InnoDB uses the transaction ID of the system transaction ID in MVCC with the largest counter of the new current table.

Only transactions that are larger than this maximum ID can use the query cache, and other transactions that are smaller than this ID cannot use the query cache.

Also, in InnoDB, all transactions that have lock operations do not use any query cache

Viii. Other

The Query cache is slightly different due to MySQL's storage engine, such as MyISAM, where the cached result set is stored in the OS cache, and the most popular innodb is placed in buffer pool.

MySQL Query cache detailed