Mysql replication invalidating query cache entries
For example, if you often use the error for Inno DB too.Regarding data consistency between the master and slave, you need to use row-based replication.
The classic tip is “set global query_cache_size=0” .
It often happens to see and hear of replication SQL thread almost always in state “invalidating query cache entries (table)” .
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 :
We also have a lot of “waiting for table lock” statuses during high volume usage.
Would changing these tables to Inno DB help the replicated database remain consistent? Switching to Inno DB might help, but it depends on what types of queries you use.
Q: We’re using My SQL on r3.4xlarge EC2 instances (16 CPU). innodb_read_io_threads and innodb_write_io_threads =4. Other parameters that could help are similar to the ones discussed in “Inno DB Troubleshooting” and “Introduction to Troubleshooting Performance: What Affects Query Execution? You need to pay attention to Inno DB options that affect IO ( . A: What do you mean by “how many masters can [you] have working together”?
Do you mean circular replication or a multi-master setup? For a multi-master setup you should ensure that the slave has enough resources to process all requests.
Whether you work in SQL Server Management Studio or Visual Studio, Redgate tools integrate with your existing infrastructure, enabling you to align Dev Ops for your applications with Dev Ops for your SQL Server databases.