The question:
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?
The Solutions:
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
Method 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.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0