Percona server or XtraDB?

MySQL & MariaDB Percona XtraDB Cluster 8.x
1

Hello

We are looking into migrating our infrastructure including Aurora Clusters and RDS instances from AWS to on-prem,
Before we went from on-prem to AWS we had percona clusters running, with simple primary-secondary setup, so it makes total sense to look this way again.

One of the options would be Perconas Server or XtraDB, and im kinda split on what we should continue with.

  • Data consistency is of most importance, and I do not really see us from benefitting from balancing writes between nodes using HAProxy or SQLProxy
  • The Current DC is dual site, and we would need the ability to change between DCs seamless, and we would also need to be able to switch between primary nodes on each DB without interruption.
  • Using multi-master-replication, is not an must-have, but nice-to-have. But I see an issue with us only having 2 DCs and not 3, with the potential for causing split-brain. (Can be somehow worked-around with an abritor)

As said we “only” have 2DCs and im a bit worried about the latency that XtraDB provides using multi-master-replication, especially for the 2nd DC if we have issues with the uplink. And that its an major tech-stack change.
But with XtraDB it would make sense to have an VIP in each DB, which makes failover/migration for our application between DC easier and more smooth, since we can “hardcode” the VIP for our application depending on which DC it runs at.

Secondly im currenlty looking at the best options for implemententing the HA. I know it depends on which replication-method we are using.
Our application currently only support the use of one IP address. Rewriting the application will not be possible within time.

What would best practices be for assigning/aligning an VIP? We would need to kinda, maybe at least once per month to switch the primary for maintenance wihtout any interruptions at all.

  • Keepalived (Server/XtraDB) with VIP would be an option, but im not sure if this option is outdated? I know this options works though with “plain” galera.
  • Pacemaker (Server)
  • Using HAProxy or SQLProxy (XtraDB)? It lean towards HAProxy, since we already have this for other purposes, and we currently do not need the layer7 options SQLProxy provides.
  • Creating VIPs on an network level, e.g. firewall, and somehow add the primary SQL server(s) here? This would likely only work if we use multi-master-replication, where all servers are master. Im not sure if we would need e.g HAProxy here as well ?. firewawall(vip)->haproxies->MySQL-nodes(s)

Thanks

2

Hi

This topic leans more toward architecture and design considerations. For in-depth guidance on such projects or proof of concepts, we recommend exploring our consulting services, where our experts can provide tailored advice and support.

Discussions of this nature can be complex and are often difficult to initiate and conclude effectively within a community forum platform.

3

Nobody does this. We would never recommend it. This is not the default behavior of HAProxy, nor of ProxySQL.

Configure two independent 3-node PXC clusters. Have them replicate async to each other, forming a Source/Source topology. Use DNS and your app to pick whichever is the current “active side.”

You would use ProxySQL for that to change the current writer.

This is no different than using traditional async replication between the two DCs. For clarification, you would not configure 1 PXC with 6 nodes between two DCs. That could be laggy.

DNS. Don’t use hardcoded IPs. Use something that can change without needing to notify the application. Additionally, this DNS entry would resolve to ProxySQL, which handles the actual HA. If current writer goes away, ProxySQL switches writes to the next member. Application has no knowledge this occurs.

Tell me you are an old sysadmin without telling me you are an old sysadmin :wink: These are outdated methods that nobody really uses anymore.

PXC+ProxySQL is the ultimate HA solution for MySQL.

4

Yes thanks, we might look at consultancy for the implementation at a later time.

5

Thanks for the very detailed and clear answer. This is great. This is just what im looking for.

Unfortunately with + ~15 standalone DBs, it increases the number of DB nodes extremely. It means we would have an minimum of 90 database instances across 2 sites. Im not sure this is doable at all (Storage and hypervisor wise).

But for the ProxySQL part how should this be done ? Im am all new to ProxySQL.
I read that the recommended way is to have proxysql running on each application server, and im not sure im a big fan of this.

  1. We have the servers kinda tight and hardened, and it would require is to again to further changes to the application, which we at this time cannot do.
  2. Im not sure this would work well with docker containers and kubernetes ?

Would it be possible to do something like, where we have an DNS entry that points to the VIP?
ProxySQL(VIP) → PCX ?

What about ProxySQL cluster ?

And yes, you are correct, old-time sysadmin :smiley: