MariaDB Galera Across Regions: When Synchronous Multi-Primary Writes Are Worth the Commit Latency, and When Async Wins

Galera Cluster is the MariaDB world's answer to a hard question: how can every node accept writes and every node have the same data? Its answer is synchronous certification of every transaction across the cluster, which works beautifully in one data centre and becomes an arithmetic problem across several. This post follows a database architect doing that arithmetic on MassiveGRID PaaS, which is built on Virtuozzo Application Platform, formerly Jelastic, before choosing between the Galera and asynchronous multi-region packages.

The application is a booking engine for a European hotel group we will call Aurelian Hotels, with properties and staff in the UK, Germany and the Netherlands, and a requirement that a booking made at any property is visible everywhere immediately. The MariaDB Multi-Region Galera Cluster deploys one Galera cluster with nodes in three or more regions, grouped into segments, with ProxySQL entry points per region; the MySQL/MariaDB/Percona Multi-Region Cluster is the asynchronous alternative.

The two packages, and what each promises

Galera multi-region is a single synchronous cluster. Every node is a primary; a transaction is certified against every node's write set before commit returns, so once committed it exists everywhere and nothing is lost if a node or a region disappears, provided a majority remains. Nodes are organised into segments, one per region, so replication traffic between regions flows once per segment rather than once per node. Asynchronous multi-region is a primary-primary topology with secondaries: two regions hold primaries, others hold replicas, replication is asynchronous so a commit returns locally and propagates afterwards. The platform's own documentation gives the decision rule plainly: if the average ping between all regions is under about 20 milliseconds, Galera's synchronous distribution is viable; otherwise the asynchronous topology is the better fit.

The arithmetic

Galera's commit latency is dominated by the round trip to the farthest node, because certification must reach every segment. The architect measures from the platform: London to Frankfurt is around 15 milliseconds round trip; London or Frankfurt to New York is around 70 to 80; to Singapore, 150 to 200. Aurelian's three regions must be at least three for Galera's quorum (a majority of nodes must be reachable to accept writes; with two regions, losing either can lose quorum), and the package requires three or more.

MassiveGRID's regions are New York, London, Frankfurt and Singapore. Aurelian's users are all in Europe, so the honest three-region Galera layout is London, Frankfurt and one more. The architect's choice is two nodes in London, two in Frankfurt and one in New York as the tie-breaking segment: the New York node participates in certification, so every commit pays the New York round trip, about 80 milliseconds. A booking engine commits a handful of rows per booking in one transaction; 80 milliseconds on a commit that a human triggered by clicking Confirm is invisible. A batch job that commits ten thousand rows one at a time is not, and she rewrites the two such jobs to batch their commits before go-live.

Why not just asynchronous, then

Because of the requirement. With asynchronous primary-primary, a booking committed in London is visible in Frankfurt after replication lag, normally under a second, occasionally more; two properties could, in that window, both sell the last room. Galera's certification makes that impossible: the second transaction to certify for the same row is rejected with a deadlock error the application retries, and there is one truth for room availability across all regions at commit time. For a hotel group that is worth 80 milliseconds. For an application that reads far more than it writes, tolerates a second of staleness and spans oceans, the asynchronous package is the right choice, and the architect says so in her design note so nobody cargo-cults the decision later.

Galera's rules, applied before migration

Galera replicates only InnoDB and requires every table to have a primary key; DELETEs on tables without one are unsupported, and rows may order differently across nodes. The platform's Galera guidance includes the query to find offending tables, and the architect runs it against the booking schema on a clone: three legacy log tables without primary keys, converted before anything else. She also notes the cluster's stop and start discipline, that nodes must be shut down sequentially and the last one down is the one to bootstrap from, and that the Database Cluster Recovery add-on exists for when that discipline was not followed.

Entry points and failure

Each region's application connects to its local ProxySQL entry point, which routes to healthy Galera nodes in the same region first, so reads are local and writes are local until certification. If Frankfurt is lost, the London and New York segments still form a majority (three of five nodes) and keep accepting writes; Frankfurt's applications fail over to the London entry point over the inter-region network. If the London segment is lost, Frankfurt and New York have three of five and continue. Losing two regions at once loses quorum, and the remaining nodes stop accepting writes to protect consistency, which is exactly the behaviour a booking system wants over serving stale availability. The nodes that fell out rejoin with incremental or full state transfer automatically when the region returns.

Cost

Five Galera nodes across three regions plus three ProxySQL nodes, scaling vertically by the hour. Aurelian's booking database is a few tens of gigabytes with a moderate write rate; the Galera nodes average around 14 cloudlets each and ProxySQL around 3 (a cloudlet is 128 MiB of RAM plus 400 MHz of CPU), about 80 cloudlets an hour in total, roughly $195 a month before discounts at MassiveGRID's published $0.003372 per cloudlet-hour, plus inter-region replication traffic. The architect's summary: a database that cannot double-sell a room across three countries, for the price of a mid-range laptop a month, and a design note explaining why the New York node is there.

Frequently Asked Questions

Why does the package need at least three regions?

Galera needs a majority of nodes to keep quorum. With nodes in only two regions, losing either region can leave the survivors without a majority and they stop accepting writes. A third region, even with a single node, gives the cluster a tie-breaker so the loss of any one region leaves a majority.

Does the New York node slow down reads in Europe?

No. Reads are served by the local segment through the regional ProxySQL entry point and never wait on remote nodes. Only commits pay the round trip, because certification must reach every segment.

Can I add nodes later or change regions?

Nodes can be added to a segment and the cluster reshapes itself with state transfer. Adding a region is adding a segment. Do it on a clone first to time the state transfer for your data size, and add nodes one at a time so the donor node's load stays acceptable.