[Solved] Query Cache utilisation ~50% Qcache_lowmem_prunes still high

Daniel Garcia Asks: Query Cache utilisation ~50% Qcache_lowmem_prunes still high
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?

Ten-tools.com may not be responsible for the answers or solutions given to any question asked by the users. All Answers or responses are user generated answers and we do not have proof of its validity or correctness. Please vote for the answer that helped you in order to help others find out which is the most helpful answer. Questions labeled as solved may be solved or may not be solved depending on the type of question and the date posted for some posts may be scheduled to be deleted periodically. Do not hesitate to share your response here to help other visitors like you. Thank you, Ten-tools.