I'm running a insert lines of application, we encountered some low performance. So I started to use sysbench and oltp_insert lua to benchmark Galera cluster (actually use random key insert).
The performance of the cluster is very bad.
In the same test:
As a separate database, I can perform about 35000 tps> Using Galera multi-master, performance is: 350 TPS
Table structure is as follows:

Table: sbtest1
The 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 `),
The KEY ` k_1 ` (` k `)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

What I tried so far (without success) :
Increase wsrep_slave_threads=16 & gt; No matter how much I use a client thread, is still 350 TPS (transaction seems to be serialized in some way by the DB) & gt; Set wsrep_sync_wait=0 (although I didn't read) & gt; Db does not use the binlog, it is not enabled.
Configuration:

[mysqld]
Binlog_format=ROW
Innodb_autoinc_lock_mode=2
The bind - address=0.0.0.0
Datadir=/var/lib/mysql

Innodb_buffer_pool_size=300 MB
Innodb_large_prefix=1
Innodb_file_format=Barracuda

The transaction - the 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

CodePudding user response:

I met the same problem. The first thing to check the following variables:

Select @ @ innodb_flush_log_at_trx_commit;

Select @ @ sync_binlog;

Set innodb_flush_log_at_trx_commit to 2 and sync_binlog is set to 0 will significantly improve performance.
The reason is that the essence of the authentication mechanism and multiple host behavior. Insert will be very slow, because in order to commit the transaction cluster will ensure that each node in the submitted refresh all log.
Please make sure your cache for RAM and the following query:

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

CodePudding user response:

I use oltp_insert test TPS is only about 1/20 of the single machine, the performance is too off