2

What is the best setting for query_cache_min_res_unit for these results:

+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 35327     |
| Qcache_free_memory      | 295242976 |
| Qcache_hits             | 236913188 |
| Qcache_inserts          | 49557287  |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 7128902   |
| Qcache_queries_in_cache | 195659    |
| Qcache_total_blocks     | 426870    |
+-------------------------+-----------+

Do I need to change any other settings?

My website creates very large results. This is the current setting:

query_cache_min_res_unit = 4096

Info on the mysql dev website

If most of your queries have large results (check the Qcache_total_blocks and Qcache_queries_in_cache status variables), you can increase performance by increasing query_cache_min_res_unit. However, be careful to not make it too large (see the previous item).

3
  • 1
    If your database is suffering from some problem, why not change a parameter you think may help and monitor to see if the problems have eased? If so, then try altering it some more and carry on until the performance no longer improves. Then try modifying another parameter. If ever the performance gets worse, try changing the parameter the other way, or restore it to its original value and try another.
    – nurdglaw
    May 24, 2013 at 13:10
  • 1
    Database tuning involves some king of dark magic. There is no "correct" value for any setting (otherwise the MySQL folks would have made the engine tune itself automagically). It is up to you to find the right setting for your specific database.
    – RandomSeed
    May 24, 2013 at 13:15
  • 1
    Try using Domas' query_cache tuner. May 24, 2013 at 17:11

1 Answer 1

2

Observations

  • Your question says you have 426870 for Qcache_total_blocks. Since each block is 1K, blocks are aligned to the nearest multiple of 1024. Thus, your query cache has about 417M.
  • If you divide Qcache_not_cached (7128902) by Qcache_inserts (49557287), this shows that about 14.4% (1 out of 7) of your queries could not be cached.
  • (Qcache_total_blocks (426870) - Qcache_free_blocks (35327)) / Qcache_queries_in_cache (195659) = 2K
  • On average, each query cache result hold about 2K, which is theoretically not possible. This could indicate some fragmentation in the query cache.

Recommendations

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.