Percona XtraDB Cluster architecture understanding

MySQL & MariaDB Percona XtraDB Cluster 8.x
,
1

Hi all,
i need some advices to understand which kind of deployment i should implement.

This is my scenario:

  • MySQLDB
  • ~50M records on the “main table”
  • three full-text index used by some “match() against()” with some columns from “main table” and some columns from other smaller tables
  • 99% of reading operations (which use full-text index) are made by a dns server.
  • 99% of writing operations are made by a sql-procedure

Right now i have 4 PXC mysql nodes - and 4 standalone dns servers as well - with synchronous replication and multi-source replication. Each server can do writes and reads.
With this configuration each dns server reach ~15k “dns read-query” per second (that is much more than my goal). Each dns server uses its own mysql server.

I would like to re-create from scratch this kind of environment but i want to make some changes:

  • 2 proxysql with keep alive and VIP on top as a connection and distribution layer.
  • some nodes (maybe 3/4/5 VMs) for the RDBMS layer
  • split read and write on different nodes.

Reading the documentation Deployment variants - Percona Distribution for MySQL i cannot understand if i have to use “Percona Server for MySQL” or “Percona XtraDB Cluster”.
If i choose to use “Percona XtraDB Cluster” there are a lots of configuration differences between Percona XtraDB Cluster and "Percona Distribution for " method.
Which of them should i use?
Please give me some advices about that.

Thank you so much

2

Hello @paffeke,
This is a highly complex re-architecture and I highly recommend you engage with us directly. If you already have PXC, then you need to use Percona XtraDB Cluster (PXC). “Percona Distribution for MySQL” is just some fancy wording for putting several packages together. There’s no difference between ‘Percona Distribution for MySQL 8.0.28’ and ‘Percona Server for MySQL 8.0.28’.
It is never recommended to use 4 PXC nodes due to splitbrain issues. Always stick to an odd number. 3 is the best for 98% of PXC’s out there.

3

Thank you for your answer matthewb.
I inherited the actual percona cluster and it is impossible for me to update it (not only percona packages but also OS).
That’s why i want to create a new parallel environment, test it and eventually switch to production.
So, new OS and Percona Packages with a odd number of nodes.

The environment i thought is possible to do with Percona Distribution?

  • 2 proxysql with keep alive and VIP on top as a connection and distribution layer.
  • some nodes (maybe 3/5 VMs) for the RDBMS layer
  • split read and write on different nodes.

Thank you
Daniele

4

Yes, you can do exactly what you have listed above. I would just use the plain Percona XtraDB Cluster packages.

1 Like
5

I will start to create a lab to test it.

Thank you so much for your advices.
Daniele