Query Cache utilisation ~50% Qcache_lowmem_prunes still high

Question

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?

Answer 1

To use least amount of QC space, use

query_cache_min_res_unit=512 

for less waste of QC RAM and qcache_lowmem_prunes expected to decrease as well over time.