# Very very slow insert for a cluster of 3 nodes :(

**URL:** <https://forums.percona.com/t/very-very-slow-insert-for-a-cluster-of-3-nodes/3211>\
**Category:** Percona XtraDB Cluster 5.x\
**Created:** [January 16, 2014, 3:45am UTC](https://forums.percona.com/t/very-very-slow-insert-for-a-cluster-of-3-nodes/3211 "2014-01-16T03:45:08Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![Delard](https://avatars.discourse-cdn.com/v4/letter/d/ec9cab/32.png) [@Delard](https://forums.percona.com/u/Delard)\
**Post date:** [January 16, 2014, 3:45am UTC](https://forums.percona.com/t/very-very-slow-insert-for-a-cluster-of-3-nodes/3211/1 "2014-01-16T03:45:08Z")

</div>

Hi,

We have installed and configurated Percona xtradb cluster 5.5.34 on 3 strong servers.  
First we have tested the performance on one node not ine the cluster. We can execute 22 000 query insert per seconds.  
But in a cluster of 3 nodes the performances are gone down at only 125 inserts per seconds ? (O\_O)  
The tables are with the engine Innodb.  
The replications are synchronised and work very well.  
Can you help us please ?

Hardware (three same nodes):  
CPU: Intel(R) Xeon(R) CPU E5-1650 v2 @ 3.50GHz (12 threads)  
RAM: 128 Go  
SSD: 512 Go  
LAN: 1Gbit/s

my.cnf (part innodb):  
innodb\_buffer\_pool\_size=64G  
innodb\_buffer\_pool\_instances=4  
innodb\_additional\_mem\_pool\_size=20M  
innodb\_log\_buffer\_size=16M  
innodb\_flush\_log\_at\_trx\_commit=0  
innodb\_support\_xa=0  
innodb\_file\_per\_table=1  
innodb\_data\_file\_path=ibdata1:100M:autoextend  
innodb\_read\_io\_threads=64  
innodb\_write\_io\_threads=64  
innodb\_io\_capacity=5000  
innodb\_log\_file\_size=512M  
innodb\_thread\_concurrency=0  
innodb\_file\_format=Barracuda  
innodb\_flush\_method=O\_DIRECT  
innodb\_autoinc\_lock\_mode=2  
innodb\_locks\_unsafe\_for\_binlog=1  
innodb\_stats\_on\_metadata=0

=== GALERA ===  
wsrep\_cluster\_name=“db\_cluster”  
wsrep\_slave\_threads=12  
wsrep\_certify\_nonPK=1  
wsrep\_max\_ws\_rows=131072  
wsrep\_max\_ws\_size=1073741824  
wsrep\_debug=0  
wsrep\_convert\_LOCK\_to\_trx=0  
wsrep\_retry\_autocommit=1  
wsrep\_auto\_increment\_control=1  
wsrep\_replicate\_myisam=1  
wsrep\_drupal\_282555\_workaround=0  
wsrep\_causal\_reads=0  
wsrep\_sst\_method=rsync

server-id=1  
wsrep\_node\_address=XXX.XXX.XXX.XXX  
#wsrep\_cluster\_address=“gcomm://”  
wsrep\_cluster\_address=“gcomm://XXX.XXX.XXX.XXX,YYY.YYY.YYY.YYY”  
wsrep\_provider\_options=“socket.checksum = 1; pc.weight=0; gcache.size=8G; evs.keepalive\_period=PT3S; evs.inactive\_check\_period=PT10S; evs.suspect\_timeout=PT30S; evs.inactive\_timeout=PT1M; evs.consensus\_timeout=PT1M; evs.send\_window=1024; evs.user\_send\_window=512;”

---

<div class="post-metadata">

**Author:** ![percona.jayj](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/percona.jayj/32/927_2.png) [@percona.jayj](https://forums.percona.com/u/percona.jayj)\
**Post date:** [January 16, 2014, 7:21am UTC](https://forums.percona.com/t/very-very-slow-insert-for-a-cluster-of-3-nodes/3211/2 "2014-01-16T07:21:02Z")

</div>

- Are these three nodes on the same LAN or spread out across a WAN?
- Inserts on one node or across all of them?
- Single threaded insert or multiple threads?
- 1 transaction for each insert, or bulk inserts/multi-insert transaction?
- Is flow control kicking in? If so – is it a specific node or from all? (wsrep\_flow\_control\_sent)

There is no question that going from a standalone node to even a “cluster” of 1 will cause a performance hit. However, I’d agree that this performance degradation is too much.

---

<div class="post-metadata">

**Author:** ![Delard](https://avatars.discourse-cdn.com/v4/letter/d/ec9cab/32.png) [@Delard](https://forums.percona.com/u/Delard)\
**Post date:** [January 16, 2014, 7:51am UTC](https://forums.percona.com/t/very-very-slow-insert-for-a-cluster-of-3-nodes/3211/3 "2014-01-16T07:51:25Z")

</div>

thanks for your answer.  
The three nodes are in the same LAN. The inserts was on one node for my test. The script doesn’t cut the connection on mysql server beacause I have one connect, one disconnect and in the middle many queries inserts. So I have one thread for this test and one transaction per insert.  
On each node wsrep\_flow\_control\_sent = 0.  
Do you see anything strange ?

Part of the script (PERL) :  
$dbh = DBI-\>connect(“DBI:mysql:$base:$server”, $login, $password);

for(${$i} = 1; ${$i} \<= $NbInsert; ${$i}++)  
{  
$request = “INSERT INTO `City` VALUES (”.${$i}.“,‘Paris “.${$i}.”’,‘FRA’,‘PARIS’,1000);”;  
$prepdbh = $dbh-\>prepare($request) or die $dbh-\>errstr;  
$prepdbh-\>execute() or die “Echec requete\n”;  
}

${$i} = ${$i} - 1;

$dbh-\>disconnect;

---

<div class="post-metadata">

**Author:** ![percona.jayj](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/percona.jayj/32/927_2.png) [@percona.jayj](https://forums.percona.com/u/percona.jayj)\
**Post date:** [January 17, 2014, 8:07am UTC](https://forums.percona.com/t/very-very-slow-insert-for-a-cluster-of-3-nodes/3211/4 "2014-01-17T08:07:33Z")

</div>

When you tested the single server – did you have wsrep\_provider enabled, or was it completely Galera-free? If the later, you may test a 1-node cluster to see if the behavior is introduced with Galera or only when you add the extra nodes.

With a rate of 125 commits per second for a single thread, this suggests 8ms of time for a single commit. The question is if this is caused by just adding wsrep or by adding extra node(s).

The performance hit from just adding wsrep is from adding transaction certification to the commit process.

The performance hit from adding more than 1 node to the cluster is from replication.

Can you test to see where the penalty comes from?

---

<div class="post-metadata">

**Author:** ![Delard](https://avatars.discourse-cdn.com/v4/letter/d/ec9cab/32.png) [@Delard](https://forums.percona.com/u/Delard)\
**Post date:** [January 17, 2014, 8:25am UTC](https://forums.percona.com/t/very-very-slow-insert-for-a-cluster-of-3-nodes/3211/5 "2014-01-17T08:25:52Z")

</div>

Yes when I had tested with one node wsre\_provider was enabled:

mysql\> show variables like “%wsrep\_provider%” \G;  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
Variable\_name: wsrep\_provider  
Value: /usr/lib/libgalera\_smm.so  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 2. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
Variable\_name: wsrep\_provider\_options  
Value: base\_host = 192.168.10.2; base\_port = 4567; cert.log\_conflicts = no; evs.causal\_keepalive\_period = PT3S; evs.consensus\_timeout = PT1M; evs.debug\_log\_mask = 0x1; evs.inactive\_check\_period = PT10S; evs.inactive\_timeout = PT1M; evs.info\_log\_mask = 0; evs.install\_timeout = PT1M; evs.join\_retrans\_period = PT1S; evs.keepalive\_period = PT3S; evs.max\_install\_timeouts = 1; evs.send\_window = 1024; evs.stats\_report\_period = PT1M; evs.suspect\_timeout = PT30S; evs.use\_aggregate = true; evs.user\_send\_window = 512; evs.version = 0; evs.view\_forget\_timeout = PT5M; gcache.dir = /disks/mysql/data/; gcache.keep\_pages\_size = 0; gcache.mem\_size = 0; gcache.name = /disks/mysql/data//galera.cache; gcache.page\_size = 128M; gcache.size = 8G; gcs.fc\_debug = 0; gcs.fc\_factor = 1; gcs.fc\_limit = 16; gcs.fc\_master\_slave = NO; gcs.max\_packet\_size = 64500; gcs.max\_throttle = 0.25; gcs.recv\_q\_hard\_limit = 9223372036854775807; gcs.recv\_q\_soft\_limit = 0.25; gcs.sync\_donor = NO; gmcast.listen\_addr = tcp://0.0.0.0:4567; gmcast.mcast\_addr = ; gmcast.mcast\_ttl = 1; gmcast.peer\_timeout = PT3S; gmcast.segment = 0; gmcast.time\_wait = PT5S; gmcast.version = 0; ist.recv\_addr = 37.187.28.14; pc.checksum = false; pc.ignore\_quorum = false; pc.ignore\_sb = false; pc.linger = PT20S; pc.npvo = false; pc.version = 0; pc.weight = 0; protonet.backend = asio; protonet.version = 0; repl.causal\_read\_timeout = PT30S; repl.commit\_order = 3; repl.key\_format = FLAT8; repl.proto\_max = 5; socket.checksum = 1

For me the problem arrive when we add the 2 other nodes.  
Perhaps we have forgottent an option in the configuration of galera. I think the commit is too slow for a synchronous replication on 3 nodes with this configuration of galera.

---

<div class="post-metadata">

**Author:** ![percona.jayj](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/percona.jayj/32/927_2.png) [@percona.jayj](https://forums.percona.com/u/percona.jayj)\
**Post date:** [January 20, 2014, 9:35am UTC](https://forums.percona.com/t/very-very-slow-insert-for-a-cluster-of-3-nodes/3211/6 "2014-01-20T09:35:25Z")

</div>

What is the ping RTT between each of your nodes? If the link between any of the nodes has a latency if 8ms, then that is your problem.

If the ping RTT is much lower (I would expect \< 1ms on a LAN), then something else is wrong, I agree. Perhaps you should reduce your custom configuration to a bare minimum to see if any of your settings are the culprit.

---

<div class="post-metadata">

**Author:** ![Delard](https://avatars.discourse-cdn.com/v4/letter/d/ec9cab/32.png) [@Delard](https://forums.percona.com/u/Delard)\
**Post date:** [January 20, 2014, 11:34am UTC](https://forums.percona.com/t/very-very-slow-insert-for-a-cluster-of-3-nodes/3211/7 "2014-01-20T11:34:15Z")

</div>

Hi,

I have 0.1 ms between the nodes. I already have changed the configuration in my.cnf many times ☹  
Perhaps the kernel version of the server can degrade the performance ?  
I have the kernel Linux db1 3.2.0-4-amd64 #1 SMP Debian 3.2.51-1 x86\_64 GNU/Linux on Debian 7

---

<div class="post-metadata">

**Author:** ![percona.jayj](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/percona.jayj/32/927_2.png) [@percona.jayj](https://forums.percona.com/u/percona.jayj)\
**Post date:** [January 21, 2014, 7:59am UTC](https://forums.percona.com/t/very-very-slow-insert-for-a-cluster-of-3-nodes/3211/8 "2014-01-21T07:59:19Z")

</div>

Well, something is wrong. Can you time a single transaction? time mysql -e “insert into …” ?

---

<div class="post-metadata">

**Author:** ![Delard](https://avatars.discourse-cdn.com/v4/letter/d/ec9cab/32.png) [@Delard](https://forums.percona.com/u/Delard)\
**Post date:** [January 21, 2014, 8:35am UTC](https://forums.percona.com/t/very-very-slow-insert-for-a-cluster-of-3-nodes/3211/9 "2014-01-21T08:35:31Z")

</div>

The performance is bad: 0.009 \* 120 = 1.08s

# time ./bin/mysql -u root -e “INSERT INTO testperformance.City VALUES (5003,‘Paris 5003’,‘FRA’,‘PARIS’,1000);”

real 0m0.009s  
user 0m0.000s  
sys 0m0.004s

* * *

mysql\> INSERT INTO `testperformance`.`City` VALUES (5004,‘Paris 5004’,‘FRA’,‘PARIS’,1000);  
Query OK, 1 row affected (0.00 sec)

---

<div class="post-metadata">

**Author:** ![Delard](https://avatars.discourse-cdn.com/v4/letter/d/ec9cab/32.png) [@Delard](https://forums.percona.com/u/Delard)\
**Post date:** [January 21, 2014, 10:48am UTC](https://forums.percona.com/t/very-very-slow-insert-for-a-cluster-of-3-nodes/3211/10 "2014-01-21T10:48:30Z")

</div>

I have found why the performance was very poor !!!  
In our LAN sometimes we have 8ms between the nodes.  
It is very strange that 8ms can degrade the performance, is there a solution ?

---

<div class="post-metadata">

**Author:** ![Delard](https://avatars.discourse-cdn.com/v4/letter/d/ec9cab/32.png) [@Delard](https://forums.percona.com/u/Delard)\
**Post date:** [January 21, 2014, 10:51am UTC](https://forums.percona.com/t/very-very-slow-insert-for-a-cluster-of-3-nodes/3211/11 "2014-01-21T10:51:35Z")

</div>

We have the same problem between a client from the web (IP public) and the server in our datacenter (8ms).

---

<div class="post-metadata">

**Author:** ![percona.jayj](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/percona.jayj/32/927_2.png) [@percona.jayj](https://forums.percona.com/u/percona.jayj)\
**Post date:** [January 22, 2014, 7:16am UTC](https://forums.percona.com/t/very-very-slow-insert-for-a-cluster-of-3-nodes/3211/12 "2014-01-22T07:16:36Z")

</div>

That is a network problem them (and not PXC’s problem). I’m glad, at least, we found an explanation. For a solution you’ll need to work with your colo provider/network team/whatever.

---

<div class="post-metadata">

**Author:** ![Delard](https://avatars.discourse-cdn.com/v4/letter/d/ec9cab/32.png) [@Delard](https://forums.percona.com/u/Delard)\
**Post date:** [January 22, 2014, 10:12am UTC](https://forums.percona.com/t/very-very-slow-insert-for-a-cluster-of-3-nodes/3211/13 "2014-01-22T10:12:51Z")

</div>

Yes we are to see this. But it is sad that only 8ms can hugely degrades the performances ☹

Thanks a lot for your exchanges 🙂