As far I understand Galera does not scale write performance, best case it does not degrade it, compared to one single server, so we use Galera for HA. (read performance may benefit and it can be load balanced across every nodes)

In Galera the write performance will be the performance of the weakest node. In light of that, who wants to got more HA than 3-4 nodes can provide? If one or two nodes fail the write performance will not degrade, even it is possible it increasing... so it seems to over guard HA with more than 4 nodes. (I am aware the WAN use case and multiple datacenters, but that case we have two or mode Galera clusters)

It is not clear for me, if given a particular write transaction, is there any significant load difference on node that actually the client use to write, compared to the load on all other nodes, who also needs to complete synchronously the very same transaction?

I try to decide, that should I have a dedicated node to write (btw it is single point of error, so I should use some infra to react), and only use the others to read, or try to load balance the writes also across all nodes?

How many nodes are optimal? Is it depends on my application read load/write load ratio?

1 Answer 1


performance...more HA than 3-4 nodes can provide?

Huh? I don't see "performance" and "HA" as being equivalent.

If one or two nodes fail the write performance will not degrade,

When 2 nodes fail, a 3-node system will stop allowing writes. A 5-node cluster can withstand 2 simultaneous failures. (For 4 nodes, it depends on the weights given to the nodes.)

Also, all activity suffers some degradation while a node is coming back up, especially when SST is required.

have two or more Galera clusters

But how do you replicate between the Clusters? Async gives you some scaling, but not necessarily more HA.

given a particular write transaction, is there any significant load difference on a node that the client actually uses to write, compared to the load on all other nodes,

It depends. On the originating node, there may be a lot of extra effort to, say, locate the rows to Update or Delete. The other nodes will be told exactly which rows to change, if any.

should I have a dedicated node to write

Some people do such. With a Proxy in front of the nodes, you can 'failover' writes to a different node rather fast. So, I don't call it a "single point of error".

Read scaling can be achieved with arbitrarily many read-only nodes asynchronously replicating off of various cluster nodes.

How many nodes are optimal?

Probably 3. More than 5 can be a problem due to all the network connections that need to exist.

Is it depends on my application's read load/write load ratio?

Not much depends on that ratio.

If HA is the main goal, you want to use 3 different data centers to avoid natural disasters that could wipe out (or knock offline) a whole data center.

If scaling writes is a big problem, please describe the application. There are techniques that may help significantly.

  • many thx for your detailed answer. Indeed HA and performance is two different things, exactly that I why I exposed my question, maybe my phrasing was not clear. My thought was, that if adding extra nodes does not scale write by design, what would be the motivation to use more than 3. You answered it, that 5 more HA as 3 which is true. However not this is the most important point in your answer and my OP, instead, how Galera performance affected in the single node write vs. multi node write... Jan 27 at 7:41
  • ...and I had the feeling that the node which receives the actual write from the client (or the load balancer, proxy etc) get considerable more load, than the others who "just" applying the write-set, and your answer you mentioned this. However here is the faxt, that MaxScale's monitor for Galera by default and its readwritesplit router will exactly do that, always write to one single node... and we must think that is intentional. I got an answer that is for preventing inter node deadlocks, still the ambiguity this question of best practice is disturbing. Jan 27 at 7:45
  • @g.pickardou - There are two stages of deadlock detection -- The first is on the node being written to; the second is on all the other nodes, when they say either "Yes, this write will succeed" or "No, there will be a conflict [that went unnoticed on the original node]". Hence, I don't see a compelling reason to use a single writer.
    – Rick James
    Jan 27 at 7:51
  • many thanks for taking time to thinking about this. Here is the related answer dba.stackexchange.com/a/322426/222764. Regardless it is correct or not, the MaxScale behaviour, that it picks a node, and exclusively writes that single node is disturbing, know knowing it is the best practice or not... Anyway, a POC on representative load will hopefully answer the single node write vs multi node write question. Jan 27 at 8:04
  • @g.pickardou - OK. I did not know that about MaxScale. Distance from each client to chosen node may be important. If the nodes are geographically dispersed and the are clients 'near' each node, then MaxScale's choice may be counterproductive.
    – Rick James
    Jan 27 at 16:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.