You can't turn Query Cache Off before MySQL 5.5

 

... 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

Comments

What version of MySQL 5.1 did you test it? I think there is a specific MySQL 5.1 version after that MySQL disables query cache.

Mon, 10/03/2011 - 15:12

what is the mutex filename? how can I check it in the output of show mutex etc.?

Wed, 09/28/2011 - 16:57

FYI: You can disable the query caches in Percona Server: http://www.percona.com/docs/wiki/percona-server:features:query_cache_enh...

Kenny Gryp
Thu, 07/07/2011 - 05:27

Reply

The content of this field is kept private and will not be shown publicly.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

More information about formatting options

By submitting this form, you accept the Mollom privacy policy.
Website by Digital Loom