7

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
mhstnsc
  • 173
  • 1
  • 6

3 Answers3

4

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);
Ilshat Karazbayev
  • 121
  • 4
3

While Galera can work correctly in multi-master use, it still means that the "certification" at commit must check all conflicts. If you insert records into the same table at multiple nodes, such conflicts are very likely. All victim transactions will be rolled back, and the application has to repeat them from the very beginning.

For a single application, multi-master operation is nearly always the wrong setup. Do it in single-master, let all clients access the same database server node.

Even then, the certification process will make a Galera setup a bit slower than a standalone node, but it won't be a factor of 100.

Jörg Brühe
  • 31
  • 2
1

Galera is more designed around a more equal read/write ration to the cluster so more read/write tests would be better. Your sysbench is also probably only hitting one node of the cluster. Maybe design a test closer to your expected usage.

For a bulk insert/change, Galera has a low flow control limit that could be increased. SHOW GLOBAL STATUS LIKE 'wsrep%' will show if you are hitting this or another limit.

Available options for wsrep_provider_options as per link.

Other options to consider:

  • gcs.fc_master_slave
  • gcs.recv_q_hard_limit
danblack
  • 8,258
  • 2
  • 12
  • 28