MySQL Query Cache not necessarily a bad thing
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.
Please feel free to use the comments form below if you have any questions or need more explanation on anything. Use the icons below to share this tutorial with your friends.