MySQL Query cache open, set, parameter query, performance variable meaning

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

Http://www.cnblogs.com/zemliu/archive/2013/08/03/3234372.html

First: 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 on,2 (DEMAND) represents the cache when there are sql_cache keywords in the SQL statement, such as: Select sql_cache user_ Name from users where user_id = ' 100 ';
So when we execute select Id,name from TableName; This will use the query cache.

① in case of Query_cache_type open, if you do not want to use the cache, you need to specify
Select Sql_no_cache id,name from TableName;

② when MySQL functions are used in SQL, they are not cached

Of course, you can also disable query caching: mysql> set session Query_cache_type=off;

Second: System variable Have_query_cache set whether the query cache is available

Mysql> Show variables like ' Have_query_cache ';

+------------------+-------+
| variable_name | Value |
+------------------+-------+
| Have_query_cache | YES |
+------------------+-------+
The above display indicates that the set query cache is available.

Third: System variable Query_cache_size

Represents the query cache size, which is the allocation of memory size to the query cache, if you allocate a size of 0,

Then the first and second steps do not work, or there is 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.

Sets the SET @ @global. query_cache_size=1000000; Here is the setting of about 1M, more than 900 K.

Check again under SELECT @ @global. query_cache_size;

+---------------------------+
| @ @global. query_cache_size |
+---------------------------+
| 999424 |
+---------------------------+
Shows that we set the new size to indicate that the setting was successful.

IV: 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, when you query the cache number result data exceeds this value will not
To cache. 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, using Set
Re-specify the size.

OK, in 4 steps you can open the query cache, the size of the specific value and the way the query is different
To specify the situation.

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 | 16777216 |
| Query_cache_type | On |
| Query_cache_wlock_invalidate | OFF |
+------------------------------+----------+
6 rows in Set (0.00 sec)

V: View the status of the cache

Mysql> Show status like '%qcache% ';
+-------------------------+----------+
| variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 11 |
| Qcache_free_memory | 16610552 |
| Qcache_hits | 10 |
| Qcache_inserts | 155 |
| Qcache_lowmem_prunes | 0 |
| qcache_not_cached | 21 |
| Qcache_queries_in_cache | 111 |
| Qcache_total_blocks | 256 |
+-------------------------+----------+
8 rows in Set (0.00 sec)

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

The total number of blocks in the Qcache_total_blocks:query Cache

Sixth: Check Query cache usage

The simplest way to check whether to benefit from the query cache is to check the cache hit ratio

When the server receives a SELECT statement, the qcache_hits and com_select variables are cached according to the query

The situation is incremented

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 | 1 |

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

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 reducing 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 is below 25% description Query_ Cache_size settings are too large, can be appropriately reduced; query cache utilization is above 80% and Qcache_lowmem_prunes > 50 say query_cache_size may be a little small, or too many fragments.

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 life The medium rate = 1.94%, the hit ratio is very poor, may write the operation more frequently, and possibly some fragments.

references a previous word

Optimization tip:
If the Qcache_lowmem_prunes value is large, the query buffer size setting is too small to increase.
If more qcache_free_blocks means more memory fragmentation, need to clean up, flush query cache
as described in the "High performance MySQL" I see, about Query_cache_min_ Res_unit size Tuning
, the book gives a calculation formula that can be used for tuning Settings reference:
Query_cache_min_res_unit = (query_cache_size-qcache_free_memory)/ Qcache_queries_in_cache

MySQL Query cache open, set, parameter query, performance variable meaning