Invalidating query cache entries mysql
So, the jumpy behaviour is caused by not using a global timestamp for replication delay, but simply the delay behind the last "hop" in the replication chain.We found this severely annoying and now use My SQL's event scheduler to update a timer table on each master every second, so we can actually see actual delay from the global master (in a non-ring topology) or delay from any peer in a ring.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 My SQL 5.5 : 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.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.For Inno DB under My SQL 4.0, the query cache was disabled for transactions.
Since they get invalidated so quickly, it seems to me the best thing would be to just use SQL_NO_CACHE on SELECT statements with these tables.
If you must have the query cache and you have so much RAM, you could execute the following: That leaves 7G. Keep in mind that the key buffer caches only My ISAM index pages, while the Inno DB Buffer Pool caches data and indexes.