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

MariaDB 10.1+ with wsrep_on=ON performs better when log_bin is enabled

Details

    Description

      Galera clusters do not necessary need binary logging enabled on nodes, so configuration with binary logging disabled is quite common. It turned out that after upgrade to 10.1.x, these configurations started to perform worse, as notable time is spent on keeping tc.log updates (in pre-10.1 versions tc.log was not maintained in such cases, see MDEV-16442).

      Simple sysbench tests may show this effect. For example, I've prepared the following table on my 2 cores old netbook running under Ubuntu 14.04:

      sysbench --test=/usr/local/share/sysbench/tests/include/oltp_legacy/common.lua --mysql-host=127.0.0.1 --db-driver=mysql --mysql-user=root --mysql-db=test --oltp-table-size=2500000 prepare

      Then I started single node cluster on the same datadir with the following command (all defaults unless specified):

      openxs@ao756:~/dbs/maria10.1$ bin/mysqld_safe --no-defaults --binlog_format=row --wsrep_provider=/usr/lib/libgalera_smm.so --wsrep_on=1 --wsrep_cluster_address=gcomm://127.0.0.1:4567 --wsrep-new-cluster &
      		 
      [1] 6991
      		 
       
      		 
      openxs@ao756:~/dbs/maria10.1$ bin/mysql -uroot -e"select @@log_bin, @@wsrep_on, @@wsrep_cluster_address"
      		 
      +-----------+------------+-------------------------+
      		 
      | @@log_bin | @@wsrep_on | @@wsrep_cluster_address |
      		 
      +-----------+------------+-------------------------+
      		 
      |         0 |          1 | gcomm://127.0.0.1:4567  |
      		 
      +-----------+------------+-------------------------+

      and the run sysbench test with N concurrent threads, where N in (4,8,16):

       sysbench --test=/usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=127.0.0.1 --db-driver=mysql --mysql-user=root --mysql-db=test --oltp-table-size=2500000 --report-interval=1 --max_requests=0 --max-time=300 --threads=*N* run

      I've got the following (low) TPS values:

      threads | TPS
      		 
      4       |  6.08
      		 
      8       |  8.46
      		 
      16      |  8.96

      Then I stopped the node and restarted it again with log_bin enabled:

      openxs@ao756:~/dbs/maria10.1$ bin/mysqld_safe --no-defaults --binlog_format=row --wsrep_provider=/usr/lib/libgalera_smm.so --wsrep_on=1 --wsrep_cluster_address=gcomm://127.0.0.1:4567 --wsrep-new-cluster --log_bin=1 &
      		 
       
      		 
      openxs@ao756:~/dbs/maria10.1$ 180618 13:46:14 mysqld_safe Logging to '/home/openxs/dbs/maria10.1/data/ao756.err'.
      		 
      180618 13:46:14 mysqld_safe Starting mysqld daemon with databases from /home/openxs/dbs/maria10.1/data
      		 
       
      		 
      openxs@ao756:~/dbs/maria10.1$ bin/mysql -uroot -e"select @@log_bin, @@wsrep_on, @@wsrep_cluster_address"
      		 
      +-----------+------------+-------------------------+
      		 
      | @@log_bin | @@wsrep_on | @@wsrep_cluster_address |
      		 
      +-----------+------------+-------------------------+
      		 
      |         1 |          1 | gcomm://127.0.0.1:4567  |
      		 
      +-----------+------------+-------------------------+

      I've got the following results, with TPS values still small, but notably higher:

      threads | TPS
      		 
      4       |  14.78
      		 
      8       |  15.00
      		 
      16      |  15.56

      It seems we should do something to reduce the impact of log_bin NOT specified or clearly recommend to set it explicitly in the KB articles for Galera nodes on 10.1.x+

      Side node: I do NOT see the same effect without wsrep_on = ON - there without log_bin enabled we run a bit faster.

      Attachments

        Issue Links

          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14339 Different Galera performance behavior regarding to flushing between MySQL and MariaDB

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

          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

            Transition Time In Source Status Execution Times
            Julien Fritsch made transition -
            Open Confirmed
            		162d 3h 7m 1
            Seppo Jaakola made transition -
            Confirmed In Progress
            		57d 22h 33m 1
            Jan Lindström (Inactive) made transition -
            In Progress Closed
            		49d 16h 4m 1

            People

              jplindst Jan Lindström (Inactive)
              valerii Valerii Kravchuk
              Votes:
              1 Vote for this issue
              Watchers:
              10 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.