1

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?

  • The metrics for the QC are mysterious. – Rick James Oct 30 '17 at 21:53
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.