100x less insert performance with Galera multi-master I am running an application which inserts a lot of rows and we were experiencing some low performance. So i started benchmarking the Galera Cluster with sysbench and oltp_insert.lua (practically inserts with random key).

The performance of the cluster is really bad. 

With the same test: 


As a standalone DB i can perform about 35000 tps 
With Galera multi-master the performance is: 350 tps


The table structure is this one:

       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


What i tried so far (with no success):


increase wsrep_slave_threads=16
no matter how many client threads i use, the TPS is still 350 (looks like the transactions are serialized somehow by the DB)
set wsrep_sync_wait=0 (although i perform no reads)
the db does not use binlog, its not enabled.


Configuration:

[mysqld]   
binlog_format=ROW   
innodb_autoinc_lock_mode=2    
bind-address=0.0.0.0   
datadir=/var/lib/mysql

innodb_buffer_pool_size = 300MB
innodb_large_prefix=1
innodb_file_format=Barracuda

transaction-isolation = READ-COMMITTED

wsrep_on=ON  
wsrep_provider=/usr/lib64/galera/libgalera_smm.so           
wsrep_slave_threads = 16         
wsrep_max_ws_rows=131072    
wsrep_max_ws_size=1073741824    
wsrep_convert_LOCK_to_trx=0    
wsrep_causal_reads=OFF
wsrep_sst_method=xtrabackup-v2
I've hit the same issue. 
The first thing to do is to check the following variables:

select @@innodb_flush_log_at_trx_commit;

select @@sync_binlog;


Setting innodb_flush_log_at_trx_commit to 2 and sync_binlog to 0 will increase the performance dramatically.

The reason is in certification mechanism and the nature of multi master behaviour. Inserts will be very slow because in order to commit the transaction cluster will ensure that all logs are flushed at the time of commit at every node.

Please be sure that your caches fit RAM with the following query as well:

select ((@@thread_stack +@@binlog_cache_size +@@read_buffer_size + @@read_rnd_buffer_size +@@sort_buffer_size +@@join_buffer_size + @@global.net_buffer_length +@@global.query_prealloc_size + @@binlog_stmt_cache_size) * @@max_connections + (@@query_cache_size + @@innodb_buffer_pool_size + @@innodb_log_buffer_size + @@key_buffer_size))/(1024*1024);