Shulou(Shulou.com)06/01 Report--

1. Query cache description

MySQL Query Cache is used to cache the SELECT statement we execute and the result set of the statement. MySql is similar to the typical KV storage in the specific technical details of implementing Query Cache, that is, the SELECT statement and the result set of the query statement are mapped by HASH and saved in a certain memory area. When the client initiates a SQL query, the search logic of Query Cache is to first verify the corresponding permissions on the SQL, and then find the results through Query Cache (note that it must be exactly the same, even if one more space or different case is considered different, even if the exact same SQL is used, different character sets, different protocols, etc., will be regarded as different queries and cached separately). It does not need to be analyzed and optimized by the Optimizer module, much less need to interact with any storage engine, reducing a large number of disk IO and CPU operations, so sometimes it is very efficient.

Second, query cache hit

To judge whether a cache is hit or not, according to the hash value of the query statement, the hash value factors include query statement, query database, client version protocol and so on. If any character of the query statement is different, it will lead to different HASH results, which may lead to cache failure. Some queries can not be cached, such as queries containing UDF, storage functions, user-defined variables, temporary tables, system tables in mysql library, or tables containing column-level permissions, functions with uncertain values (Now ()); query cache is completely stored in memory, and the entire memory space allocation is recycled, which will lead to additional system resource consumption, which will lead to memory fragmentation.

3. 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 related variable description

Query_cache_min_res_unit: the minimum allocation unit of memory blocks in the query cache; smaller values reduce waste, but lead to more frequent memory allocation operations; higher values lead to waste and excessive 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 memory space available to the query cache; in bytes, it must be an integer multiple of 1024

Query_cache_type: ON, OFF, DEMAND

A, 0 (OFF): turn off the Query Cache function, and Query Cache will not be used under any circumstances

B, 1 (ON): enable the Query Cache function, but when the SQL_NO_CACHE prompt is used in the SELECT statement, Query Cache will not be used

C, 2 (DEMAND): turn on the Query Cache function, but use Query Cache only if the SQL_CACHE prompt is used in the SELECT statement.

Query_cache_wlock_invalidate: if a table is locked by other joins, whether the result can still be returned from the query cache. The default value is OFF, which means you can continue to return data from the cache when the table is locked by other joins. ON means 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)

View the description of cache variable parameters:

Qcache_free_blocks: the number of free blocks in the cache pool, free blocks of memory.

Qcache_free_memory: free memory space in the cache

Qcache_hits: number of cache hits

Qcache_inserts: the number of times the results of cacheable query statements are cached

Qcache_lowmen_prunes: how many times the LRU algorithm was used to clean the cache because of the lack of memory space in the query cache

Qcache_not_cached: results that can be cached but cannot be cached, such as query results that exceed the size of the cache block, queries that contain variable functions, etc.

Qcache_queries_in_cache: the number of SQL cached in the current cache

Qcache_total_blocks: how many memory blocks are there in the entire query cache

Cache hit ratio: Qcache_hits/ (Qcache_hits+Com_select)

Analyze and configure the query cache process and improve the cache hit rate

1. At first, if the query cache hit ratio is acceptable? If you can, it's over.

two。 Cannot accept the current cache hit rate, check that most queries are not cacheable? If so, most queries cannot be cached. Is the query_cache_limit large enough? If so, it ends, indicating that the query cannot be cached, and it may be user-defined variables, storage functions, and so on. In this case, 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 they are not cached. Is there a lot of serious work going on?

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

③ .2. If the cache is not cached because there are too many fragments, what happens when the memory is too low?

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

④ .2. If the memory is not too low, are there many update statements? The cache caused by frequent table updates can not be hit, there are a lot of frequently updated statements, the load is not suitable for the cache, it is recommended to close the cache. If there are not many statements that are updated frequently, there are other configuration problems.

5. If a lot of verification work hadn't taken place, did the cache start? Yes, I have not seen this query. If not, start the cache.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.