MySQL query cache Variables

The following describes the MySQL cache variable query in detail and analyzes the steps in the MySQL cache variable query process. If you are interested in this, take a look.

 
 

  
  
mysql> show global status like 'qcache%';  
  
  
+-------------------------+-----------+  
  
  
| Variable_name | Value |  
  
  
+-------------------------+-----------+  
  
  
| Qcache_free_blocks | 22756 |  
  
  
| Qcache_free_memory | 76764704 |  
  
  
| Qcache_hits | 213028692 |  
  
  
| Qcache_inserts | 208894227 |  
  
  
| Qcache_lowmem_prunes | 4010916 |  
  
  
| Qcache_not_cached | 13385031 |  
  
  
| Qcache_queries_in_cache | 43560 |  
  
  
| Qcache_total_blocks | 111212 |  
  
  
+-------------------------+-----------+ 
 
 

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: it is not suitable for MySQL to query cache variables. It is usually because these queries are not SELECT statements or functions such as now () are used.
Qcache_queries_in_cache: number of queries (and responses) cached currently.
Qcache_total_blocks: Number of cached blocks.
Let's query the server's query_cache Configuration:

 
 

  
  
mysql> show variables like 'query_cache%';  
  
  
+------------------------------+-----------+  
  
  
| Variable_name | Value |  
  
  
+------------------------------+-----------+  
  
  
| query_cache_limit | 2097152 |  
  
  
| query_cache_min_res_unit | 4096 |  
  
  
| query_cache_size | 203423744 |  
  
  
| query_cache_type | ON |  
  
  
| query_cache_wlock_invalidate | OFF |  
  
  
+------------------------------+-----------+ 
 
 

Description of each field:
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.

How to enable MySQL slow Query

Mysql multi-Table query implementation

Introduction to MySQL CONVERT Functions

How to modify MySQL character set in Liunx

Take you to understand mysql Variables