Mysql – Percona Xtradb Cluster : How to speed up insert

innodbMySQLperconatuningxtradb-cluster

I recently installed a 3 full master node cluster based on Percona Xtradb (very easy install). But now i need to make some tuning to increase INSERT/UPDATE requests. Actually, i made around 100 insertions every 5 minutes, but also made around 400 update in the same time. All this operation take less than 3 minutes when i was on a single server architecture. And now, with 3 node server, it takes more than 5 minutes …

Is there any tuning i can do to speed up this operations ?
Here is my actual cnf configuration :

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

wsrep_provider=/usr/lib/libgalera_smm.so
wsrep_cluster_address=gcomm://dbnode01,dbnode02,dbnode03

binlog_format=ROW
default_storage_engine=InnoDB
innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2
wsrep_node_address=1.2.3.4
wsrep_cluster_name=my_cluster
wsrep_sst_method=xtrabackup
wsrep_sst_auth="user:password"

Here are the 3-server hard config :

Node#1

CPU: Single Processor Quad Core Xeon 3470 - 2.93Ghz (Lynnfield) - 1 x 8MB cache w/HT
RAM: 8 GB DDR3 Registered 1333
HDD: 500GB SATA II

Node#2

CPU: Single Processor Quad Core Xeon 1270 V2 - 3.50GHz (Ivy Bridge) - 1 x 8MB cache w/HT
RAM: 4 GB DDR3 1333
HDD: 1.00TB SATA II

Node#3

CPU: Intel(R) Xeon(R) CPU E3-1245 V2 @ 3.40GHz (4-Cores)
RAM: 32G
HDD: 2T

UPDATE

Actualy there's around 2.4M records (24 fields each) in the table concerned by the INSERT/UPDATE statements (6 fields indexed).

Best Answer

By its Nature in a Galera Cluster a DML statement is expected to run a bit slower than a DML statement on a normal MySQL node because the response time of the DML statements includes not only the commit time on the local node but also sending the the write-set (ws) to the Group and receiving back the GTID of the ws.

So the total response time is: query time + 2 x round-trip to the group.

A simple insert will run possibly within 1 ms. And the round-trip to the group is possibly about 400 - 600 us. So 5 instead of 3 minutes I am not surprised to see...

Now, what can we improve?

a) Reduce query time: Write performance is influenced by: innodb_buffer_pool_size (big enough to cache all data in memory), innodb_log_file_size (as bigger as better) and innodb_flush_log_at_trx_commit (0 or 2 are faster than 1). With your setting above you have not followed the Codership recommendation (innodb_flush_log_at_trx_commit=0).

b) Reduce number of round trips. When you batch your DML statements into transactions you have bigger but less write-sets which should cause less round trips. So your time spent in the network should become smaller.

c) Make your network faster. Use smallest possible network latency: dedicated network. 1 or 10 Gbit. Not much hardware in between (firewall, switches, routers, etc).

d) Parallelize your inserts?

Regards, Oli

PS: There are still some places free in our Galera Cluster trainings in 2 weeks!

Related Question