I’m trying to benchmark the performance of 3-node pxc cluster. The sysbench result I’m getting on a 3-node pxc is much slower than the sysbench result on a single node community version server. The transaction rate on the pxc is about 1/2 the rate on the single node. I expected the performance of the 3-node cluster to be better than the single node. I suspect that I have configured the cluster and/or the test incorrectly. I’ve tried several different configurations, but still get similar results.
Are there any obvious problems with my setup?
Is there a guide to running sysbench on pxc?
Sysbench Results (tps): [TABLE=“border: 1, cellpadding: 1, width: 500”]
[TR]
[TD]Threads[/TD]
[TD]Community-56[/TD]
[TD]PXC-3node[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]178.8[/TD]
[TD]122.41[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]1323.24[/TD]
[TD]891.09[/TD]
[/TR]
[TR]
[TD]128[/TD]
[TD]2166.06[/TD]
[TD]595.68[/TD]
[/TR]
[TR]
[TD]1024[/TD]
[TD]1427.13[/TD]
[TD]563.8[/TD]
[/TR]
[TR]
[TD]2048[/TD]
[TD]579.6[/TD]
[TD]528.31[/TD]
[/TR]
[TR]
[TD]4096[/TD]
[TD]42.02[/TD]
[TD]304.98[/TD]
[/TR]
[/TABLE]
3 Physical Hosts:
24 core Xenon
256 GB RAM
3T disk
pxc version: 5.6.24-72.2-56
pxc config:
[mysqld]
datadir=/clusterdb/mysql/data
user=mysql
wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.12.65,192.168.12.66,192.168.12.67
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_node_address=192.168.12.65
wsrep_sst_method=xtrabackup-v2
wsrep_cluster_name=my_centos_cluster
wsrep_sst_auth=“xxxx:xxxxx”
log-error=/clusterdb/mysql/var/log/mysqld.log
max_connections = 6000
max_prepared_stmt_count = 65536
innodb_log_file_size = 1500M
community version 5.6.26 config:
[mysqld]
user = mysql
bind-address = 0.0.0.0
port = 50000
socket = /clusterdb/mysql/56/sbtest/var/run/mysql.sock
pid-file = /clusterdb/mysql/56/sbtest/var/run/mysql.pid
datadir = /clusterdb/mysql/56/sbtest/data
tmpdir = /clusterdb/mysql/56/sbtest/tmp
log-bin = /clusterdb/mysql/56/sbtest/var/log/binlog/mysql-bin.log
expire_logs_days = 5
max_binlog_size = 100M
general-log = 0
general_log_file = /clusterdb/mysql/56/sbtest/var/log/mysql.log
log-error = /clusterdb/mysql/56/sbtest/var/log/error.log
slow-query-log = 1
slow_query_log_file = /clusterdb/mysql/56/sbtest/var/log/slow.log
relay-log = /clusterdb/mysql/56/sbtest/var/log/relaylog/mysqld-relay-bin
relay-log-index = /clusterdb/mysql/56/sbtest/var/log/relaylog/mysqld-relay-bin.index
server-id = 1
innodb_fast_shutdown = 0
max_connections = 6000
open_files_limit = 32768
max_prepared_stmt_count = 65536
table_open_cache = 8000
sysbench version: 0.5
sysbench command:
sysbench --test=oltp
–mysql-host=mysql-cluster1,mysql-cluster2,mysql-cluster3
–mysql-user=xxxx --mysql-password=xxxx
–mysql-table-engine=InnoDB
–mysql-engine-trx=yes
–oltp-table-size=200000000
–max-time=60
–max-requests=0
–num-threads=$N
–oltp-auto-inc=off
–oltp-test-mode=complex
–db-driver=mysql
run