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




      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= --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:// --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://  |

      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= --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:// --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://  |

      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.


          Issue Links



              jplindst Jan Lindström
              valerii Valerii Kravchuk
              1 Vote for this issue
              10 Start watching this issue