Cluster performance really slow compared to just one server

llowry · December 30, 2020, 4:22pm

Using Ubuntu Server 14.04.1 and Percona XtraDB 5.6.20-68.0-56.

When the cluster is running with all three servers and we do a set of inserts (~800,000 of them) we see
a through put of around 150 inserts/second.

If I shut down servers two and three and now run the same transactions we see a through put of 6,000
to 12,000 inserts/second. It jumps around a bit.

It just seems wrong for the cluster to be that much slower. My my.cnf from the first server is below.
All three servers are the same, Intel Xeon E3-1270 @ 3.5Ghz, 32 Gb memory, and a single 256
Samsung SSD, dual gigabit nics bonded in balance-rr mode and all on the same gigabit switch.

Any thoughts are deeply appreciated

Larry

[mysqld]
datadir=/var/lib/mysql/
user=mysql

#Skip reverse DNS lookup of clients
skip-host-cache
skip-name-resolve

wsrep_provider=/usr/lib/libgalera_smm.so
#wsrep_cluster_address=gcomm://
wsrep_cluster_address=gcomm://192.168.90.140,192.168.90.141,192.168.90.142

max_connections=1000
max_connect_errors=100
max_allowed_packet= 32M
max-heap-table-size = 32M
tmp-table-size = 32M
thread_cache_size = 2000
open-files-limit = 65535

binlog_format=ROW
default_storage_engine=InnoDB
innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=0

innodb_buffer_pool_size=25G
innodb_log_file_size=512M

wsrep_node_address=192.168.90.140
wsrep_sst_method=xtrabackup
wsrep_cluster_name=prod
wsrep_sst_auth=user:hidden

tkauppin · December 30, 2020, 4:31pm

have you made some sanity checks about the perfomance of your environment?

test disk read and write speed of all machines ( using dd for example )
test network throughput between all the machines ( iperf for example )

przemek · December 30, 2020, 4:31pm

Have all the nodes identical MySQL configuration? Can you see any network related errors, maybe double check bonding properties?
Also what if you do asynchronous replication from this node that is capable of doing over 6k inserts/s to the two other nodes - do they keep up with replication?

tucj7 · December 30, 2020, 4:31pm

Hi przemek, would you be able to explain how to test these settings? How do I make replication asynchronous? Thanks

przemek · December 30, 2020, 4:31pm

By asynchronous replication I mean standard master-slave setup available for normal MySQL.

evrynet · December 30, 2020, 4:32pm

Is it possible to setup asynchronous replication with percona master-master mysql cluster?

Ovi · December 30, 2020, 4:32pm

By default PXC is asynchronous. To make it fully synchronous, you must enable wsrep_sync_wait.

przemek · December 30, 2020, 4:32pm

I can see there is some fundamental confusion here about how Galera works and what is the difference between traditional asynchronous MySQL replication and Galera replication.
Ovi, this is not entirely true about PXC/Galera being asynchronous by default. The way Galera based replication (so the one existing in PXC) is completely different from the way how MySQL replication (available since very early MySQL days) works.
Galera is synchronous in a meaning that each transaction must reach all the nodes on commit, and only applying those transactions against real data is later asynchronous. In the same time, cluster won’t allow any node to stay too much behind in applying transactions, and will pause if there is a need. This is also called “virtually synchronous”. Please read the first chapter here:
http://galeracluster.com/documentation-webpages/

Also this is true that enabling wsrep_sync_wait forces kind of full synchronous mode, however this makes sense in only very special use cases.

Now the standard MySQL replication, regardless what topology you will choose, so whether it will be master->slave or master<->master, is completely asynchronous and also does not have mechanism to take care about what the slaves are doing (with small exception of semi-sync mode). In standard replication, the slaves are allowed to be lagged in replicating from master and the master does not really care about that.

In PXC, you can mix both Galera and traditional replication, so PXC node can be a slave to normal MySQL instance or to node in another PXC cluster. PXC node can also be master to async slaves. See some examples here:
http://www.percona.com/blog/2013/06/21/changing-an-async-slave-of-a-pxc-cluster-to-a-new-master/