1

I'm facing for the first time to a real scaling problem with a db-heavy webapp.

We are studying Galera and other cluster systems.

I ask you what is the ideal way to use Galera.

I ipothize having 3 nodes

  1. write on only one node and read from other two
  2. write and read from all of 3
  3. write in roundrobin using a balancer

NOTE: this is not a opinion-related question. I am here to ask you to clarify what the goal of Galera, what is the intended, suggested, use

NOTE 2: we need scaling on reading operations than on writing

Community
  • 1
  • 1
realtebo
  • 23,922
  • 37
  • 112
  • 189

2 Answers2

2

With Galera you will get optimal performance in mode 1.

If you write to all nodes in a write-heavy environment, you will experience a large rise in deadlocks.

You should also be aware that transaction commit latency on Galera is substantially higher than with a single node or with asynchronous replication because the commit will not return until a quorum of nodes acknowledges it. That means that performance you will get from Galera will typically be worse on writes.

Galera's main use case is in environments where you cannot afford to ever lose a transaction to a server failure, e.g. payment processing or other heavily regulated environments. The other use case is if you have an application that is written in a way that makes it very sensitive to race conditions due to replication lag. If your intended usage doesn't fall into either of those two categories, it is unlikely that Galera is the best solution for you.

Gordan Bobić
  • 1,748
  • 13
  • 16
1

The goal of Galera is to allow all 3 flavors.

The preference depends on your application and perhaps how spread out your servers are.

There are applications that are so wedded to Master-Slave that No. 1 may be preferred.

No. 2 is perhaps the original design.

No. 3 is a variant on No. 2, by adding a balancer on. (I agree with "round-robin" as the preferred method.)

For HA, the 3 (or more) nodes should be geographically scattered. But that leads to necessary delays that may complicate things.

Are you looking for write scaling? If so, please elaborate. Galera will give you some write scaling, but not a lot. Read scaling is available from most replication schemes.

For further analysis of the VARIABLES and slowlog, see http://mysql.rjweb.org/doc.php/mysql_analysis -- That will help with or without Galera.

Rick James
  • 135,179
  • 13
  • 127
  • 222