MySQL Query cache

MySQL Query cache

Yun Zhengjie

Copyright Notice: Original works, declined reprint! Otherwise, the legal liability will be investigated.

I. What is MySQL query cacheto save the full result returned by the MySQL query statement, when hit, MySQL returns the result immediately, eliminating parsing, optimization, and execution. The SELECT statement itself is hashed, the result of the calculation as a key, the query result as value, if the query is cached hit, the value is returned directly. when there are some indeterminate data in the query statement, it will not be cached; For example, now (), current_time (); Generally, if a query contains user-defined functions, stored functions, user variables, temporary tables, system tables in the MySQL library, or any tables that contain permissions, it is generally not cached.   Two. Caching brings additional overhead1> Each query will first check whether it is hit;2> Query results should be cached first;When the server has more than one CPU, and the concurrency is very large, this time we have to consider each CPU to make the cache memory space problem, that is, when this situation occurs, we need to measure the efficiency of the cache. Of course, you also have to combine the above two points to judge:  three. Querying cache-related variables

1Mysql> Show global variables like'query_cache%';2+------------------------------+---------+3| variable_name | Value |4+------------------------------+---------+5| Query_cache_limit |1048576|6| Query_cache_min_res_unit |4096|7| Query_cache_size |0|8| Query_cache_type | On |9| Query_cache_wlock_invalidate | OFF |Ten+------------------------------+---------+ One 5RowsinchSet (0.00sec) A  -Mysql> -  the 1>. Query_cache_type: -Query cache type, whether to turn on the cache function, there are three ways to open {on| Off|DEMAND}, where DEMAND means that the SELECT statement explicitly uses the Sql_cache option to cache yo.  - 2>. Query_cache_size: - The total space used by the cache. The unit is a byte, and the size must be an integer multiple of 1024. When MySQL starts, it allocates and immediately initializes a memory space of the size specified here, which means that + If this size is modified, all caches are emptied and reinitialized. Therefore, it is not recommended to modify this size, preferably in the first design, this memory should not be too large.  - 3>. Query_cache_min_res_unit: +Minimum memory block for storing cache (query_cache_size-qcache_free_memory)/Qcache_queries_in_cache is able to get an ideal value A 4>. Query_cache_limit: at the maximum value of a single cached object is not cached when it is exceeded, and manual use of Sql_no_cache can be artificially avoided by attempting to cache an attempt to return out of this parameter-qualified statement - 5>. Query_cache_wlock_invalidate: -If a table is locked by another user connection, the result is still returned from the cache, and off indicates return.

four. How to determine the hit ratethe hit rate of the MySQL query cache is divided into times and byte hit ratios. The state variable is the result of the MySQL statistics query, the server variable is the value that we can set in advance, can define its working property in advance, the MySQL state variable is the value of statistic data that is constant at work (such as the frequency of cache hit and so on). We can query by the following command:

1Mysql> Show global Status like'qcache%';2+-------------------------+-------+3| variable_name | Value |4+-------------------------+-------+5| Qcache_free_blocks |0|6| Qcache_free_memory |0|7| Qcache_hits |6|8| Qcache_inserts |0|9| Qcache_lowmem_prunes |0|Ten| qcache_not_cached |0| One| Qcache_queries_in_cache |0| A| Qcache_total_blocks |0| -+-------------------------+-------+ - 8RowsinchSet (0.01sec) the  -Mysql> - 1>. Qcache_free_blocks #指的是空闲块数 refers to the amount of space that the system has not used in the total cache size space allocated to the MySQL process.  - 2>. Qcache_hits #指的是命中次数 + 3>. Qcache_inserts #向缓存空间中插入的缓存的次数 - 4>. Qcache_free_memory #空闲空间 refers to the memory space that has not been allocated by the operating system.  + 5>. Qcache_lowmem_prunes #内存太小, number of pruning memory A 6>. qcache_not_cached #没被缓存的个数 at 7>. Qcache_queries_in_cache #缓存中缓存的查询个数 - 8>. Qcache_total_blocks #总块数 refers to the total cache size space allocated by the system to the MySQL process - 9>. Additional knowledge: -A>. Flush Query_cache #碎片整理 -B>.reset Query_cache #清空缓存

six. Calculate hit ratio

1Mysql> show global status where Variable_name='qcache_hits'OR variable_name='Com_select';2+---------------+-------+3| variable_name | Value |4+---------------+-------+5| Com_select |158|6| Qcache_hits |4|7+---------------+-------+8 2RowsinchSet (0.00sec)9 TenMysql> One  A 1>The total number of com_select has been queried, and we are here 158 times; - 2>The cache hit a qcache_hits number of times, and here we have hit 4 hits; -So we can count the number of hits: qcache_hits/(Qcache_hits+com_select), of course, this is just the number of hits, we want to query the method of byte hit rate is very difficult. Therefore, you can also refer to another indicator, hit and write ratio, that is, the value of qcache_hits/qcache_inserts, if the ratio can be greater than 3:1It indicates that the cache is also valid and can reach 10:1, for a more ideal situation. If this parameter cannot be reached, it is recommended that the cache be closed.

Seven. Caching Optimization Ideas1>. Bulk write, not multiple individual writes2>: Cache space should not be too large, because a large number of cached colleagues fail to cause the server to feign death3> If necessary, use Sql_cache and Sql_no_cache to manually control the cache. 4> For write-intensive scenarios, disabling caching instead improves performance.

MySQL Query cache