AWS RDS Read repica failure issue

Question

We created read replica on our MySQL RDS server and our master instance has multi-AZ enabled, when we tried to force fail-over testing our read replica's IO thread got stopped and we were getting Error 1236 fatal error our binary logs got corrupted.

To avoid this replica failure it's mandatory to enable innodb_flush_log_at_trx_commit=1 and sync_binlog=1 but if we set these variable as per recommendation then its degrade our write operation by 50% - 60%.

Is there any way through that we can avoid this replication error instead of setting above recommended value else if it's necessary to set as per recommendation then kindly suggest us a way how we can improve our write operations?

Answer 1

This Answer applies to MySQL replication in general, not just AWS.

If you are that close to exceeding the capacity of the system, you need to do some serious research into what is going on.

The short answer is to combine (where practicable) multiple transactions into one. innodb_flush_log_at_trx_commit=1 involves an 'extra' fsync at the end of each transaction. So, fewer transactions --> less I/O --> less contention.

Before I understood what was going on, I rand with sync_binlog=0. When something did crash, the binlogs were not actually "corrupt", but the Slaves would be pointing to an "impossible position". This is because the position information had been sent to the Slave before actually writing to disk on the Master. The solution was simple: Move the pointer (on the Slave) to the beginning (Pos=0 or 4) of the next binlog (on the Master).

I suspect (without any real evidence) that innodb_flatc has more impact on performance than sync_binlog.

Now for some AWS-specifics. If "multi-AZ" means that every disk write is writing to machines in two different datacenters, then the issue goes beyond just the two settings you brought up. If, instead, it means that the Slave is remote from the Master, then it is acting like ordinary MySQL Replication (for this Q&A).