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
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.
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”;
}
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.
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.
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.
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
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 ?
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.
