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 :
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.