Mysql slave invalidating query cache entries
/" fat-fingering the space, I'm going to blame myself only - not fileutils or bash - this has nothing to do with the database- (about backups) "Unless you meticulously lock tables or make the database read-only for the duration," - this is not trivial, but logging onto slave and doing "FLUSH TABLES WITH READ LOCK, sync, snapshot, UNLOCK TABLES" is not rocket science either.
And it's well documented on their "backup methods" page.- "It's unrealistic to expect every single user to run SHOW CREATE TABLE before every single query, or to memorize the types of every column in your schema, though." - ... we shouldn't ask them to remember the syntax either - just keep guessing until you get everything right ;)- "Foreign keys are ignored if you spell them certain, common, ways" - another case of "I want to use the wrong syntax, but still get the right answer"I really wish he limited himself to hard facts - the main idea of the article wouldn't suffer at all.
) - and move your way up as the read performance increases without reaching "Waiting for query cache lock" on writes.
"Be cautious about sizing the query cache excessively large, which increases the overhead required to maintain the cache, possibly beyond the benefit of enabling it. Sizes in the hundreds of megabytes might not be." is not a useful answer.
If I turn off either the Replication or the Query Cache then the lock up does not happen.Platform: , with only difference in that the post-processed data is stored using separate INSERT and UPDATE queries rather than INSERT ON DUPLICATE KEY UPDATE.My SQL configuration (on both the Debian 6 and 7 machines): Try to reduce the query cache size significantly. Start with 16M or 32M and adjust the query_cache_limit accordingly (256K?to make invalidation more granular, you can use a local function: and m2m changes.Invalidation tries to be granular which means it won't invalidate a queryset that cannot be influenced by added/updated/deleted object judging by query conditions.
From My SQL's own docs:"Furthermore, Inno DB does not recognize or support “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification.