You can’t turn Query Cache Off before MySQL 5.5

Posted in: Technical Track

… or at least, not completely!

It often happens to see and hear of replication SQL thread almost always in state “invalidating query cache entries (table)” .

The classic tip is “set global query_cache_size=0” .

While sometime this works, others don’t; the query cache is “disabled” and you still see the SQL thread in state “invalidating query cache entries (table)”.

That is related to a known bug, fixed in MySQL 5.5 :

http://bugs.mysql.com/bug.php?id=38551

In short, in all MySQL versions before 5.5 , the query cache mutex is acquired even if query_cache_size=0 and query_cache_type=OFF : always!

That is, even if the query cache is not enabled, the mutex (slow, by nature) to access the (not existing) query cache is acquired for every binlog event.

The only way to not acquire the query cache mutex in MySQL pre-5.5 is to compile MySQL without query cache. No other workarounds!

For MySQL 5.5 , to completely disable the query cache (thus, not acquiring the query cache mutex) is required to set query_cache_type=OFF at startup (not at runtime).

That also means that if query_cache_type=OFF at startup, you need to restart MySQL if later you want to enable the query cache (you can’t enable it at runtime).

The query mutex becomes a very hot spot especially if you are using RBR. In fact, with RBR there is a binlog event for each modified row, thus the mutex is acquired for every row.

 

References:

http://bugs.mysql.com/bug.php?id=38551

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_query_cache_type

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_query_cache_type

Interested in working with Rene? Schedule a tech call.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *