Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
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.
Using 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 innodb_flush_log_at_trx_commit.
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.
