Database Architecture: Primary‑Backup, Master‑Slave, Read‑Write Splitting, and Consistency Solutions

1. Database Architecture Principles

Four core goals guide any database design: high availability, high performance, data consistency, and scalability.

2. Common Architecture Schemes

Scheme 1: Primary‑Backup (Only the primary handles read/write, the backup is for failover)

jdbc:mysql://vip:3306/xxdb

High‑availability analysis: If the primary fails, a keepalive tool automatically switches traffic to the backup, transparent to the application.

Performance analysis: All reads and writes go to the primary, creating a bottleneck for read‑heavy workloads; the backup is idle 50% of the time.

Consistency analysis: Since all operations use the primary, there is no data‑consistency issue.

Scalability analysis: Adding replicas does not improve read performance; sharding is required for scaling.

Practical notes: Performance can be improved with better indexing or caching; scalability can be addressed by sharding.

Scheme 2: Dual‑Primary (Two primaries provide service with load balancing)

jdbc:mysql://vip:3306/xxdb

High‑availability analysis: Failure of one primary does not affect the other; the switch is transparent.

Performance analysis: Read/write capacity roughly doubles compared to Scheme 1.

Consistency analysis: Data consistency problems arise and must be solved by the consistency solutions described later.

Scalability analysis: Adding a third primary is possible but adds another data‑synchronization layer, which is not recommended.

Practical notes: Primary‑key conflicts can be avoided using a distributed ID generator.

Scheme 3: Master‑Slave with Read‑Write Separation (One master, multiple slaves)

jdbc:mysql://master-ip:3306/xxdb

jdbc:mysql://slave1-ip:3306/xxdb

jdbc:mysql://slave2-ip:3306/xxdb

High‑availability analysis: The master is a single point of failure; if it goes down, write service stops.

Performance analysis: Reads are offloaded to slaves, improving overall throughput, but adding many slaves increases the master’s binlog‑shipping load and data‑sync latency.

Consistency analysis: Replication lag can cause stale reads; consistency solutions are needed.

Scalability analysis: Adding more slaves improves read capacity but also increases synchronization overhead.

Scheme 4: Dual‑Primary + Master‑Slave (Hybrid, appears ideal)

jdbc:mysql://vip:3306/xxdb

jdbc:mysql://slave1-ip:3306/xxdb

jdbc:mysql://slave2-ip:3306/xxdb

Provides high availability and performance, but still suffers from data‑consistency issues and an extra synchronization layer.

3. Consistency Solutions

Type 1 – Primary‑to‑Slave Consistency

Replication lag creates temporary inconsistency; solutions include:

Ignore the lag if the business tolerates delay.

Force all reads to the primary (primary‑backup scheme) and use caching to boost read performance.

Cache a key that represents the synchronization window; read from the primary if the cache misses, otherwise read from a slave.

Use semi‑synchronous replication to ensure the slave acknowledges writes before the client receives a response.

Introduce a database middleware (e.g., MyCat) to route reads intelligently, at the cost of added complexity.

Type 2 – DB‑to‑Cache Consistency

Typical cache workflow: evict → write DB → read DB → write cache. Inconsistent states arise when a read hits the cache before the DB write is reflected. Solutions involve careful ordering of cache eviction and write‑through strategies, as illustrated in the accompanying diagrams.

4. Personal Insights

Architecture Evolution

Four evolution paths are outlined, showing how schemes can be combined with sharding (horizontal/vertical) to achieve higher scalability.

Key Takeaways

• Adding cache and proper indexing are universal performance boosters. • Sharding brings massive benefits but introduces new challenges. • The choice of architecture must match specific business scenarios; most production systems at 58.com still rely on primary‑backup with optional sharding. • An architecture that ignores business context is ineffective.

For further reading, the original article links to many additional technical resources.