Last week at the OUG Harmony conference thanks to Dimitri Kravtchuk I learned that setting query_cache_size=0 does not disable and remove locking from the Query Cache. You actually need to also set query_cache_type=0. This appears to been a bug, seen in the presently still open MySQL bugs database entry #38511.
My recommendation to customers now is to set both variables on all existing MySQL versions if you are not using the MySQL Query Cache.
Thanks to the Performance Schema in MySQL 5.5 for uncovering this. More information in Dimitri’s detailed post at MySQL Performance: Using Performance Schema
Details of all MySQL presentations at OUG Harmony 2011 in Helsinki, Finland.
Fixed in Drizzle of course, but I’m sure you already knew that
I personally prefer simply disabling it in configure, so the maligned code is simply gone.
Yeah I can confirm, when i did my benchmark testing last month, I had to set query_cache_type=0 as well. I read it somewhere hence why I set it
Performance Schema didn’t play a role in discovering this. Former Sun performance engineer Neel Nadgir discovered it with DTrace back in 2008 and reported it at http://bugs.mysql.com/bug.php?id=38551 . Neel also did a lot of the excellent early Sun work on finding InnoDB bottlenecks. Dimitri just didn’t know about it until I told him when he noticed it in that blog entry.
This is documented with “To reduce overhead significantly, also start the server with query_cache_type=0 if you will not be using the query cache.” at http://dev.mysql.com/doc/refman/5.5/en/query-cache-configuration.html and with: “If the server is started with query_cache_type set to 0, it does not acquire the query cache mutex at all, which means that the query cache cannot be enabled at runtime and there is reduced overhead in query execution.” at http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_query_cache_type .
Bug http://bugs.mysql.com/bug.php?id=38551 is closed. It’s not backwards compatible so it won’t be changed in 5.1. With the fix, setting the type to 0 prevents enabling the query cache while the server is running and that’s why query_cache_type defaults to 1 in 5.5. Setting query_cache_type to 0 in MySQL 5.1 and earlier doesn’t produce the performance benefit that’s available in 5.5.
Setting it to 0 can be of particular value on a slave with row-based replication.
It’s sometimes interesting to see how long it takes knowledge to spread. The MySQL support team at Oracle knew about this and was telling people about the unfixed issue back in 2008. It was mentioned in a release note with “Performance” tag in December 2009. Dimitri knew about it from May 2010 and Ronald Bradford from May 2011. And you’re still probably ahead of most users so it’ll be usefully doing the rounds as an interesting performance tip for a few more years yet.
Another option that’s worth knowing about is back_log. Use it when your clients are slow to connect to a highly loaded Linux-based MySQL server.
James Day, MySQL principal Support Engineer, Oracle