Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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).

share|improve this question
    
How big your inserts and updates are? Your numbers do not sound particularly high (understatement!) without knowing their nature more closely. –  dezso Sep 9 '13 at 13:49
    
Update and insert are not so big, but it took time to execute. –  Spin0us Sep 9 '13 at 15:05

2 Answers 2

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!

share|improve this answer
    
I made some tuning on all nodes corresponding to their hardware config. It's much faster now. The only anoying thing is the latency during a node start. –  Spin0us Sep 13 '13 at 16:02

I notice that all your nodes have different hardware.

On an cluster it is recommended that you have all nodes of equal power.

Taken from Percona Docs, Limitations page.

The write throughput of the whole cluster is limited by weakest node. If one node becomes slow, whole cluster is slow. If you have requirements for stable high performance, then it should be supported by corresponding hardware.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.