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

InnoDB flushes redo log too often

    XMLWordPrintable

    Details

      Description

      When I did the benchmark tests for MDEV-11900 I checked the size of the commit groups for the InnoDB redo log and the server binlog. An unexpected result was, that for MariaDB 10.2.3 and low thread counts I saw InnoDB doing twice as many redo log fsyncs (Innodb_os_log_fsyncs) as COMMIT operations (Com_commit). MariaDB 10.1 and MySQL 5.7 don't show this behavior. They do only very few more fsyncs than COMMITs.

      At higher thread counts the commit group size in MariaDB 10.2 increases as expected, but it stays well below the numbers I see for MariaDB 10.1 and MySQL 5.7. I suspect that InnoDB always does two fsyncs on the redo log when it should do only one.

      The effect gets visible with innodb_flush_log_at_trx_commit=1 and enabled binlog (log-bin = xxx) no matter if sync-binlog = 0 or sync-binlog = 1. Disabling the binlog puts everything back to normal. Another visible effect of the excessive flushing is reduced throughput in read/write OLTP benchmarks in comparison to MariaDB 10.1 and MySQL 5.7.

      The commit group size for the binlog is also smaller in MariaDB 10.2 (again in comparison to MariaDB 10.1 and MySQL 5.7).

      Another unexpected observation is this: the InnoDB redo log commit group size decreases, when going from sync_binlog=0 to sync_binlog=1. For the binlog commit group size it's the other way round: sync_binlog=1 gives bigger groups than sync_binlog=0. Also both numbers differ significantly. I had expected to see roughly the same commit group size for the binlog and the redo log. The indicator I used here is Binlog_commits in relation to Binlog_group_commits.

      I attach a spread sheet with my numbers. See also MDEV-11900 for throughput numbers.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              knielsen Kristian Nielsen
              Reporter:
              axel Axel Schwenke
              Votes:
              1 Vote for this issue
              Watchers:
              11 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: