Can PXC over WAN be implemented in production ? If so what are the recommendations? Below is my use case can you please advise on how this can be achieved in MySQL database.
I have a requirement for multi-master MySQL database across 3 data centers. Applications will be reading from local dc but writes happen to active data center. PXC across WAN with 7 nodes, 2nodes dc1, 2nodes dc2, 3 nodes in dc3 , having active-passive-passive setup, do you recommend this use case in production?
Considering the latency and packeloss to be very less and writes happening only on one node do you suggest this approach ? Or is there a better solution to implement 3dcs replication where any datacenter able to take over writes when Datacenter failover happens.
I think you already have this in the back of your mind, 7 node PXC over WAN - is a NO. With 7 nodes, it is going to bring a lot of overhead when you think about flow control, sst, certifications, latencies. IMHO you must look for an alternative.
“multi-master MySQL database”: You cannot scale writes with multi master. May be this requirement should be revisited.
“active-passive-passive setup”: Even if the writes are only happening in one node, the rest still participate in the certification process.
How do you think of having a PX cluster in each region but connected with async gtid replication?
pxcdc1_db1 x pxcdc1_db2 x pxcdc1_db3 \___ pxcdc2_db1 x pxcdc2_db2 x garbd \___ pxcdc3_db1 x pxcdc3_db2 x garbd
Also remember with PXC as a solution you have some limitations.
I understand you mentioned to have 3 PXC clusters, each in one DC, first dc to have 3 nodes , second and third dc’s each with 2 nodes and 1 gardb node. But how do I setup the replication between them so that incase of failover of datacenter I will have less or no manual effort for switching over MySQL db.
You mean master-master setup between DC1 (PXC1-cluster) - DC2( PXC2 -cluster) ? And how can have async replication setup to DC3( PXC3-cluster) - either DC3 being slave to DC1 OR DC2 but this does not gurantee the consistency incase the master DC is down. Then I need to manually reconfigure DC3 as slave/master to other DC and sync-up the data.
Hi @shirisha,
How far apart are the datacenters? Are they across the country, or across the city? This can be pro/con for WAN clusters. As @kedarpercona said, each node participates fully within PXC. A commit on node1-DC1 replicates to node2-DC2, node3-DC2, etc etc, and all nodes wait for confirmation. If your DCs are within the city, the lag won’t be too bad and you can do this.
But if across the country, the lag might not be acceptable, depending on how much traffic this application creates. If your app creates 100trx/sec, you might not notice the lag across the country. But if 1000+/sec, you might notice the lag. Is the lag at that rate acceptable to you? If so, then continue on, but if not, rethink the design.
If you create a proof-of-concept, be sure to adjust the gmcast.segment parameter, as this will lessen the amount of data transmitted across the WAN.
My three datacenters are across the Country. I have already configured gmcast.segment.
So I can implement PXC across WAN with 7 nodes in production ? Do you have use cases with such configuration running in production ? Apart from latency and restricting the writes to one primary writer node(Active DC) , can you please let me know if there are any other factors which my cluster will be highly sensitive to across WAN ?
None that I know of. Most active-passive-passive XtraDB Clusters that I see implemented follow a similar style to what @kedarpercona said above, where you have 1 primary PXC, and the other DCs are replicas. Say DC1 is active, and DC2/DC3 are passive PXC replicas. If DC1 goes down, DC2 becomes active. DC3 is reconfigured to be a replica of DC2. This is straightforward using GTIDs. When DC1 comes back online, it also becomes a replica of DC2 to sync the changes. There is nothing automatic here; this setup is very manual intervention.
…Then I need to manually reconfigure DC3 as slave/master to other DC and sync-up the data.
As Matthew said, “Nothing automatic” - having such a wan setup with automatically moving parts will not give you much control. I’d prefer having such configuration over a PXC cluster all across.
I can implement PXC across WAN with 7 nodes in production ?
Can you → yes.
Should you → mostly no.
Do you have use cases with such configuration running in production ?
Yes. We have a customer with PXC → PXC across different cities (Melbourne, Sydney) and latency was their main issue.
Apart from latency and restricting the writes to one primary writer node(Active DC) , can you please let me know if there are any other factors which my cluster will be highly sensitive to across WAN ?
I think both Matthew and Guptaji have provided the inputs for this question. I agree with them.