Using innodb_flush_log_at_trx_commit = 2 with RDS MultiAZ - Safe?
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 https://dba.stackexchange.com/questions/12611/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?
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.