[MDEV-11599] Galera is 3x slower than standalone with durable settings Created: 2016-12-18  Updated: 2019-05-20  Resolved: 2019-05-20

Status: Closed
Project: MariaDB Server
Component/s: Galera
Affects Version/s: 10.1.20
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: amq Assignee: Jan Lindström (Inactive)
Resolution: Not a Bug Votes: 0
Labels: performance
Environment:

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.

 Comments   
Comment by Daniel Black [ 2016-12-19 ]

Galera isn't great a write scaling. Its good for read scaling and failover. Having said that the following may help:

With such a high write load increasing the innodb_log_file_size will help (though it will affect both scenarios equally).

Are each of the nodes on the same SSD though VMs?

In wsrep_provider_options, try raising gcs.fc_limit to enable a larger backlog - 128-1k. gcache.size could be significantly undersized too. Having wsrep_slave_threads larger to the approximate number of threads doing a write could help 50% of sysbench setting? repl.commit_order=1 and evs.send_window >> 4 could also help.

Big caveat about innodb_flush_log_at_trx_commit!=1 - really only save if all your nodes are on independent power cicuits otherwise a power failure will make you loose data.
https://jira.mariadb.org/browse/MDEV-11599
Try looking at the SHOW GLOBAL STATUS as the end of the sysbench run, particularly at the wsrep variables. This will help identify which limits you are hitting. Record for both the sysbench specified node and one of the slave nodes with a FLUSH STATUS before the sysbench run will help.

Generated at Thu Feb 08 07:51:12 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.