How PhxSQL Achieves MySQL‑Compatible High Availability with Paxos‑Based Replication

PhxSQL is a MySQL‑compatible, high‑availability relational database cluster. It deploys a single‑master multi‑slave topology and can automatically switch masters while guaranteeing data consistency as long as a majority of nodes are alive.

PhxSQL is built on Percona 5.6, so MySQL terminology applies.

MySQL’s semi‑synchronous replication has consistency flaws during master switches, leading to rollback issues for the old master and slaves that have received binlogs.

PhxSQL Architecture

The system consists of three layers:

Phxbinlogsvr – handles binlog replication and master management.

Phxsqlproxy – transparently forwards client requests to the current master.

Phxsync – a MySQL plugin that mediates communication between MySQL and Phxbinlogsvr.

A node running Phxsqlproxy, MySQL, and Phxbinlogsvr is called a PhxSQL Node.

Replication Process

In PhxSQL, Phxbinlogsvr stores MySQL binlogs and uses the Paxos protocol for replication, forming a reliable log store. MySQL masters no longer send binlogs directly to slaves; instead, the Phxsync plugin forwards them to the Phxbinlogsvr cluster. Slaves retrieve binlogs from their local Phxbinlogsvr.

Master Management

PhxSQL elects a master via Paxos voting, grants it a lease, and periodically renews the lease. Only one master exists globally, and expired masters are rejected.

Phxbinlogsvr on each slave checks if the current master lease has expired.

If expired, it verifies that its local MySQL has applied all binlogs.

It then initiates a Paxos vote to elect a new master; on success, the MySQL instance becomes master and read‑only mode is disabled.

The old master detects loss of master status, switches to slave role, pulls binlogs from its Phxbinlogsvr, and re‑enables read‑only.

Phxsqlproxy Request Forwarding

Phxsqlproxy forwards client requests from a slave node to the master’s proxy, ensuring that only one MySQL instance handles reads and writes, thus eliminating phantom reads caused by stale client master information.

Performance Evaluation

Using sysbench, PhxSQL was compared with MySQL semi‑synchronous replication under various workloads (100% write, read‑only, and mixed 20% write). The tests ran on a 24‑core Xeon server with 32 GB RAM and SSD RAID10.

Read performance: PhxSQL is slightly lower than native MySQL (≈3‑13% QPS reduction).

Write performance: PhxSQL outperforms MySQL semi‑sync by 16‑25% QPS increase and 10‑20% lower latency.

Conclusion

PhxSQL resolves the rollback and multi‑master issues of MySQL semi‑synchronous replication, enabling automatic master failover while maintaining strong consistency. Although the added Phxsqlproxy slightly reduces read throughput, the Paxos‑based log store delivers superior write performance compared to MySQL’s semi‑sync mode.