Mysql cache Parameters

The optimization of mysql is not feasible. It has been optimized several times, but it is not ideal. It is still a waste of resources. I have always found that the cache hit rate of my mysql is very poor. When the condition is good, it reaches 60-70%, but the running time is long, only 10-20%. I checked some information about some 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 the "YES" table in the cache can be queried.
Query_cache_limit: the maximum value of the Select query results that can be cached is 1048576 Bytes/1024 = kb. That is, the maximum value of the select query results that can be cached must be less than kb.
Query_cache_min_res_unit the memory size allocated to the query cache results each time is 4096 bytes by default, that is, 4 kb.

After my tests,

When set GLOBAL query_cache_min_res_unit = 4096;, the number of fragments is larger than 3000.

When set GLOBAL query_cache_min_res_unit = 2046;, the number of fragments is relatively small, and the number of fragments is more than 1000.

However, when it is too small, it will increase the IO burden.

Mysql> show status;

There is a section starting with Qcache _.

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

1-(Qcache_hits/Qcache_inserts) indicates the cache hit rate.

But I always question why we need to subtract this number from 1. Qcache_hits is the number of commands, so Qcache_inserts is the total number, and the number of hits is divided by the total number, which is not the hit rate. Please check it out here.

Qcache_free_memory indicates how much memory is available in the cache.
Qcache_hits indicates the number of hits in the query cache, that is, the number of queries that respond directly from the query cache.
Qcache_inserts indicates the result of querying the total number of query commands cached in the cache area.
Qcache_lowmem_prunes indicates the number of query results that overflow and deletion from the cache when the query is full.
Qcache_not_cached indicates the number of query commands that do not enter the query cache.
Qcache_queries_in_cache queries the results of the number of query commands currently cached in the cache.

This part is different from what I found later.

Explanation of cache variable query in MySQL:

Qcache_free_blocks: Number of adjacent memory blocks in the cache. A large number of fragments may exist. Flush query cache sorts the fragments in the CACHE to obtain an idle block.

Qcache_free_memory: idle memory in the cache.

Qcache_hits: increases when a query hits the cache.

Qcache_inserts: It increases every time a query is inserted. By dividing the number of hits by the number of inserts, This is the ratio of no hits.

Qcache_lowmem_prunes: the cache has insufficient memory and must be cleaned up to provide more space for queries. It would be better to look at this number for a long time; if this number continues to grow, it may indicate that the fragmentation is very serious, or the memory is very small. (The free_blocks and free_memory above can tell you what the situation is)

Qcache_not_cached: the number of queries that are not suitable for caching. It is generally because these queries are not SELECT statements or use functions such as now.

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

Qcache_total_blocks: Number of cached blocks.

Query_cache_limit: queries exceeding this size will not be cached.

Query_cache_min_res_unit: Minimum cache block size

Query_cache_size: query the cache size

Query_cache_type: cache type, which determines the type of queries to be cached. In this example, select SQL _no_cache query is not cached.

Query_cache_wlock_invalidate: when another client is performing a write operation on the MyISAM table, if the query is in the query cache, whether to return the cache result or wait until the write operation is complete and then read the table to obtain the result.

The configuration of query_cache_min_res_unit is a double-edged sword. The default value is 4 kb. Setting a large value is good for big data queries. However, if all your queries are small data queries, this can easily 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 sort out the CACHE fragmentation, or try to reduce query_cache_min_res_unit, if your QUERY is a small amount of data.

Query cache utilization = (query_cache_size-Qcache_free_memory)/query_cache_size * 100%

If the query cache utilization is below 25%, the query_cache_size setting is too large and can be appropriately reduced. If the query cache utilization is above 80% and Qcache_lowmem_prunes> 50, the query_cache_size may be small, or too many fragments.

Query cache hit rate = (Qcache_hits-Qcache_inserts)/Qcache_hits * 100%

The cache fragmentation rate of the sample server is 20.46%, the query cache utilization is 62.26%, the query cache hit rate is 1.94%, And the hit rate is very low. It is possible that write operations are frequent and there may be some fragments.

Reference a story from a predecessor

Optimization tips:
If the Qcache_lowmem_prunes value is large, the size of the query cache is too small and needs to be increased.
If Qcache_free_blocks is large, it indicates that there are many memory fragments and need to be cleared. flush query cache
According to my High Performance MySQL, The query_cache_min_res_unit Size Optimization
The book provides a calculation formula, which can be used as a reference for tuning settings:
Query_cache_min_res_unit = (query_cache_size-Qcache_free_memory)/Qcache_queries_in_cache