Mysql slave invalidating query cache entries table
Your app can survive one slave node going down and still serve the traffic.
Any slave can be taken out of a pool for a maintenance without causing a problem to the application. Let’s take a look at some real life examples…Imagine the typical scenario of a user performing a page edit: Reading from slave shortly after altering the data we’re interested in is a tricky business — we may get data from before the update. Instead of invalidating the cache we update it with the latest data., mostly INSERT statements, around 5-10 rows per second. In 5-10 seconds when it will be time to process new data again the same lock up will happen. Query #2 is the updating of the post-processed data.A PHP based application is running on the server that reads the freshly replicated data every 5-10 seconds, processes it and stores (INSERT ON DUPLICATE KEY UPDATE) results in a separate database . A web application displays the post-processed data for the user. Query #3 is streaming (unbuffered) the newly replicated data for processing.XX Waiting for query cache lock INSERT INTO `db B`.`table B` (...) VALUES (...) ON DUPLICATE KEY UPDATE ... Simultaneous SELECT and INSERT in one database seems to cause a dead lock with query cache update by INSERT ON DUPLICATE KEY UPDATE in a different database.If I turn off either the Replication or the Query Cache then the lock up does not happen.The query cache has been disabled-by-default since My SQL 5.6 (2013) as it is known to not scale with high-throughput workloads on multi-core machines.
Rene confirmed this in his post yesterday, but it has also previously been mentioned by Stewart Smith, Domas Mituzas (update: and Kristian Koehntopp).
That is to say that we are shifting strategy to invest in improvements that are more generally applicable to all workloads.
We also agree with Rene’s conclusion, that caching provides the greatest benefit when it is moved closer to the client: With the current limitations noted, the query cache will continue to be supported for the life time of My SQL 5.7.
A hypothetical example might be a complex query to build a list of values for a drop-down list that always appears on a webpage form.
In a situation like this, the query cache can mask performance problems caused by missing indexes, which makes it helpful for novice users.
While this approach solves scaling problems, it brings its own set of problems. All writes (like INSERT, UPDATE and DELETE queries and schema ALTERs) go to a master node.