MySQL Query cache detailed

MySQL Query cache

Used to save the full result returned by the MySQL query statement, when hit,

MySQL returns results immediately, eliminating parsing, optimization, and execution phases

Concurrency query volume is very large, the CPU core number of a very long cache is not necessarily valid

(Multiple threads compete for this memory space in which the cache is stored)

How to check the cache???

MySQL saves the results with the cache:

Make a hash calculation of the SELECT statement itself, the result of the calculation as a key, the query result as value

What statements are not cached?

When there are some indeterminate data in the query statement, it is not cached; For example, now (), Current_time ();

In general, 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

Caching brings additional overhead

1, each query will first check whether the hit

2, the query results should be cached (write cache)

View variables associated with the cache

mysql> show global variables like  ' query_cache% '; +------------------------------+- ---------+| variable_name                 | value    |+------------------------------+----------+| query _cache_limit            | 1048576   ||  query_cache_min_res_unit     | 4096     | |  query_cache_size             |  16777216 | |  query_cache_type             | on        | |  query_cache_wlock_invalidate | off      |+---------------------- --------+----------+query_cache_type: Query Cache Type: whether to turn on the cache function, there are three ways to open {on| Off| DemAnd}   demand: means that the SELECT statement explicitly uses the Sql_cache option to cache   (Sql_no_cache will not be cached) Query_cache_size: total space; units in bytes , the size must be an integral multiple of 1024. When MySQL starts, it allocates and immediately initializes a memory space of the size specified here;    This means that if you modify this size, the cache is emptied and reinitialized. query_cache_min_res _unit: The minimum memory block for storing the cache; (query_cache_size - qcache_free_memory)  / Qcache_queries_in_cache  Be able to get an ideal value     settings too large, easy to waste; set too small, prone to large amounts of fragmentation query_cache_limit: Maximum value of a single cached object, not cached when out of bounds; manual use of Sql_no_ The cache can be artificially avoided by attempting to buffer the return out of this parameter qualified statement query_cache_wlock_invalidate: If a table is locked by another user connection, the result is still returned from the cache, and off is the return

How to determine the hit rate:

Divided into the number of times and the byte hit ratio (the amount of data that is omitted after the hit, or the cost of parsing itself)

mysql> show global status like  ' qcache% '; +---------------------------------------- ---------+----------+| variable_name                                     | value    |+------------------------------------ -------------+----------+| qcache_free_blocks (number of free blocks)                    | 1         ||  qcache_free_memory (free space)                    | 16759688 | |  qcache_hits (hit Count)                           | 0        | |  Qcache_inserts  (number of inserts)                         | 2         | |  qcache_lowmem_prunes (Memory is too small, number of pruning memory)  | 0        | |  qcache_not_cached (number not cached)               | 8        | |  qcache_queries_in_cache (number of queries cached in cache)  | 0        | |  qcache_total_blocks (total number of blocks)                     | 1        |+---------------- ---------+----------------------------------+

Defragment MySQL > Flush query_cache

Empty cache mysql > Reset Query_cache

Calculate hit Ratio:

MySQL > Show global status where Vaiable_name= ' qcache_hits ' OR variable_name= ' com_select ';

qcache_hits/(qcache_hits + com_select)

Mysql> show global status where Variable_name= ' qcache_hits ' OR variable_name= ' com_select '; +---------------+------- +| variable_name | Value |+---------------+-------+| Com_select | 25 | Total query At this point, the hit will not accumulate | Qcache_hits | 3 |+---------------+-------+ hit ratio =3/(3+25)

Caching Optimization Ideas

1. Bulk write, not multiple individual writes

2. Cache space should not be too large, because a large number of cached colleagues fail to cause the server to feign death

3. If necessary, manually control the cache using Sql_cache and Sql_no_cache.

4. For write-intensive scenarios, disabling caching instead improves performance.

This article is from the "Homecoming" blog, make sure to keep this source http://sixijie123.blog.51cto.com/11880770/1883864

MySQL Query cache detailed