Mysql sync_binlog = 50 and innodb_flush_log_at_trx_commit = 2 with replication

MySQLreplication

I'm looking into sync_binlog = 50 and innodb_flush_log_at_trx_commit = 2 to improve write performance. There is this question, but it doesn't address the specific problems with replication.

I have an application that processes incoming log data with dozens of commits per second, causing a lot of flushing to disk, and losing one second of data is not a problem for the increase in speed it gives. However, The docs state:

For durability and consistency in a replication setup that uses InnoDB
with transactions [set both options to 1].

But it doesn't specify the issues with consistency and durability. I would like to know what exactly can go wrong. If you have a hardware crash and the slave becomes live, it may be one second behind the original master. Are there subsequent problems in getting that failed master back online (as slave)? Could there be mismatches in ID's, duplicate rows, etc?

As I said, loosing data is OK, but breaking replication is not.

Edit: BTW, by now, the problem is solved another way. Long story, but an comprehensive schema change made it possible run log processors in parallel. Still slow per process, but many at a time makes it fast.

Best Answer

Another way to get past these possible problems is to use a Galera Cluster (eg, MariaDB 10 or PXC). With Galera, you can (should) be sloppy about such syncs; instead rely on the cluster's ability to recover automatically and efficiently from a dead machine, whether dead from power, OS, or whatever. One caveat, though: You need to make sure a single failure (such as power) won't kill all nodes in the cluster simultaneously.

With Galera, you can write and read any node. 3 is the minimal number of nodes to provide automatic recovery from the loss of any one node. You can configure it to do all writes to a specific node, thereby pretending to have Master+Slaves configuration. (In some applications, this is actually beneficial.)

Back to your situation. Set both of those settings to 1 or the Master; set them to 50 and 2 for the Slaves, then... If a Slave dies, assume it is corrupt and rebuild it; do not try to recover the data, since the necessary syncs may not have been done. To lighten the load on the Master, don't send standalone reads to it.