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

Different Galera performance behavior regarding to flushing between MySQL and MariaDB

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

          duplicates

          Bug - A problem which impairs or prevents the functions of the product. MDEV-16509 MariaDB 10.1+ with wsrep_on=ON performs better when log_bin is enabled

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          relates to

          Task - A task that needs to be done. MDEV-11376 AliSQL: [Feature] Issue#11 REDO LOG GROUP COMMIT AT SERVER LAYER

          • Major - Major loss of function.
          • Stalled

          Bug - A problem which impairs or prevents the functions of the product. MDEV-11937 InnoDB flushes redo log too often

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-16442 Document tc.log usage by default and the fact that wsrep in 10.1+ is an XA-capable storage engine

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            View 7 older comments
            hholzgra Hartmut Holzgraefe added a comment -

            Could not reproduce it with the backup of my original test setup (may not have recovered the actual final backup of it).

            With manual testing I now see it again though.

            Platform: Ubuntu 18.04.1, datadir on SSD

            Versions tried: 10.2.8, 10.2.19, 10.3.11

            Basic single node configuration:

            [mysqld]
            		 
            datadir=/usr/local/mysql/var
            		 
            server-id=1
            		 
            wsrep_on=ON
            		 
            wsrep_provider=/usr/local/mariadb-10.3.11-linux-systemd-x86_64/lib/galera/libgalera_smm.so
            		 
            wsrep_cluster_name=test_cluster
            		 
            wsrep_cluster_address=gcomm://192.168.23.15
            		 
            wsrep_sst_method=rsync
            		 
            wsrep_sst_auth=root
            		 
            wsrep_node_address=192.168.23.15
            		 
            wsrep_node_name=node-1
            		 
            binlog-format=ROW

            With just extra 

            [mysqld]
            		 
            log-bin

            using defaults for sync-binlog and innodb-flush-log-at-trx-commit I get about 90tps on all three versions.

            With extra 

            [mysqld]
            		 
            log-bin
            		 
            sync-binlog=0
            		 
            innodb-flush-log-at-trx-commit=0

            I see a speedup to about 1000tps.

            When I disable the binlog:

            [mysqld]
            		 
            # log-bin
            		 
            sync-binlog=0
            		 
            innodb-flush-log-at-trx-commit=0

            I get about 250-300tps.

            For running sysbench I use 

            mysql -h 192.168.23.15 -u root -e "drop database if exists sbtest"
            		 
            mysql -h 192.168.23.15 -u root -e "create database sbtest"
            		 
            /usr/bin/sysbench oltp_read_write --db-driver=mysql --mysql-host=192.168.23.15 --mysql-user=root --table_size=100000 --tables=10 --events=1000000 --threads=16 prepare
            		 
            /usr/bin/sysbench oltp_read_write --db-driver=mysql --mysql-host=192.168.23.15 --mysql-user=root --table_size=100000 --tables=10 --events=1000000 --threads=16 run
            		 
            /usr/bin/sysbench oltp_read_write --db-driver=mysql --mysql-host=192.168.23.15 --mysql-user=root --table_size=100000 --tables=10 --events=1000000 --threads=16 run

            I'm going to attach the sysbench output for 10.3.11

            hholzgra Hartmut Holzgraefe added a comment - Could not reproduce it with the backup of my original test setup (may not have recovered the actual final backup of it). With manual testing I now see it again though. Platform: Ubuntu 18.04.1, datadir on SSD Versions tried: 10.2.8, 10.2.19, 10.3.11 Basic single node configuration: [mysqld] datadir=/usr/local/mysql/var server-id=1 wsrep_on=ON wsrep_provider=/usr/local/mariadb-10.3.11-linux-systemd-x86_64/lib/galera/libgalera_smm.so wsrep_cluster_name=test_cluster wsrep_cluster_address=gcomm://192.168.23.15 wsrep_sst_method=rsync wsrep_sst_auth=root wsrep_node_address=192.168.23.15 wsrep_node_name=node-1 binlog-format=ROW With just extra [mysqld] log-bin using defaults for sync-binlog and innodb-flush-log-at-trx-commit I get about 90tps on all three versions. With extra [mysqld] log-bin sync-binlog=0 innodb-flush-log-at-trx-commit=0 I see a speedup to about 1000tps. When I disable the binlog: [mysqld] # log-bin sync-binlog=0 innodb-flush-log-at-trx-commit=0 I get about 250-300tps. For running sysbench I use mysql -h 192.168.23.15 -u root -e "drop database if exists sbtest" mysql -h 192.168.23.15 -u root -e "create database sbtest" /usr/bin/sysbench oltp_read_write --db-driver=mysql --mysql-host=192.168.23.15 --mysql-user=root --table_size=100000 --tables=10 --events=1000000 --threads=16 prepare /usr/bin/sysbench oltp_read_write --db-driver=mysql --mysql-host=192.168.23.15 --mysql-user=root --table_size=100000 --tables=10 --events=1000000 --threads=16 run /usr/bin/sysbench oltp_read_write --db-driver=mysql --mysql-host=192.168.23.15 --mysql-user=root --table_size=100000 --tables=10 --events=1000000 --threads=16 run I'm going to attach the sysbench output for 10.3.11
            hholzgra Hartmut Holzgraefe added a comment -

            mariadb-10.3.11-1.log – log-bin enabled, sync/flush off, ~1000tps
            mariadb-10.3.11-2.log – log-bin disabled, sync/flush off, ~300tps
            mariadb-10.3.11-3.log – log-bin enabled, sync/flush on, ~100tps

            hholzgra Hartmut Holzgraefe added a comment - mariadb-10.3.11-1.log – log-bin enabled, sync/flush off, ~1000tps mariadb-10.3.11-2.log – log-bin disabled, sync/flush off, ~300tps mariadb-10.3.11-3.log – log-bin enabled, sync/flush on, ~100tps
            hholzgra Hartmut Holzgraefe added a comment -

            See also https://jira.mariadb.org/browse/MDEV-16509

            hholzgra Hartmut Holzgraefe added a comment - See also https://jira.mariadb.org/browse/MDEV-16509
            rgpublic Ranjan Ghosh added a comment -

            Hm. I cannot see any difference. I get 3000 tps consistently with the script mentioned above. BTW: Isn't sync_binlog=0 the default anyway?

            rgpublic Ranjan Ghosh added a comment - Hm. I cannot see any difference. I get 3000 tps consistently with the script mentioned above. BTW: Isn't sync_binlog=0 the default anyway?
            seppo Seppo Jaakola added a comment -

            The fix for MDEV-16509 improved the performance of non binlogging use case (log_bin==OFF). And it looks like MDEV-16509 is a duplicate of this issue, so imo this issue could be closed.

            seppo Seppo Jaakola added a comment - The fix for MDEV-16509 improved the performance of non binlogging use case (log_bin==OFF). And it looks like MDEV-16509 is a duplicate of this issue, so imo this issue could be closed.

            People

              jplindst Jan Lindström (Inactive)
              hholzgra Hartmut Holzgraefe
              Votes:
              1 Vote for this issue
              Watchers:
              13 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.