A single database server is the last single point of failure most small SaaS companies remove, because replication has a reputation for being fiddly and for failing in interesting ways. The DevOps engineer at a booking platform we will call Tidewater Reservations had that reputation in mind when the company decided its one MariaDB node was no longer acceptable. This post is his comparison of the three topologies the MySQL/MariaDB/Percona Cluster package on MassiveGRID PaaS, built on Virtuozzo Application Platform (formerly Jelastic), offers as a dropdown, and how the workload made the decision.
The workload, and the two questions that matter
Tidewater's database serves a booking application: heavy on reads (availability searches), moderate on writes (bookings, cancellations, payments), with a strict requirement that a confirmed booking is never lost and never double-sold. Traffic is European business hours with a Monday spike. The engineer reduced the choice to two questions: what happens when a node dies, and what happens when two nodes are asked to write the same row at the same time. Every topology answers both differently.
What the package sets up for every topology
Before comparing, it helps to know what is the same. The cluster package installs the chosen engine (MariaDB, MySQL or Percona Server) on the number of nodes you request, configures the replication scheme you pick, and places two ProxySQL nodes in front as the entry point, reachable at proxy.<environment>.<platform domain>. ProxySQL splits reads from writes, monitors every backend and excludes nodes that are unavailable or slow, and re-adds them when they recover. New database nodes added by horizontal scaling are discovered and joined automatically: for the asynchronous topologies the platform clones an existing secondary and lets it catch up by replaying the binary log, which keeps scale-out fast and the binlog from ever expiring mid-copy.
The platform's Smart Auto-Configuration also tunes InnoDB's buffer pool and related settings to each node's cloudlet allocation, so a node that grows vertically under load gets a correspondingly larger buffer pool without a hand edit. The engineer therefore compared topologies, not installation effort, which was one form in each case.
Primary-secondary: the read-scaling default
How it works. One primary accepts writes; one or more secondaries replicate asynchronously and serve reads. ProxySQL sends writes to the primary and spreads reads across the secondaries.
When a node dies. Losing a secondary is invisible; ProxySQL routes reads elsewhere. Losing the primary stops writes until a secondary is promoted. The package does not promote automatically in this scheme, so failover is an operator action, quick but manual.
Concurrent writes. Not a concern: only one node writes.
Verdict for Tidewater. Excellent for the availability-search read load and the simplest to reason about, but a manual primary failover during a Monday morning booking rush was exactly the scenario the company wanted to eliminate.
Primary-primary: two writers, asynchronous
How it works. Two primaries replicate to each other asynchronously; extra nodes added by scaling become secondaries distributed between them. ProxySQL can send writes to either primary, so write load is balanced and recovery from a primary failure is simpler because the other primary is already live.
When a node dies. Writes continue on the surviving primary with no promotion step. This is the scheme's main advantage over primary-secondary.
Concurrent writes. This is the catch. Replication is asynchronous, so two primaries can each accept a write to the same row before either has seen the other's. For most rows this never happens; for a seat-availability counter it is the double-sale the company fears. The platform staggers auto-increment offsets so key collisions do not occur, but application-level conflicts on the same row are inherent to asynchronous multi-writer setups.
Verdict for Tidewater. Fine for many applications; for a booking system the engineer would have had to pin all booking writes to one primary through ProxySQL rules, which is primary-secondary with extra steps.
Galera: synchronous, multi-primary, opinionated
How it works. Three or more MariaDB nodes replicate synchronously: a transaction's write set is certified on every node before the commit returns, so all nodes hold identical data at all times and any node can accept reads and writes.
When a node dies. The remaining nodes continue with no promotion and no lost committed transactions. ProxySQL simply stops routing to the failed node. A rejoining node performs an incremental or full state transfer automatically.
Concurrent writes. Handled by certification: if two nodes try to commit conflicting writes, one commit fails with a deadlock error and the application retries. A double-sale cannot be committed. The cost is that every commit pays the round trip to the other nodes, which within one MassiveGRID region is small.
The rules. Every table needs a primary key and must use InnoDB; DELETEs on tables without a primary key are unsupported. Tidewater's schema already complied, which the engineer verified with a query against information_schema before choosing. Cluster shutdowns must be sequential so the last node bootstraps the next start, and the platform automates that when you stop or restart the environment from the dashboard.
Verdict for Tidewater. Galera answered both questions the way the business needed: a dead node costs nothing, and a conflicting write is refused rather than silently applied.
The decision table
| Primary-secondary | Primary-primary | Galera / XtraDB | |
|---|---|---|---|
| Replication | Asynchronous | Asynchronous | Synchronous |
| Primary failure | Manual promotion | Other primary continues | Cluster continues |
| Lost writes on failover | Possible (unreplicated tail) | Possible | None committed |
| Same-row concurrent writes | N/A (one writer) | Conflict risk | Certified; one commit fails and retries |
| Write latency | Lowest | Lowest | Plus intra-cluster round trip |
| Read scaling | Add secondaries | Add secondaries | Every node serves reads |
| Schema constraints | None | None | InnoDB and primary keys required |
| Best for | Read-heavy, tolerant of brief write outage | Balanced writes, low conflict | Transactional systems that cannot lose or double-apply writes |
What he added after choosing
Two add-ons turned the cluster into something the engineer was comfortable being paged for. The Database Cluster Recovery add-on runs a diagnostic of replication state, Galera cluster size and status, and service health, and can repair the common failures (a node that fell out, a cluster that lost quorum after a network blip) from a dashboard button, choosing the node with the highest transaction sequence as the source of truth. The Database Backup/Restore add-on takes scheduled dumps to a separate storage node, because replication protects against hardware failure and not against a bad migration. The SSL/TLS add-on encrypted connections from the application servers, a requirement of the company's payment processor.
Three months in, a hardware fault took one Galera node offline at 10:14 on a Monday. ProxySQL excluded it, the booking application logged nothing unusual, the platform live-migrated the container to another host and the node rejoined with an incremental state transfer. The engineer read about it in the load alert email at 10:20. That email is the whole argument for choosing a topology by asking what happens when a node dies.
Frequently Asked Questions
Can I change topology after installation?
Not in place; the auto-clustering scheme is fixed when the cluster is created. The practical path is to install a new cluster with the desired scheme, replicate or dump-and-restore the data into it, and switch the application's connection string to the new ProxySQL entry point. Cloning the environment first gives a safe place to rehearse.
How many nodes does Galera need?
Three is the working minimum, so that a majority remains if one node fails and the cluster keeps quorum. The package places nodes on different physical hosts automatically. Adding nodes later is a horizontal scaling operation and the new node joins with an automatic state transfer.
Does my application need to change for ProxySQL?
Usually only the host name. Point the application at the ProxySQL entry point using the credentials from the installation email. ProxySQL splits reads and writes by inspecting statements, so no code changes are needed. If you create additional database users afterwards, add them to ProxySQL's user table as well so the proxy can authenticate them.
Pick the topology from a dropdown
The MySQL/MariaDB/Percona Cluster package on MassiveGRID PaaS deploys primary-secondary, primary-primary, Galera or XtraDB clusters with two ProxySQL nodes in front, in one click and billed per cloudlet-hour. Free 14-day trial, no credit card.
MySQL Cluster on PaaS