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

Different Galera performance behavior regarding to flushing between MySQL and MariaDB

    XMLWordPrintable

    Details

      Description

      When doing sysbench tests against standalone mysqld vs. single node Galera instance vs. two node cluster performance differs between MySQL and MariaDB setups.

      With both MySQL and MariaDB standaline non-Galera nodes show best transactions per second throughput when not having to do any commit time fsync, as expected. Meaning that things are fast with innodb_flush_at_trx_commit != 1 and log_bin disabled or sync_binlog=OFF

      A single node MySQL Galera setup shows almost the same numbers, is fast in the same config combinations.

      A MariaDB Galera 10.x cluster, with 0 <= x <= 2 at least, shows a slightly different behavior though. Here only the combination "innodb_flush_at_trx_commit != 1, log_bin enabled, sync_binlog=OFF" is almost as fast as a standalone non-galera-enabled instance with the same "no fsync" settings. When disabling log_bin transactions per second go down quite a bit, to similar values as if innodb_flush_at_trx_commit was actually enabled.

      I first suspected that group_commit might play a role, but Binlog_commits and Binlog_group_commits counters were always showing equal values in SHOW STATUS output, so this can be ruled out.

      When running sysbench against a 2 node cluster performance differences were not as massive as in the single node setup, which was to be expected, but still visible.

      So while turning off innodb_flush_at_trx_commit; and sync_binlog if log_bin is enabled, is usually considered safe in a cluster (if the hardware setup is redundant enough, e.g. simultaneous power failure on all nodes can be ruled out), and to improve performance/throughput a little bit, it turns out that with MariaDB the performance improvement is only visible if binlog writing is enabled, which is at least counter-intuitive.

        Attachments

        1. mariadb-10.1.17-bench1.txt
          1 kB
        2. mariadb-10.1.17-bench2.txt
          1 kB
        3. mariadb-10.2.9-bench1.txt
          1 kB
        4. mariadb-10.2.9-bench2.txt
          1 kB
        5. mariadb-10.3.11-1.log
          4 kB
        6. mariadb-10.3.11-2.log
          4 kB
        7. mariadb-10.3.11-3.log
          4 kB
        8. mysql-5.7.18-bench1.txt
          1 kB
        9. mysql-5.7.18-bench2.txt
          1 kB

          Issue Links

            Activity

              People

              Assignee:
              jplindst Jan Lindström
              Reporter:
              hholzgra Hartmut Holzgraefe
              Votes:
              1 Vote for this issue
              Watchers:
              13 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration