All we need is an easy explanation of the problem, so here it is.
I understand the downsides of using the parameter setting
innodb_flush_log_at_trx_commit = 2
If you want to know more, look at another answer, for example Is it safe to use innodb_flush_log_at_trx_commit = 2
What I am trying to understand is – If I use a MultiAZ RDS server does this mean the write will happen to the other server also, thus in a failure I will keep data?
Or does the replication happen AFTER the write has happened?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Multi-AZ RDS uses block-level replication to replicate the I/O, so it will wait for block I/O requests on the local instance, then replicates that write to the instance on the other AZ.
innodb_flush_log_at_trx_commit = 2, log writes use buffered I/O, so they may delay the block I/O request for up to 1 second.
In other words, flushing the innodb log is what triggers the sync to the other multi-AZ instance. The multi-AZ sync doesn’t happen until the innodb log is flushed locally first.
Therefore multi-AZ does not mitigate the risk of data loss if you use a relaxed values for
If you’re trying to get better performance by using that relaxed setting, but also have assurance of HA, you cannot do that. RDS performance is generally bad. If you need high write throughput, you need a different platform.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂