MySQL Query cache area

To improve the query speed, MySQL maintains a memory area (official documentation indicates that the size is at least 41984B) to cache the query results, and when the query finds data in the cache, it returns the result without executing the SQL statement.

Criteria for query Hits

Each cache query requires at least two blocks (one block for querying text and one or more blocks for query results). Also, each query used by each table requires a block. However, if two or more queries use the same table, only one block needs to be allocated.

For two query statements whether the same decision, MySQL is relatively strict, the query must be exactly the same (byte-wise) to be considered to be the same. In addition, the same query string may be considered different for other reasons. Queries that use different databases, different protocol versions, or different default character sets are considered to be different queries and are cached separately. For example, the following two queries are not the same:

Tbl_name

Tbl_name

Buffer configuration

In MySQL, the set buffer size command is:

MySQL>set GLOBAL query_cache_size = 41984;

If the set query_cache_size is less than 41984, the setting fails and it is automatically set to 0. A setting of 0 means that no buffers are used.

When Query_cache_size is greater than 0, there is no guarantee that the buffer will be used, and it must be determined according to the variable query_cache_type , which has 3 states, representing the different buffering modes in 3, respectively:

0 or off--------will block caching or query cache results.

1 or on---------will allow caching, except for query statements starting with select Sql_no_cache.

2 or demand--------enable caching only for those query statements that start with select Sql_cache.

Here's how it's set up:

mysql> SET SESSION query_cache_type = OFF;

　　

Memory size allocation for query results　　

To control the maximum value of a specific query result that can be cached, you should set the query_cache_limit variable. The default value is 1MB.

When a query result (data returned to the client) is fetched from the query buffer, it is sorted in the query cache. Therefore, data is usually not processed in large chunks. The query cache allocates data blocks based on data ordering, so when a block of data is used up, a new chunk of data is allocated. Because memory allocation operations are expensive (time-consuming), the query cache is assigned the minimum value by query_cache_min_res_unit system variables. When the query executes, the most recent result data block is determined based on the actual data size, so that unused memory can be freed. Depending on the type of query that your server executes, you will find it useful to adjust the value of the Query_cache_min_res_unit variable:

• The default value of Query_cache_min_res_unit is 4KB. This should be suitable for most situations.
• If you have a large number of queries that return small result data, the default chunk size may cause memory fragmentation and display as a large chunk of free memory. Memory fragmentation forces the query cache to trim (delete) queries from cache memory due to lack of memory. At this point, you should reduce the value of the Query_cache_min_res_unit variable. The number of free blocks and queries moved out due to trimming is given by the values of the qcache_free_blocks and qcache_lowmem_prunes variables.
• If a large number of queries return large results (check qcache_total_blocks and Qcache_queries_in_cache state variables), you can improve performance by increasing the value of the Query_cache_min_res_unit variable. However, be careful not to make it too big.

That is, when the query results are allocated buffer size, not wait for the query results to be returned after the allocation of space according to the result size, but instead of allocating a memory according to Query_cache_min_res_unit, the results are returned to the memory directly. There are 3 scenarios to consider:

1. if (the size of the query result is =query_cache_min_res_unit), the result is directly stored in the buffer.
2. if (the size of the query result is <query_cache_min_res_unit), the result is stored directly in the buffer, and then the allocated space is freed, this situation is prone to memory fragmentation.
3. if (the size of the query results >query_cache_min_res_unit), you need to allocate memory space to hold the results

Querying for high-speed buffering status and maintenance

You can use the following statement to check whether the MySQL server provides query caching functionality:

Mysql> SHOW VARIABLES like ' Have_query_cache ';

+------------------+-------+

| variable_name | Value |

+------------------+-------+

| Have_query_cache | YES |

+------------------+-------+

You can use the Flush query cache statement to clean up query buffer fragmentation to improve memory usage performance. The statement does not remove any queries from the cache.

The RESET query cache statement removes all queries from the query buffer. The FLUSH Tables statement also performs the same work.

To monitor query cache performance, use Show status to view cache state variables:

SHOW STATUS like ' qcache% ';

+-------------------------+--------+

| variable name | value |

+-------------------------+--------+

| Qcache_free_blocks | 36 |

| Qcache_free_memory | 138488 |

| Qcache_hits | 79570 |

| Qcache_inserts | 27087 |

| Qcache_lowmem_prunes | 3114 |

| qcache_not_cached | 22989 |

| Qcache_queries_in_cache | 415 |

| Qcache_total_blocks | 912 |

+-------------------------+--------+


Clear Buffer free space
　　Query caching uses variable-length blocks, so qcache_total_blocks and qcache_free_blocks can display query cache memory fragmentation. After FLUSH QUERY CACHE is executed, only one free block is reserved.

mysql>FLUSH QUERY CACHE;


Memory substitution Policy

The query buffer uses a least-recently-used (LRU) policy to determine which queries are moved out of the buffer (the information provided based on the qcache_lowmem_prunes state variable can help you resize the query cache.) It calculates the number of queries that are removed from the query buffer to free memory in order to cache new queries )

MySQL Query cache area