Which DB Architecture Wins? High Availability, Performance & Consistency Explained

1. Database Architecture Principles

High Availability

High Performance

Consistency

Scalability

2. Common Architecture Solutions

Scheme 1: Primary‑Standby (Only the primary handles read/write; standby is for failover)

jdbc:mysql://vip:3306/xxdb

High Availability Analysis: If the primary fails, a keepalive tool automatically switches to the standby, transparent to the business layer.

High Performance Analysis: All reads and writes go to the primary, creating a bottleneck; read traffic often becomes the limiting factor.

Consistency Analysis: All operations on the primary avoid data inconsistency.

Scalability Analysis: Adding replicas does not improve read performance.

Practical Considerations: Performance can be improved with efficient indexes and caching; scalability issues can be addressed by sharding (分库分表).

Scheme 2: Dual‑Primary (Both primaries provide services with load balancing)

jdbc:mysql://vip:3306/xxdb

High Availability Analysis: If one primary fails, the other continues serving.

High Performance Analysis: Read/write performance roughly doubles compared to Scheme 1.

Consistency Analysis: Data consistency issues arise; see the consistency solutions section.

Scalability Analysis: Can be extended to three primaries, but adds extra data synchronization overhead.

Practical Considerations: Data consistency problems can be mitigated with sharding and distributed ID generation.

Scheme 3: Primary‑Replica (One primary, multiple replicas, read/write separation)

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

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

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

High Availability Analysis: Primary is a single point of failure; replicas are highly available.

High Performance Analysis: Reads are offloaded to replicas, improving overall performance; different indexes can be applied to online and offline replicas.

Consistency Analysis: Data consistency issues exist; see the consistency solutions section.

Scalability Analysis: Adding more replicas improves read capacity but increases the load on the primary for binlog replication.

Practical Considerations: Consistency problems can be addressed with the solutions described later; primary single‑point failure remains a concern.

Scheme 4: Dual‑Primary + Primary‑Replica (Hybrid design)

jdbc:mysql://vip:3306/xxdb

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

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

High Availability: Redundant primaries and replicas provide robust failover.

High Performance: Combined benefits of dual‑primary and read‑replica scaling.

Consistency: Data consistency challenges persist; refer to the consistency solutions.

Scalability: Adding replicas improves read throughput, similar to Scheme 2.

Practical Considerations: Additional synchronization layers increase latency; careful design is required.

3. Consistency Solutions

Type 1: Primary‑Replica Consistency

The highlighted area shows where data synchronization occurs: the replica pulls binlog from the primary, which introduces a window of inconsistency. If a read request occurs during this window, stale data may be returned.

Ignore the delay if the business can tolerate it.

Force reads from the primary (use primary‑standby architecture) and employ caching to boost read performance. Beware of cache‑related cascade failures.

Cache‑first strategy: generate a key based on DB/table/business characteristics, store it in cache with TTL ≥ sync time, read from cache first, fall back to primary if cache miss.

Semi‑synchronous replication: the write returns only after the replica acknowledges, reducing inconsistency at the cost of higher latency.

Database middleware (e.g., Mycat) to route reads/writes intelligently, similar to the cache‑first approach but with higher complexity and cost.

Type 2: DB‑Cache Consistency

Typical cache usage flow:

Evict cache.

Write to DB.

Read from cache? If miss, read from DB.

After DB read, write back to cache.

If step 4 executes before step 3’s synchronization, the cache can become stale. To avoid this, always set an appropriate TTL on cached entries.

4. Personal Insights

Architecture Evolution Paths

Path 1: Scheme 1 → Scheme 1 + sharding → Scheme 2 + sharding → Scheme 4 + sharding

Path 2: Scheme 1 → Scheme 1 + sharding → Scheme 3 + sharding → Scheme 4 + sharding

Path 3: Scheme 1 → Scheme 2 → Scheme 4 → Scheme 4 + sharding

Path 4: Scheme 1 → Scheme 3 → Scheme 4 → Scheme 4 + sharding

Key Takeaways

Adding cache and indexes is a universal way to improve DB performance.

Sharding brings huge benefits but also introduces new challenges.

Choosing the right architecture (primary‑standby, dual‑primary, read/write separation, etc.) must consider specific business scenarios.

Most deployments stick with Scheme 1 or Scheme 1 + sharding; only a few adopt Scheme 3 with read/write separation and sharding.

Remember: an architecture that ignores business context is merely a shortcut.