I've run a series of OLTP benchmarks with varying write intensity (10, 22, 80 and 100% writes). I tested 3 different log settings:
- innodb_flush_log_at_trx_commit=0, sync-binlog=0
- innodb_flush_log_at_trx_commit=1, sync-binlog=0
- innodb_flush_log_at_trx_commit=1, sync-binlog=1
Naturally there is a big difference between 1. and 2., but also between 2. and 3. Here I see a performance drop between 10% and 50%. The effect is more pronounced when the datadir resides on a hard (rotating) disk. But even on a (fast) SSD the effect is clearly visible. For details see the attached spread sheets.
I tested MariaDB 10.1 and 10.2 and also MySQL 5.7. The numbers are in the same ballpark for all 3 of them with some slight advantage for 10.2. This however is no reason to celebrate, because in absolute numbers 10.2 comes out at the bottom.
I also spotted an irregularity here: it seems that MariaDB 10.2 does two fsyncs per commit on the InnoDB redo log as soon as one sets innodb_flush_log_at_trx_commit=1 and enables the binlog (no matter if sync-binlog=0 or 1). This is someting to investigate in a separate task.
I also checked the bytes written per transaction to the redo log and the binlog. There is a moderate increase of ~6 bytes per binlog event in 10.2 compared to 10.1. No numbers here for MySQL (it does not have a status variable for binlog writes).
I've run a series of OLTP benchmarks with varying write intensity (10, 22, 80 and 100% writes). I tested 3 different log settings:
Naturally there is a big difference between 1. and 2., but also between 2. and 3. Here I see a performance drop between 10% and 50%. The effect is more pronounced when the datadir resides on a hard (rotating) disk. But even on a (fast) SSD the effect is clearly visible. For details see the attached spread sheets.
I tested MariaDB 10.1 and 10.2 and also MySQL 5.7. The numbers are in the same ballpark for all 3 of them with some slight advantage for 10.2. This however is no reason to celebrate, because in absolute numbers 10.2 comes out at the bottom.
I also spotted an irregularity here: it seems that MariaDB 10.2 does two fsyncs per commit on the InnoDB redo log as soon as one sets innodb_flush_log_at_trx_commit=1 and enables the binlog (no matter if sync-binlog=0 or 1). This is someting to investigate in a separate task.
I also checked the bytes written per transaction to the redo log and the binlog. There is a moderate increase of ~6 bytes per binlog event in 10.2 compared to 10.1. No numbers here for MySQL (it does not have a status variable for binlog writes).