You can’t turn Query Cache Off before MySQL 5.5

Jul 6, 2011 / By Rene Cannao


… 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 :

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.



Share this article

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>