MySQL Query cache

MySQL Query Cache

To save the full result returned by the MySQL query statement, when hit, MySQL returns the result immediately, eliminating the parsing, optimization, and execution phases.

How do I check the cache?

MySQL Save the results in the cache:

The SELECT statement itself is hashed, the result of which is key, and the query result as value.

Data that will not be cached:

There are some indeterminate data in the query statement that will not be cached, such as now (), current_time (), generally, if the query contains custom 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 must first check whether hit;

2, check the results should be cached first;

Cache-Related variables:

View cache variables show GLOBAL VARIABLES like ' query_cache% ';

Query_cache_limit: The maximum value of a single cached object, out of the cache, and manually using Sql_no_cache can artificially avoid attempts to cache statements that return results beyond the qualified value of this parameter.

Query_cache_min_res_unit: The minimum memory block for storing the cache; Calculation method: (query_cache_size-qcache_free_memory)/qcache_queries_in_cache

Query_cache_size: Total space, in bytes, size must be 1024 times times the number. When MySQL starts, it allocates and immediately initializes a memory space of the size specified here, which means that if you modify this size, the cache is emptied and reinitialized;

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; Query_cache_wlock_invalidate: If a data table is locked by another user connection, the result is still returned from the cache, and off is returned.

How to determine the hit rate:

SHOW GLOBAL STATUS like ' qcache% ';

Qcache_free_blocks: Number of free blocks

Qcache_free_memory: Free Space

Qcache_hits: Hit Count

Qcache_inserts: Number of times to write cache to cache space

Qcache_lowmem_prunes: Because memory is small, the number of free memory

qcache_not_cached: Number of buffers not being cached

Qcache_queries_in_cache: The number of inserted queries cached in the cache

Qcache_total_blocks: Total number of blocks

Defragmentation: FLUSH Query_cache

Clear cache: RESET Query_cache

Calculate hit Ratio:

SHOW GLOBAL STATUS WHERE variable_name= ' qcache_hits ' OR variable_name= ' com_select ';

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

| variable_name | Value |

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

| Com_select | 10 |

| Qcache_hits | 0 |

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

qcache_hits/(com_select+ qcache_hits)

Also need to refer to the ratio of hit and write, that is, the value of Qcache_hits/qcache_inserts, if the value can be greater than 3:1, it indicates that the cache is also valid, can reach 10:1, for a more ideal situation. If the hit rate is low and the ratio hovers at 1:1, it means that the cache has no eggs or is closed.

Cache Optimization Ideas:

How to make cache hits when using caching, improving the validity of the cache:

1, bulk write rather than multiple individual writes, so that only one impact on the cache

2, too large cache space, may cause a large number of cache failure caused the server to feign death, so cache space should not be too large

3. If necessary, use Sql_no_cache to manually control the cache

4. For write-intensive scenarios, disabling caching can improve performance

MySQL Query cache