Search code examples
mysqlgalera

Galera: expected use is multi-master or write one and read all?

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

Solution

  • 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.