2

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?

1 Answer 1

1

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.

1
  • Bill thanks for a clean summary of this, much appreciated. "Just add resources" for now I think! Mar 24, 2022 at 11:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.