Tuning the MySQL Query Cache - Part 2
It's been quite some time since I wrote my original post on optimizing the query cache, which set out a plan for testing improvements. Unfortunately I can't recall the dates of all the changes I made, and the server workload hasn't been consistent, but I think some trends can still be recognized.
The big question I had initially was if whether changing the value of query_cache_min_res_unit could have a significant effect on the query cache's memory utilization, and correspondingly reduce a high prune rate. The default value of 4K was almost twice the average size of a stored result, so I planned to reduce the setting in 1K increments and observer any effects.
The Data
As can be seen in the memory utilization graph, things did improve with a smaller min_res_unit; the larget improvements can be seen in Nov (3K) and February (2K). I believe the last change occured in August (1K), but little effect is noticable.
These changes are mirrored in the block allocations, with more total blocks being allocated as the min_res_unit is reduced. Free blocks increases as well, but to a lesser extent, and mysqlreport indicated fragmentation is largely unchanged (12.75% versus the original 12.40%).
While a slight increase in queries stored occured, and noticable bumps occur when the settings changes were applied, no substantial change is sustanied.
Over the period, little change occured in the rate of hits, inserts, and prunes.
Conclusion
While memory utilization was increased with changes to query_cache_min_res_unit, I don't think it resulted in significant performance improvements. It doesn't appear that any negative effects resulted, so tuning the value to approximate your average query size is a win, but not a great one.
I believe the significant issues remain as fragmentation and limited space; once the query cache fills a minimal amount of memory is freed at each prune, and only small contiguous blocks are available - limiting effective use of available memory. My next step is to set up a cron task to regularly optimize the query cache, ensuring that large contiguous blocks of memory are available. My concern though, is that while again memory utilization will improve, the large volume of prunes will still occur as a small amount of memory is freed at each prune and quickly re-utilized.
Comments
Hi,
I performed the same kind of optimization as you did in your part 1, since I was experimenting the exact same behaviour.
I did see the cache memory get utilized more fully as you did, but I also am seeing higher cache prunes per day (around 60K/day before the change and 80K/day after).
One thing I can see in cacti, that's showing the same in your graphs is that cacti shows Qchache hit rate of nearly 0. The weird thing is that mysqltuner says something like this:
[OK] Query cache efficiency: 94.0% (40M cached / 43M selects)
I'm now wondering what data source I can trust.. one says the cache is not used at all, while the other says it's 94% effective.. :\
I'm not sure you're reading your cacti graph correctly. The values for hits, inserts, and prunes should be in operations per second and is a representation of how busy your server is; compare the values in the cacti graph to the qps value that MySQLTuner gives you for some scale.
The default graph includes queries stored with the hit/insert/prune values, which I think is misleading since they are difference units (operations per second versus query count), and may also have vastly different scales (a few orders of magnitude in my case, but a busier server would likely be less drastic).
The efficiency value in MySQLTuner is how effective your query cache is.
Effectivenes metrics you can glean from the cacti graph would be the ratios of hits to inserts and inserts to prunes - not their absolute values (mysqlreport calculates these ratios).
In summary, your server may only hit the query cache a couple times per second (the cacti Qcache Hits value), but when it does it is usually successful (the MySQLTuner query cache efficiency).
Aha! thanks for the precision. You're right, the graph is very misleading..
So then the query cache is useful most of the time on this server. The number of webapps hosted on it is faily limited, so that would help explain it I guess.
But since the number of cache prunes got higher, doesn't it affect performance of the database? I guess have smaller chunks naturally means that more of them are going to get scraped off if we need some space in the cache.
Post new comment