MySQL Query cache

First, the query cache description

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 a single space or case is different, even the exact same SQL, If you use different character sets, different protocols, and so on, they are also considered 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.

Second, query cache hit

Determine whether a cache is hit, by the hash value of the query statement to determine that the hash value factors including query statements, query database, client version of the protocol, such as query statements of any character, will result in different hash results, can cause the cache can not hit. Some queries cannot be cached, such as a query containing UDFs, stored functions, user-defined variables, temporary tables, system tables in the MySQL library, or tables with column-level permissions, functions with indeterminate values (now ()); The query cache is completely stored in memory, and the entire memory space is allocated for recycling, which can result in additional system resource consumption, which can lead to the generation of memory fragmentation.

Third, view the parameters of the query cache

mariadb [(none)]> show global variables like  '%query% '; +--------------------- ---------+--------------------+| variable_name                 | Value               |+------------------------------+--------------------+| expensive_subquery_ limit     | 100                 | |  ft_query_expansion_limit     | 20                  | |  have_query_cache             | yes                 | |  long_query_time              | 10.000000           | |  query_alloc_block_size       | 16384               | |  query_cache_limit            | 1048576             | |  query_cache_min_res_unit     | 4096                | |  query_cache_size             |  16777216           | |  query_cache_strip_comments   | OFF                 | |  query_cache_type             | on                  | |  query_cache_wlock_invalidate | OFF                 | |  query_prealloc_size          | 24576               | |  slow_query_log                | off                | |  slow_query_log_file          | localhost-slow.log  |+------------------------------+--------------------+14 rows in set  (0.01 sec)

Query Cache Correlation Variable Description

query_cache_min_res_unit: The smallest allocation unit of memory blocks in the query cache; Smaller values reduce waste, but lead to more frequent memory allocation operations; a larger value can lead to waste and can cause too much fragmentation;

query_cache_limit: The maximum query result that can be cached; For query statements with larger results, it is recommended to use Sql_no_cache in select

query_cache_size: The total available memory space for the query cache; The unit is byte and must be an integer multiple of 1024;

query_cache_type: On, OFF, DEMAND

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.

query_cache_wlock_invalidate: If a table is locked by another connection, the result can still be returned from the query cache, and the default value is off, which means that the table can continue to return data from the cache in a scene locked by another connection ; On indicates that it is not allowed;

mariadb [(none)]> show global status like  ' qcache% '; +------------------------ -+----------+| variable_name           |  value    |+-------------------------+----------+| qcache_free_blocks       | 1        | |  qcache_free_memory      | 16759656 | |  Qcache_hits             | 0         | |  Qcache_inserts          | 0         | |  qcache_lowmem_prunes    | 0        | |  Qcache_not_cached       | 7         | |  qcache_queries_in_cache | 0        | |  Qcache_total_blocks     | 1         |+-------------------------+----------+8 rows in set  (0.00 sec)

To view the cache variable parameter description:

qcache_free_blocks: Number of free blocks in the cache pool, free blocks of memory.

qcache_free_memory: Free memory space in cache

qcache_hits: Cache Hit count

qcache_inserts: The number of times the result of a cacheable query statement is put into the cache

qcache_lowmen_prunes: How many times is it because the query cache memory space is too small to use the LRU algorithm to clean up the cache

qcache_not_cached: Results that can be cached but not cached, such as the query result 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: How many blocks of memory are in the entire query cache

Cache Hit Ratio: qcache_hits/(Qcache_hits+com_select)

Iv. analyzing and configuring the query caching process and increasing cache hit ratios

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M00/92/D0/wKioL1kDLhaTK32vAAHf_JDS6DA042.png "title=" Q1.png "alt=" Wkiol1kdlhatk32vaahf_jds6da042.png "/>

①. Start, if the query cache hit rate is acceptable? If you can, it's over.

②. Cannot accept the current cache hit rate, see most queries are not cacheable? Yes, that is, most of the queries can not be cached, query_cache_limit big enough? If so, it concludes that the query cannot be cached, that it may be a user-defined variable, a stored function, and so on, and it is recommended to turn off the query cache. If it is not large enough, you need to increase this value.

③.1. Most query caches can be cached, but not cached. Is there a lot of serious work going on?

Yes, check to see if the cache is fragmented. You need to lower the value of query_cache_min_res_unit or whether to flush the query cache command to defragment the cache to reduce fragmentation.

③.2. What if the cache is not cached because too many fragments are causing the memory to be too low for the remediation work?

④.1. If the memory is too low, increase the query_cache_size.

④.2. Is there a lot of update statements if it's not memory too low? Frequent update tables cause the cache to not be hit, there are many frequently updated statements, the payload is not suitable for caching, and it is recommended to close the cache. There are no more frequently updated statements, there are other configuration issues.

⑤. If not a lot of verification work has occurred, the cache started? Yes, I have not seen this query. No, start the cache.

This article is from the "take a deep Breath again" blog, make sure to keep this source http://ckl893.blog.51cto.com/8827818/1920613

MySQL Query cache