MySQL Cache parameters

The optimization of MySQL is not in line, has done several optimizations, but is not very ideal, or waste resources too much. I have always found that my MySQL cache hit rate is very poor, in good times to reach the 60-70%, but run a long time, only 10-20%. Checked some data about the cached parameter records

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

Have_query_cache
Whether to support query buffer "yes" table is to support query buffer
Query_cache_limit the maximum value of the cached select query result 1048576 byte/1024 = 1024kB = maximum cacheable Select query result must be less than 1024KB
Query_cache_min_res_unit the size of the memory allocated to query cache results by default is 4096 byte or 4kB

After my test,

Set GLOBAL query_cache_min_res_unit=4096; , the debris will be more, in more than 3,000.

Set GLOBAL query_cache_min_res_unit=2046, when the debris is less, in more than 1000.

But too small will increase IO burden

Mysql>show status;

There's a qcache_ in the middle.

Qcache_free_blocks | 4984 |
| Qcache_free_memory | 30097400 |
| Qcache_hits | 701669 |
| Qcache_inserts | 832414 |
| Qcache_lowmem_prunes | 41224 |
| qcache_not_cached | 2654 |
| Qcache_queries_in_cache | 20527 |
| Qcache_total_blocks | 46362

Qcache_hits/qcache_inserts is the cache hit ratio.

But I always question why to 1 minus this number, qcache_hits is the number of orders, then Qcache_inserts is the total, hit number divided by total, not the hit rate, and here to check.

Qcache_free_memory indicates how much memory is available in the query cache now
Qcache_hits represents the number of hits in the query buffer, that is, the number of queries that respond directly from the query buffer
Qcache_inserts indicates that the query cache has always had the result of how many query commands were cached before
Qcache_lowmem_prunes indicates the number of query results that have been overrun and deleted from the query buffer full
Qcache_not_cached indicates the number of query commands that do not enter the query buffer
Qcache_queries_in_cache Query Cache The results of how many query commands are currently cached

This part is different from what I found later.

MySQL Query cache variable Explanation:

Qcache_free_blocks: The number of contiguous memory blocks in the cache. A large number indicates that there may be fragments. FLUSH QUERY Cache will defragment the cache to get a free block.

Qcache_free_memory: Free memory in cache.

Qcache_hits: Increases every time a query hits in the cache

Qcache_inserts: Increases each time you insert a query. The hit count divided by the number of inserts is an out of proportion.

Qcache_lowmem_prunes: The number of times the cache appears to be out of memory and must be cleaned up to provide space for more queries. This number is best for long periods of time; If the number is growing, it means that it may be very fragmented or that there is little memory. (The Free_blocks and free_memory above can tell you what kind of situation it belongs to)

Qcache_not_cached: The number of queries that are not suitable for caching, usually because these queries are not SELECT statements or functions such as now ().

Qcache_queries_in_cache: The number of queries (and responses) that are currently cached.

Qcache_total_blocks: The number of blocks in the cache.

Query_cache_limit: Queries exceeding this size will not cache

Query_cache_min_res_unit: Minimum size of cache block

Query_cache_size: Query Cache Size

Query_cache_type: Cache type, determining what kind of query to cache, the example indicates that the Select Sql_no_cache query is not cached

Query_cache_wlock_invalidate: When there are other clients are writing to the MyISAM table, if the query cache, whether to return the cache results or wait for the write operation completed reread table to obtain results.

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 cause 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 amounts of data.

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

Query cache utilization below 25% indicates that query_cache_size settings are too large to be appropriately reduced; query cache utilization is above 80% and Qcache_lowmem_prunes > 50 query_cache_ Size may be a 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%, hit ratio is very poor, may write more frequently, and may be some fragments.

Quote a senior.

Tuning tips:
If the Qcache_lowmem_prunes value is large, the query buffer size setting is too small and needs to be increased.
If Qcache_free_blocks more, indicating more memory fragments, need to clean up, flush query cache
According to my view of "high performance MySQL", about query_cache_min_res_unit size tuning
, the book gives a calculation formula that can be used to set up a reference for tuning:
Query_cache_min_res_unit = (query_cache_size-qcache_free_memory)/Qcache_queries_in_cache