Sample Header Ad - 728x90

Query Cache utilisation ~50% Qcache_lowmem_prunes still high

2 votes
1 answer
505 views
I ran two tests, each running for about a week # Test 1 # ### Query Cache Settings ### - query_cache_limit: **50K** - query_cache_size: **128M** - query_cache_min_res_unit: **1K** ### Results ### - Qcache_free_blocks: **6,004** - Qcache_free_memory: **93,598,504** - Qcache_hits: **377,204,411** - Qcache_inserts: **13,056,941** - Qcache_lowmem_prunes: **384,610** - Qcache_not_cached: **5,794,907** - Qcache_queries_in_cache: **16,006** - Qcache_total_blocks: **38,724** ### Results (Calculated Values) ### - Utilisation: **40.7%** - Removal Ratio: **2.9%** - Hit Rate: **95.2%** - AVG Query Size: **4,012** - Frag Indicator: **15.5%**

Utilisation = 100-Qcache_free_memory*100/query_cache_size
Removal Ratio = Qcache_lowmem_prunes*100/Qcache_inserts
Hit Rate = Qcache_hits*100/(Qcache_hits+Qcache_inserts+Qcache_not_cached)
AVG Query Size = (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache
Frag Indicator = Qcache_free_blocks*100/Qcache_total_blocks

# Test 2 # ### Query Cache Settings ### same as above except - query_cache_min_res_unit: **2K** ### Results ### - Qcache_free_blocks: **1,287** - Qcache_free_memory: **70,082,320** - Qcache_hits: **223,742,107** - Qcache_inserts: **7,104,530** - Qcache_lowmem_prunes: **186,235** - Qcache_not_cached: **3,218,864** - Qcache_queries_in_cache: **20,802** - Qcache_total_blocks: **43,424** ### Results (Calculated Values) ### - Utilisation: **55.6%** - Removal Ratio: **2.6%** - Hit Rate: **95.6%** - AVG Query Size: **4,217** - Frag Indicator: **3.0%** # now for the question.... # As expected, when I lowered query_cache_min_res_unit, fragmentation went up. What really has me confused is why is the removal ratio still so high even when utilisation is low?
Asked by Daniel Garcia (25 rep)
Oct 28, 2017, 08:29 PM
Last activity: Dec 25, 2024, 08:01 PM