Explanation for querying cache variables in MySQL

For the database system, query is the most important function. The following describes how to query cache variables in MySQL. If you are interested in querying cache variables in MySQL, take a look.

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.

Implementation of MySQL non-repeated Query

Introduction to transcoding in mysql command line cmd Environment

Mysql command line parameters

Troubleshooting of MySQL command line garbled

How to import SQL data through Mysql command lines