Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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

share|improve this question
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 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 at 13:15
 
Try using Domas' query_cache tuner. –  Shlomi Noach May 24 at 17:11

migrated from stackoverflow.com May 24 at 13:43

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 0 down vote

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.