MySQL Query cache open, settings, parameter queries, performance variables

Query_cache_type How to use query caching

In general, we will set the Query_cache_type to on, which should be on by default

MySQL>SELECT@ @query_cache_type; + -- ------------------+ | @ @query_cache_type | + -- ------------------+ |  on                 | + -- ------------------+

Query_cache_type has 3 values of 0 for closing the query cache off,1 on behalf of the on,2 (DEMAND) represents the cache when there are sql_cache keywords in the SQL statement, such as:

Select user_name  from where user_id = '  - ';

So when we execute select Id,name from TableName; The query cache is used.

1) in case of Query_cache_type open, if you do not want to use the cache, you need to specify

SELECT  from TableName;

2) When a MySQL function is used in SQL, it is not cached

Of course, you can also disable query caching:

MySQL>set session query_cache_type=off;

System variable Have_query_cache Set whether the query cache is available

 mysql>  show variables like   '  

The above display indicates that the set query cache is available.

System Variable Query_cache_size

Represents the query cache size, which is the allocation of memory size to the query cache, if you assign a size of 0, then the first step and the second step does not work, or no effect.

 mysql>  select  @ @global  .query_cache_size;  +  -- -------------------------+  |  @ @global . query_cache_size |  +  -- -------------------------+                   |  16777216  |  +  -- -------------------------+  

Above is mysql6.0 set default, the previous version as if the default is 0, then it is necessary to set the next.

-- set Global.query_cache_size here is set around 1M, more than 900 K.  >set@ @global. query_cache_size=1000000;

Query_cache_limit control the maximum value of cached query results

For example: If the query result is very large, also cache???? This is obviously out of the question.

MYSQL can set a maximum cache value, and when you query the cache number result data exceeds this value will not be cached. The default is 1M, which means that more than 1M query results are not cached.

 mysql>  select  @ @global  .query_cache_limit;  +  -- --------------------------+  |  @ @global . Query_cache_limit |  +  -- --------------------------+                     |  1048576  |  +  -- --------------------------+  

This is the default value, if you need to modify it, just like setting the cache size, use set to re-specify the size.

MySQL Query cache related variables

Mysql>Show variables like '%query_cache%';+------------------------------+---------+|Variable_name|Value|+------------------------------+---------+|Have_query_cache|YES||Query_cache_limit| 1048576 ||Query_cache_min_res_unit| 4096    ||Query_cache_size| 0       ||Query_cache_type|  on      ||Query_cache_wlock_invalidate| OFF     |+------------------------------+---------+

To view the status of a cache

Mysql>Show status like '%qcache%';+-------------------------+-------+|Variable_name|Value|+-------------------------+-------+|Qcache_free_blocks| 0     ||Qcache_free_memory| 0     ||Qcache_hits| 0     ||Qcache_inserts| 0     ||Qcache_lowmem_prunes| 0     ||Qcache_not_cached| 0     ||Qcache_queries_in_cache| 0     ||Qcache_total_blocks| 0     |+-------------------------+-------+

MySQL provides a series of Global status to record the current state of the Query Cache, as follows:

qcache_free_blocks: Number of memory blocks in Query Cache that are currently idle

qcache_free_memory: Total amount of Query Cache memory currently in idle state

qcache_hits: Query Cache Hit Count

qcache_inserts: The number of times to insert a new query cache into the query cache, that is, the number of missed hits

qcache_lowmem_prunes: When query cache memory capacity is insufficient, you need to remove the old query cache from the number of times it will be used for the new cache object

qcache_not_cached: The number of SQL not being cache, including SQL that cannot be cache, and SQL that will not be cache because of Query_cache_type settings

Qcache_queries_in_cache: Number of SQL currently in Query cache

qcache_total_blocks: Total Block count in Query Cache

Check Query Cache usage

The simplest way to check whether to benefit from the query cache is to check the cache hit rate, and when the server receives a SELECT statement, the qcache_hits and Com_select variables are incremented based on the query cache.

The calculation formula for query cache hit ratio is: qcache_hits/(qcache_hits + com_select).

Mysql>Show status like 'com_select%';+---------------+-------+|Variable_name|Value|+---------------+-------+|Com_select| 3     |+---------------+-------+

Query_cache_min_res_unit configuration is a "double-edged sword", the default is 4KB, set the value of large data query is good, but if your query is small data query, it is easy to create memory fragmentation and waste.

Query Cache Fragmentation Rate = Qcache_free_blocks/qcache_total_blocks * 100%

If the query cache fragmentation rate exceeds 20%, you can use FLUSH query cache to defragment the cache, or try to reduce query_cache_min_res_unit if your queries are small data volumes.

Query Cache utilization = (query_cache_size-qcache_free_memory)/query_cache_size * 100%

Query cache utilization below 25% indicates that the query_cache_size setting is too large to be appropriately reduced; query cache utilization is above 80% and Qcache_lowmem_prunes > 50 query_cache_ Size may be a little bit small, or too much fragmentation.

Query Cache Hit Ratio = (qcache_hits-qcache_inserts)/qcache_hits * 100%

Sample server query Cache Fragmentation rate = 20.46%, query cache utilization = 62.26%, query cache Hit ratio = 1.94%, bad hit, probably write more frequently, and possibly some fragments

Quote a word from a predecessor

If the Qcache_lowmem_prunes value is larger, it indicates that the query buffer size setting is too small and needs to be increased.
If there are more qcache_free_blocks, it means that there is more memory fragmentation, need to clean up, flush query cache
For the tuning of query_cache_min_res_unit size, a calculation formula is given, which can be used for tuning setting reference:
Query_cache_min_res_unit = (query_cache_size-qcache_free_memory)/Qcache_queries_in_cache

Reference:

Http://blog.sina.com.cn/s/blog_75ad10100101by7j.html

MySQL Query cache open, settings, parameter queries, performance variables