Choosing the Right MySQL OLTP Architecture: PXC, MHA, or Dual‑Master?

Before replacing an enterprise‑grade database with an open‑source MySQL solution and moving from shared storage to local disks, three operational questions must be answered:

Can temporary data inconsistency be tolerated after a failover (e.g., some writes not yet replicated to the standby)?

Is a 3–30 second service interruption during a database failure acceptable?

Do the workload’s scalability, throughput, latency, and user‑experience requirements demand a high‑performance architecture?

Solution 1 – Percona XtraDB Cluster (PXC)

PXC uses the Galera replication engine to provide synchronous multi‑node replication. All nodes can accept reads and writes while guaranteeing strict data consistency and high availability.

Advantages

True synchronous data replication.

Multiple read/write nodes (requires sharding or separate tables per node).

Strict data consistency across the cluster.

Well‑suited for read‑heavy, write‑light OLTP workloads.

Disadvantages

Does not support XA (two‑phase commit) transactions.

Cluster throughput is limited by the slowest node; transaction latency can be an order of magnitude higher than classic master‑slave setups.

Only the InnoDB storage engine is supported.

Every table must have a primary key; large transactions are discouraged.

Explicit lock statements such as LOCK TABLE are not supported.

High lock and dead‑lock contention; poor scalability for hotspot updates.

For high concurrency, low‑latency networking (e.g., InfiniBand) is recommended.

Integration requires several third‑party plugins, increasing operational complexity.

Solution 2 – Master High Availability (MHA)

MHA (Master High Availability Manager and Tools for MySQL) monitors the master, repairs divergence logs on slaves, and promotes a selected slave to master when the original master fails, preserving data consistency.

Advantages

Automatic detection of master failure and failover.

No performance penalty; works with any storage engine.

Automatic data compensation ensures maximum consistency after a master crash.

Can be used for active‑active (dual‑city) deployments.

Disadvantages

If the master hardware fails or SSH access is lost, the failover may lose in‑flight data.

Failover typically takes 9–12 seconds, which may be too long for latency‑sensitive services.

Solution 3 – Native MySQL Dual‑Master (MM)

MySQL’s built‑in bidirectional replication (often called “master‑master”) eliminates a single point of failure and removes the write bottleneck of a single master.

Advantages

Fast switchover, typically within 3 seconds.

Simple configuration; no third‑party plugins required.

Disadvantages

Hardware failures can cause loss of in‑flight transactions.

Higher risk of data inconsistency compared with MHA because writes are performed on both masters without built‑in conflict resolution.

Consistency Guarantees with MHA

MHA can achieve near‑zero data loss under the following conditions:

No hardware failure: after a master crash, the repaired master can be synchronized and DBA can manually reconcile any missing rows.

Only a MySQL process failure: MHA automatically copies all committed transactions to the standby, resulting in zero data loss.

When MySQL’s semi‑synchronous replication (two‑phase commit) is enabled, MHA’s data‑compensation tools provide consistency comparable to PXC.

Overall Comparison

PXC delivers strong consistency and multi‑node write capability, but its scalability is limited by the slowest node, it suffers from lock contention, and it requires high‑quality low‑latency networking.

MHA offers automatic failover with minimal performance impact and works with any storage engine, yet it cannot guarantee zero data loss during a master hardware failure and its switchover time is longer.

Native MySQL dual‑master (MM) provides the fastest failover and the simplest setup, but it carries a higher risk of data divergence. Combining MM with MHA’s data‑compensation utilities or enabling MySQL semi‑synchronous replication can mitigate inconsistency risks.