Master MySQL Query Cache: Metrics, Commands, and Optimization Tips

Query Cache is a crucial metric for MySQL performance tuning; by examining its status you can detect fragmentation, cache hits, and missed queries.

mysql>show global status like 'qcache%';

Qcache_free_blocks

Number of adjacent memory blocks in the cache; a large count may indicate fragmentation.

Qcache_free_memory

Amount of free memory available in the cache.

Qcache_hits

Number of times a query was served from the cache, showing the basic effectiveness of the Query Cache.

Qcache_inserts

Number of queries that missed the cache and were inserted; together with Qcache_hits you can compute the hit rate.

Query Cache hit rate =

Qcache_hits / ( Qcache_hits + Qcache_inserts )

Qcache_lowmem_prunes

Number of queries removed from the cache due to insufficient memory.

Combining Qcache_lowmem_prunes with Qcache_free_memory helps determine whether the cache size is adequate or if memory pressure frequently evicts queries.

Qcache_not_cached

Number of queries that are not suitable for caching, such as those using functions like now().

Qcache_queries_in_cache

Current number of queries stored in the Query Cache.

Qcache_total_blocks

Current number of memory blocks used by the Query Cache.