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 :




Here are the 3-server hard config :


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


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


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


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

  • 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

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!

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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