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.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.
First, I want to thank everybody for attending the August 25 webinar.I'm running a 4 servers master-master cluster of My Sql. Replication topology: 1 - 1 UPDATE It seems that server 3 has its SBM at 0, while the other servers are jumping up and down. It looks like the server is busy doing something, and there is a huge delay between when the server gets the statement, and when it executes it. After disabling cache, server 4 is ok but 1&2 are still having this issue. id=60696 If anyone knows how to fix it, i would be glad to hear There is one flaw with mysql's seconds_behind_master value: it only takes into account the position relative to one upstream hop away.(2 servers version 5.1, and 2 version 5.5) While checking the slave status, i see the seconds_behind_master at 0, and half a second after i see it jumps to 2000, and so fourth. Easiest demonstrated with a slightly simpler replication topology: server1 - server3 If server2 falls behind, and is processing some long-running queries, the following will happen, assuming as start point: : Everyone ok : server1 writes two 10-minute queries to the binlog, no replication delay anywhere : server2 starts processing query one. : server2 is done with query 2, replication delay zero again.One question: How is it possible to get N previous events using the SHOW BINLOG EVENTS command? You cannot get the previous five events using and tail its output.For example, the position is 999 and I want to analyze the previous five events. Q: We are having issues with inconsistencies over time.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?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.) - 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.