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

Galera is 3x slower than standalone with durable settings

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 10.1.20
    • N/A
    • Galera
    • CentOS 7.2 x86_64 (xfs), Ubuntu 16.04 x86_64 (ext4)

    Description

      When comparing a single-node Galera with a standalone MariaDB, I have noticed a suspiciously big performance difference with durable/non-durable settings:

      1. Galera is 3x slower than standalone, both with durable settings
      2. Durable Galera is 3x slower than non-durable Galera

      Config:

      [mysqld]
       
      # durable
      sync_binlog=1
      innodb_flush_log_at_trx_commit=1
       
      # non-durable
      # sync_binlog=0
      # innodb_flush_log_at_trx_commit=2
       
      max_connections=2000
       
      query_cache_type=0
      query_cache_size=0
       
      log_bin=1
      binlog_format=ROW
      log_slave_updates=1
       
      innodb_flush_method=O_DIRECT
      innodb_buffer_pool_size=4000M
      innodb_buffer_pool_instances=4
      innodb_log_buffer_size=64M
       
      [galera]
       
      wsrep_on=ON
      wsrep_provider=/usr/lib64/galera/libgalera_smm.so
      innodb-autoinc-lock-mode=2
      wsrep_cluster_name=galera
      wsrep_node_address=node1
      wsrep_node_name=node1
      wsrep_cluster_address=gcomm://
      wsrep_sst_method=rsync
      wsrep_slave_threads=8
      

      Benchmark: Sysbench 0.5

      sysbench \
      --test=/usr/share/doc/sysbench/tests/db/oltp.lua \
      --mysql-host=localhost \
      --mysql-user=root \
      --oltp-table-size=1000000 \
      --num-threads=128 \
      --max-requests=0 \
      --max-time=60 \
      run
      

      Results:

      Galera, durable
      read/write requests: 4994.74 per sec.

      Standalone, durable
      read/write requests: 16858.99 per sec.

      Galera, non-durable
      read/write requests: 15938.04 per sec.

      Standalone, non-durable
      read/write requests: 17055.88 per sec.

      Server details:

      2 Cores
      8 GB RAM
      SSD
      

      I have repeated the tests multiple times, even re-bootstrapped the data directory and Galera.

      What is also interesting, the CPU idle stays above 50% with the durable Galera, while it stays under 1% in other testing scenarios.

      Update:
      Changing wsrep_slave_threads to 4, innodb_buffer_pool_instances to 2 and including innodb-autoinc-lock-mode=2 in mysqld didn't change anything.

      Another interesting thing: iowait is over 20% with the durable Galera, while it is under 1% in other scenarios.

      Attachments

        Issue Links

          Activity

            People

              jplindst Jan Lindström (Inactive)
              anybodywise amq
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.