MySQL Query Cache not necessarily a bad thing

Category:

Tutorials > Database Management Systems > MySQL > Tips and Tricks

After reading about the query cache mutex contention, scalability issues on multi-cores, and people recommending to disable query cache on multi-core machines. I decided to give it a try and disabled it hoping to improve performance and since I was also seeing a lot of "freeing items" states for my queries (mysql works on query cache for part of the time query is in that state). FYI: disabling query cache by setting its size to 0 before 5.5 (or some version of 5.1??) does not completely disable the query cache.

After setting the cache size to 0 on a Sunday, I monitored the server for a few hours but did not see any issues and then comes next morning and I started seeing CPU bound load with several running processes fighting for CPU ('r' column in vmstat for a 5 seconds average was showing me above 20 values more often). The load average was mostly above 10 (processors: physical = 2, cores = 12).

Then I decided to set the size to 32M and all of a sudden everything started working fine as if nothing ever happened and now the 'r' column was mostly under 5 and load average was under 5.

The lesson that I learned is: No matter how bad query cache might be, it can still improve performance significantly on the right type of load. I was running MySQL 5.1.58, so I am not sure how your version of MySQL will respond to the changes. Please make sure you know what you are doing before making any configuration changes.

I'd highly recommend everyone to monitor query cache activity through Cacti graphs or MySQL administrator or any other profiler.

Did this tutorial help a little? How about buy me a cup of coffee?

Buy me a coffee at ko-fi.com

Please feel free to use the comments form below if you have any questions or need more explanation on anything. I do not guarantee a response.

IMPORTANT: You must thoroughy test any instructions on a production-like test environment first before trying anything on production systems. And, make sure it is tested for security, privacy, and safety. See our terms here.

Comments (write a comment):

Hi there,

Did you tried to restart mysql as well? As I can recall, disabling query cache (set type to 0 and size to 0) runtime in 5.1 is doesn't mean that the locking will go away.

Bests,
Istvan Posted by: Istvan P on Oct 04, 2011

Each case is different, I'm agree with idea that the query cache is sometimes useful. Posted by: Cédric on Oct 04, 2011

@Istvan: no, I did not try restarting the server but I will do that just to give it another try and share the results.

But, even with a cache size of 0 the mutex should have been held for brief periods since the cache size is 0 so less work needs to be done on the cache b/c of its size so technically (unless I am misunderstanding something) I should have seen some improvement at least but the significant reverse affect is puzzling. Posted by: Shahryar G on Oct 07, 2011

Mutexes are involved even when you set the type and size to 0. Percona has fixed this (or at least improved it).

If you are using the QC, don't make it too big. Some of the maintenance is performed by linearly scanning the entries. A rough guideline is to not ask for more than query_cache_size = 50M. Posted by: Rick J on Feb 27, 2012

leave a comment on tutorial leave a comment