Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to query cache variables in MySQL. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have some understanding of the relevant knowledge after reading this article.

MySQL query cache variable explanation:

Qcache_free_blocks: the number of adjacent memory blocks in the cache. A large number means there may be fragments. FLUSHQUERYCACHE will defragment the cache to get a free block.

Qcache_free_memory: free memory in the cache.

Qcache_hits: increments every time a query hits in the cache

Qcache_inserts: increments each time a query is inserted. The miss ratio is the number of hits divided by the number of inserts.

Qcache_lowmem_prunes: the number of times the cache runs out of memory and must be cleaned to provide space for more queries. This number is best seen over a long period of time; if it is growing, it means that the fragmentation may be very serious, or there may be very little memory. (the free_blocks and free_memory above can tell you which case it is.)

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

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

Qcache_total_blocks: the number of blocks in the cache.

Query_cache_limit: queries larger than this size will not be cached

Query_cache_min_res_unit: minimum size of the cache block

Query_cache_size: query cache size

Query_cache_type: cache type, which determines what kind of query to cache. In the example, selectsql_no_cache query is not cached.

Query_cache_wlock_invalidate: when another client is writing to the MyISAM table, if the query is in querycache, whether to return the cache result or wait for the write operation to complete and then read the table to get the result.

Query_cache_min_res_unit configuration is a "double-edged sword", the default is 4KB, set a large value for big data query is good, but if your queries are small data queries, it is easy to cause memory fragmentation and waste.

What does MySQL query cache variable mean

Query cache fragmentation rate = Qcache_free_blocks/Qcache_total_blocks*100%

If the query cache fragmentation rate exceeds 20%, you can use FLUSHQUERYCACHE to defragment the cache, 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%, it means that the query_cache_size setting is too large and can be reduced appropriately; if the query cache utilization is above 80% and the Qcache_lowmem_prunes is more than 50, the query_cache_size may be a little small, or there may be too many fragments.

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

Example server query cache fragmentation rate = 20.46%, query cache utilization = 62.26%, query cache hit rate = 1.94%, the hit rate is very poor, maybe write operations are more frequent, and there may be some fragments.

On how to query cache variables in MySQL to share here, I hope the above content can be of some help to you, you can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.