Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-11900

Run benchmarks with sync-binlog before it gets enabled in 10.2 by default

Details

    Description

      We are about to enable sync-binlog by default in 10.2. Please check how it affects performance on some standard read-write load with binary log enabled. No need for accurate numbers, but we should have at least a rough picture.

      Attachments

        Activity

          axel Axel Schwenke added a comment -

          I've run a series of OLTP benchmarks with varying write intensity (10, 22, 80 and 100% writes). I tested 3 different log settings:

          1. innodb_flush_log_at_trx_commit=0, sync-binlog=0
          2. innodb_flush_log_at_trx_commit=1, sync-binlog=0
          3. 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).

          axel Axel Schwenke added a comment - 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'd say 50% is not too bad, that's something that I would initially expect, my results were much worse (but they aren't reliable).

          I don't see it mentioned anywhere, did you run with the default statement binlog_format, or were there different ones? I wonder if it might make a difference.
          Our new default is expected to be mixed, which can easily switch to row.

          elenst Elena Stepanova added a comment - I'd say 50% is not too bad, that's something that I would initially expect, my results were much worse (but they aren't reliable). I don't see it mentioned anywhere, did you run with the default statement binlog_format, or were there different ones? I wonder if it might make a difference. Our new default is expected to be mixed, which can easily switch to row.

          People

            axel Axel Schwenke
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.