Percona Xtradb Cluster : How to speed up insert?
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!