Galera WAN Clustering use case, having remote nodes performance impact is not clear

Question

In official doc Galera Use Cases for the 2nd, WAN Clustering use case I am reading the following:

"Synchronous replication works fine over the WAN network. There will be a delay, which is proportional to the network round trip time (RTT), but it only affects the commit operation."

If I understand correctly Galera will replicate the write-set to all nodes. So when the client executes a write transaction on a 'local' node (we must here have an intelligent load balancer, (MaxScale?) but that is an other topic), then the write-set must go through the wire for all nodes.

The node which accessible the worst bandwidth will probably the slowest answer to OK to commit.

It is not clear, why the official states that a WAN node introduces only an RTT proportional lag, when as I see this lag will be indeed bandwidth proportional, and for big write sets will introduce considerable lag. It is also average write-set size proportional. What I am missing?

Question

I do understand that the motivation is HA across data centers. What are the real performance impacts to include WAN nodes into a Galera cluster, even for clients connecting to their local data center?

Answer 1

Yes, a local client will see (and be able to measure) a delay curing the COMMIT statement. It may be about 80ms across the US, or perhaps over 200ms to the far side of the earth.

Something like 50(?)ms is enough for a human to notice that the action is not "instantaneous".

Tips:

• Pack more things into fewer transactions. This will help the entire web page to finish sooner.

• Have more clients doing things independently of each other. This will point out that you can still get a high "queries per second" even while the latency sucks.

Some applications can juggle those two things to achieve a high "throughput"; some cannot. Think of throughput as how many people arrive at a faraway destination -- it's determined by only by how many roads between here and there, not by how long it takes ("latency").

"latency" or "ping time" or "round trip time" is what we are talking about here.

Your Ethernet may claim a "bandwidth" of 1Gb, but in reality, the "throughput" is somewhat less than that. (Analogy: traffic jam.)

"Performance" is either throughput or latency or both. Be sure to specify which aspect of "performance" interests you.

Example. I had a single-threaded app that was doing dozens of writes per second. This was fine when the client and server were in the same data center. One day the database had to be failed over to a server 80ms away. The app became essentially useless.

So, I did several things

• Redesigned some of the dataflow to do fewer writes.
• Used batched INSERTs instead of one-row-at-a time. (When practical, this is big winner, even without network concerns.)
• Re-thought the transactions, making fewer transactions doing more writes before COMMIT.
• Moved as much as I could into a Stored Procedure that would do a hundred or so write in the SP -- on the server. Instead of hundreds of SQL statements, it was one CALL (plus a few to set up things for that SP call.)

Although that anecdote involved traditional Primary-Replica topology, the first three techniques would apply to a WAN based Galera. The SP technique would still be useful if the "near" node is down and your client needs to talk to a "far" node.