Invalidating query cache entries
id=38551 In short, in all My SQL 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.
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)”.
For My SQL 4.1 , Inno DB plays traffic cop when allowing access to the query cache on a per-table basis.I belive the issue is not related to long running queries.First because i dont't see the servers processing anything, and second, because as i mentiomed in update4, the server stops processing and gets stuck on invalidating cache on the old non-Percona servers which caused the replication to halt until the cache was invalidated (Which took a lot of time). id=60696 We solved the issue by moving entirely to Percona My SQL server v5.5 which has the ability to disable Query Cache completely.You should contact the help centre via the link at the bottom of the page, to ask for your multiple accounts to be merged.
I'm currently working on a My SQL database where we are seeing a large number of invalidations from the query cache, primarily because of the high number of INSERT, DELETE and UPDATE statements that are being executed on many of the tables.
We lately mved to My SQL V5.5 on Cent OS release 6.7 with master and 2 slaves.