Mysql – ProxySQL in PXC and failing all nodes

clusteringmysql-clusterperconapercona-serverproxysql

I'm going to install Percona XtraDB Cluster, and I have two questions about it:

1- Is it the last step? https://www.percona.com/doc/percona-xtradb-cluster/LATEST/verify.html

Or installing ProxySQL is mandatory? I have only 3 nodes and not planning to add an extra node only for ProxySQL! And I think I don't even need it, my round-robin load-balancer is sending the requests to these servers randomly (without health controlling).

So if a node is down it can't even read/write to the DB.
in a nutshell, my application nodes are the same as my DB nodes, and a load-balancer in front of them, So I think I don't need ProxySQL, Am I?

2- What happens if all 3 nodes are down together, I think if this situation happens in a common Group Replication of MySQL, all the cluster collapses and all nodes are being removed from the cluster and we need to reconfigure the cluster manually again, Is the same problem exists in PXC or not?

Best Answer

No, installing ProxySQL is not mandatory
Do you mean all three nodes close down suddenly rather than close down gracefully? What version of Percona XtraDB Cluster are you using? If it is a version 5.6.19 or later, then the nodes will read the last state on startup and will try to restore primary component once all the members again start to see each other. This makes the PXC cluster to automatically recover from being powered down without any manual intervention. This information came from https://www.percona.com/blog/2014/09/01/galera-replication-how-to-recover-a-pxc-cluster/ and you may find other useful blogs around this subject and/or webinars on the Percona site.

Disclosure: I am from Percona

Related Solutions

Mysql – the Best way to set Disaster recovery setup for percona Xtra DB Cluster

In the latest Percona XtraDB cluster version it is possible to make WAN segments so that there is more efficient communication between nodes. If you didn't do that, all nodes would try to replicate to all nodes, which is highly inefficient for Galera. With Galera 3/PXC 5.6, you can establish those so that only one node at a time will replicate to the other segments. This was specifically thought for geographical redundancy.

Before 5.6:

  ---------------
 /     /   \     \
A --- B --- D --- E   (A, B, C Local nodes; D,E Remote nodes; all talk to all)
 \   /     /     /
   C ------------

After 5.6 (Galera 3):

[ A --- B ] --- [ D --- E ]   (no redundant communication between segments)
[  \   /  ]
[    C    ]

In any case, think that the round-trip time will limit the minimum latency for each transaction, which means that using geographical replication will make the cluster much slower.

An alternative option that you can setup is combining Galera and standard replication, like this:

 A --- B ~~~ D --- E (--- : Galera replication; ~~~: MySQL replication )
  \   /
    C

Standard MySQL replication is way more efficient, as it does not wait for the "slaves" to confirm the changes (it does not use group communication). The disadvantage is clear: being asynchronous it does not guarantee that absolutely all changes applied to the primary cluster has been transmitted to the slave node or nodes. If the replications is well managed that should only mean less than 1 second of transactions, but this is certainly a no-go for banking applications or other applications where loss of information is impossible even if the whole datacenter fails. Whoever, that is something that most web applications and services can handle. On the negative aspect, standard replication has some disadvantages, specially if it is not well managed, like potential data drifts or extra latency due to it being single-threaded.

Mysql – Split brain scenario – RAC and PXC

I can see why Oracle RAC can handle split brain and PXC cannot.

What separates them in their architecture and data storage. In what ways ???

Oracle RAC

All RAC Instances deal with only one set of database files
Each RAC Instance has its own Log Buffer and LGWR (Log Writer) process
Writes to any Oracle RAC instance, even on nodes that go down temporarily, can have their log sequence numbers coalesced and serialized

Percona XtraDB Cluster

Each PXC node has its own complete set of data
Writes to one PXC node must be rolled back or committed on all nodes in unison
Writes to PXC nodes that are split in communication will only update/commit to its local copy of the data. Awareness of such writes by other unconnected PXC nodes does not exist when communication has been established again.

According to Percona's Frequently Asked Questions on PXC

Q: How would it handle split brain?

A: It would not handle it. The split brain is hard stop, XtraDB Cluster can’t resolve it. That’s why the minimal recommendation is to have 3 nodes. However there is possibility to allow a node to handle the traffic, option is:
wsrep_provider_options="pc.ignore_sb = yes"

SUMMARY

Oracle RAC is ACID compliant as a Cluster (again, one set of database files)
Percona XtraDB Cluster abides by CAP Theorm instead of ACID compliance
See YouTube video where Vadim Tkachenko (Percona CTO) discusses the split-brain scenario and how it is your responsibility (Video starts from 17:00 mark, listen up to 19:20 or more)