hi,
any doc about the suggested topology design on XtraDB cluster 8.0.19 ?
it has to include what is the best global variable suggest those structure and design.
like why 2 x nodes is not better than 3x nodes for percona xtraDB cluster.
in the Percona XtraDB Cluster Documentation Release 8.0.19-10 it said:
“A cluster consists of nodes, where each node contains the same set of data synchronized accross nodes. The recommendedconfiguration is to have at least 3 nodes, but you can have 2 nodes as well”
why 2 nodes is ok ? what about 2x nodes on primary site and 2x nodes on DR site? will it be possible to failover ? node majority usually recommend 3 nodes to avoid split brain by using odd number of nodes.
in MariaDB Galera cluster, they do not allow even number like us, 2 x in primary and other 2x in DR site ! e.g. once primary site down, DR site down too to present insyn between both site (split brain).
please show me any doc with diagram which say 3 nodes is better than 2. and usually how many node suggested in primary site and how many in DR site. tks.
AFAIK, no open source DB platform can do cross site failover, please confirm with me if percona xtraDB cluster is the only one can do this ! the Percona XtraDB Cluster Documentation Release 8.0.19-10 say sth like cross site but I am not sure how it cross site? by 2 x different xtraDB cluster on different site and replication is setup between them?
any answer ? we only have 2 x data centers and I am not sure if 3 x nodes in primary DB and 2 x nodes in DR site is a good design based on nodes majority.
The documentation says you can have a 2-node cluster. It is not recommended to have a 2-node cluster but you can do it. You can also have a 1-node cluster. The recommendation is 3 to avoid split-brain, or single server failure situations.
PXC should have an odd number of members, 3 or 5, to avoid split-brain issues. Your DR setup is up to you. You can have a 3-node PXC in primary DC with another 3-node PXC in your DR, and use async-replication to keep DR in sync. Or you can have just a single MySQL server acting as async replica in DR.
Correct, you would need some external tool to manage failover such as Orchestrator or ProxySQL.
“The documentation says you can have a 2-node cluster. It is not recommended to have a 2-node cluster but you can do it. You can also have a 1-node cluster. The recommendation is 3 to avoid split-brain, or single server failure situations.”
yes I knwe, I will have 3 nodes cluster on primary site.
“You can have a 3-node PXC in primary DC with another 3-node PXC in your DR,”
then it is not an odd number in total , it is 6, right ? how about 3 nodes in primary and 2 x nodes in DR ? can they work together as cluster?
"and use async-replication to keep DR in sync. "
yes, BTW, any document to show me how to set this configuration up ?
“Or you can have just a single MySQL server acting as async replica in DR.”
1 x nodes in DR and 3 x nodes in primary still ok ? it is even number , 4 , in total , is not an odd number anymore!
“Correct, you would need some external tool to manage failover such as Orchestrator or ProxySQL.”
so actually PXC will not failover by itself ?
You need to pay attention to my words.
> You can have a 3-node PXC in primary DC with another 3-node PXC in your DR
That is NOT 6 nodes. That is 2 INDEPENDENT clusters of 3 nodes each using async replication between them.
You can create 1 cluster of 5 nodes between two datacenters (3 in DC1, 2 in DC2) but this is not good practice because commit times will be slow due to synchronous WAN replication.
> yes, BTW, any document to show me how to set this configuration up ?
This is a standard setup. If you can set up async replication, you can set this up. Plenty of google-searches.
> 1 x nodes in DR and 3 x nodes in primary still ok ? it is even number , 4 , in total , is not an odd number anymore!
You need to understand the difference between a plain MySQL node and a cluster node. That is NOT 4 nodes. That is 1 cluster of 3 nodes and 1 async MySQL server. The async server is NOT a node/member of the cluster.
> so actually PXC will not failover by itself
If node 1 goes down, nodes 2 and 3 will remain online and the cluster will be online. BUT, there is nothing to manage your application traffic to reroute connections to the other nodes.
“You can create 1 cluster of 5 nodes between two datacenters (3 in DC1, 2 in DC2) but this is not good practice because commit times will be slow due to synchronous WAN replication.”
so single cluster , replication between nodes can only be sync mode?
so across DC can only have 2 x cluster with diff cluster name and the replication between 2 x cluster can ONLY be async mode?
“If node 1 goes down, nodes 2 and 3 will remain online and the cluster will be online.”
the same cluster , right ?
so the proxysQL is just like maxscale for mariaDB or mysql router for Mysql ?
and there are no failover between nodes at all within the same cluster, right ? can proxySQL helps to router to differnet cluster in DR site , which carry the same data set ?
> so single cluster , replication between nodes can only be sync mode?
Correct. PXC/Galera is synchronous replication only between nodes. PXC–>MySQL as Source/Replica is async.
> so across DC can only have 2 x cluster with diff cluster name and the replication between 2 x cluster can ONLY be async mode?
Correct. With 2 independent/different clusters, you can only do async.
>> “If node 1 goes down, nodes 2 and 3 will remain online and the cluster will be online.”
> the same cluster , right ?
Correct
>so the proxysQL is just like maxscale for mariaDB or mysql router for Mysql ?
ProxySQL is far more powerful and feature-rich
> and there are no failover between nodes at all within the same cluster, right?
Correct
> can proxySQL helps to router to differnet cluster in DR site , which carry the same data set ?
Yes
" PXC–>MySQL as Source/Replica is async.“
PXC and MySQL mostly the same, right ? so not only PXC to -> MySQL but also PXC to PXC ? I don’t understand this.
”> can proxySQL helps to router to differnet cluster in DR site , which carry the same data set ?
Yes＂
So ProxySQL only need to know remote PXC ip address? e.g. 3 x nodes in remote data center, 3 x IP in remote DC and we just need to add to proxySQL ?
> PXC and MySQL mostly the same, right ? so not only PXC to -> MySQL but also PXC to PXC ? I don’t understand this.
PXC is MySQL with additional clustering technology (Galera). Yes, you can replicate using standard asynchronous replication from PXC to MySQL. “MySQL” does not speak Galera. PXC and MySQL both speak MySQL async protocol. Yes, you can replicate using asynchronous replication from PXC A to PXC B.
> So ProxySQL only need to know remote PXC ip address? e.g. 3 x nodes in remote data center, 3 x IP in remote DC and we just need to add to proxySQL ?Yes, you must correctly configure ProxySQL to make failover work correctly.
is there any topology diagram in any URL show out this DR concept of PXC ?
" “MySQL” does not speak Galera. PXC and MySQL both speak MySQL async protocol."
do you mean galera replication only between nodes in the same cluster on the same site, and MySQL async protocol only across site?
so Galera replication can’t across sites anyway?
please help on this too:
https://forums.percona.com/discussion/56033/about-pmm-standalone-installation#latest
hi,
sorry, I reread this :
“You can create 1 cluster of 5 nodes between two datacenters (3 in DC1, 2 in DC2) but this is not good practice because commit times will be slow due to synchronous WAN replication.”
so this still means to me that the SAME PXC can across site? but it just slow as sync across a long distance is not working very well?
What if we have a very good connection between sites so that sync lagging is not noticeable ?
any signal from PXC to tell us the network between 2 x sites is not good and we have to consider to create 2 x clusters and must setup async replication between sites?
> so this still means to me that the SAME PXC can across site?
Yes. You can put PXC nodes anywhere in the world and create a synchronous cluster, but you’ll be limited by the lag/latency for commit times.
> any signal from PXC to tell us the network between 2 x sites is not good
SHOW GLOBAL STATUS LIKE ‘wsrep_evs_repl_latency’ shows you the lag between nodes in your cluster.
> and we have to consider to create 2 x clusters and must setup async replication
That is 100% your decision. If 2s of latency is OK with you, then you don’t need to setup async. However, if 0.5s of latency is too high for you, then you’ll have to setup async. The decision is yours.