Query Cache utilisation ~50% Qcache_lowmem_prunes still high

Posted on

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.

Leave a Reply

Your email address will not be published.