Mysql master-slave replication very slow

I went for the first option.

That worked up to the point where the slave started trying to do inserts that conflicted with the primary keys. The slave had done more work than the master bin-log had persisted, as previously mentioned. One aspect I didn't anticipate was that the slave contained data that wasn't in the master; i.e. the slave persisted some transactions before the power outage that the master HAD NOT persisted.

Since for my case, these transactions weren't payment-related or similar, I chose to delete the data from the slave (thereby losing some data that had happened, but which didn't exist in the master) and then let replication run again. This brought the slaves completely up to date. If the data had been more important, we have auto-increment offsets sufficient to give us some wriggle room for manually wrangling the data and ensuring referential integrity was not compromised. Thankfully we didn't need to do that in this case.

For a machine in (passive) master-master configuration which was in this predicament, I chose a similar approach. By passive master-master, I mean that we have an active master (serverA) which is where all the writes go, and a passive master (serverB) that is in place to allow for schema updates to take place with zero downtime. The data in active master (serverA) was chosen as the true values, despite knowing that this meant we lost a couple of persisted transactions which weren't deemed important.

• Changed the log file and position on the slave.

  CHANGE MASTER MASTER_LOG_FILE='bin-log.004713', MASTER_LOG_POS=0; -- on serverB
  

• Restarted slave replication on the passive master (serverB) until it failed with primary key constraint violations, as with the other slaves.

   START SLAVE; -- on serverB
  

• Stopped slave replication from the passive master (serverB) to the active master (serverA).

  STOP SLAVE; -- on serverA
  

• DELETE the rows on the slave (serverB) which didn't exist in the master on serverA.

  DELETE FROM SOME_TABLE WHERE ID IN (???,????); -- on serverB
  SHOW MASTER STATUS\G; -- get the new master log position on serverB
  

• Move the active master (serverA) slave exec position to skip those deletes from the passive master (serverB).

  CHANGE MASTER TO MASTER_LOG_POS=???; --on serverA; use the value just obtained from serverB
  

• Restart replication on both the active master (serverA) and passive master.

  START SLAVE; -- on both machines. serverA does nothing and serverB starts catching up.
  

This would be interesting to write down while catching up:

• how much bandwidth is used

• how much cpu utilization (especially i/o wait)

• ram usage

In response to the new information about i/o wait being high; for innodb a lot can be done, take a look at. I've learned many things from mysqlperformanceblog. Here's some hints:

innodb_flush_method=O_DIRECT

"Avoid double buffering and reduce swap pressure, in most cases this setting improves performance. Though be careful if you do not have battery backed up RAID cache as when write IO may suffer."

innodb_flush_log_at_trx_commit=2

"If you’re not concern about ACID and can loose transactions for last second or two in case of full OS crash than set this value. It can dramatic effect especially on a lot of short write transactions."

Those did WONDERS for us, but the drawback is that you might loose a second of written data. This is because instead of write (flush to disk) every record, you flush every second.

You can read more on: http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/