Database Application Architecture Principles and Common Schemes

1. Database Application Architecture Principles

High Availability (HA)

High Performance

Consistency

Scalability

2. Common Architecture Schemes

Scheme 1: Primary‑Standby Architecture (only primary handles read/write, standby used for failover)

jdbc:mysql://vip:3306/xxdb

1. High‑availability analysis: If the primary fails, a keepalive tool automatically switches to the standby, transparent to the business layer, requiring no code or configuration changes.

2. High‑performance analysis: All read/write traffic goes to the primary, easily becoming a bottleneck; most internet apps are read‑heavy, so reads become the limiting factor. The standby’s resources are under‑utilized (≈50%).

3. Consistency analysis: All operations on the primary avoid data‑consistency issues.

4. Scalability analysis: Adding replicas does not improve read performance in this scheme.

5. Practical implementation: Performance can be improved by adding efficient indexes and introducing caching to boost read performance, or use sharding for scalability.

Scheme 2: Dual‑Primary Architecture with Load Balancing

jdbc:mysql://vip:3306/xxdb

1. High‑availability analysis: If one primary fails, the other continues to serve traffic; the switch is transparent to the application.

2. High‑performance analysis: Read/write capacity roughly doubles compared with Scheme 1.

3. Consistency analysis: Data‑consistency issues arise; see the consistency solutions section.

4. Scalability analysis: Adding a third primary is possible but not recommended due to added data‑synchronization latency.

5. Practical implementation: Address consistency with dedicated solutions and resolve primary‑key conflicts using a distributed ID service.

Scheme 3: Primary‑Replica Architecture with Read/Write Separation (one primary, multiple replicas)

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

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

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

1. High‑availability analysis: The primary is a single point of failure; if it goes down, write services stop.

2. High‑performance analysis: Reads are offloaded to replicas, improving overall performance; replicas can have different indexes for online/offline workloads.

3. Consistency analysis: Data‑consistency problems exist; see the consistency solutions section.

4. Scalability analysis: Adding more replicas improves read throughput but increases load on the primary for binlog replication.

5. Practical implementation: Consistency issues can be mitigated as described below; no perfect solution for primary single‑point failure yet.

Scheme 4: Dual‑Primary + Primary‑Replica Architecture (seemingly perfect)

jdbc:mysql://vip:3306/xxdb

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

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

1. High‑availability analysis: High availability.

2. High‑performance analysis: High performance.

3. Consistency analysis: Data‑consistency issues persist; see consistency solutions.

4. Scalability analysis: Read scalability can be improved by adding replicas, but additional synchronization layers increase latency.

5. Practical implementation: Similar trade‑offs as Scheme 2, with extra data‑sync delay.

Note: What happens if a replica fails? How does read‑load‑balancing handle fault tolerance?

3. Consistency Solutions

Category 1: Primary‑Replica Consistency Solutions

Data synchronization (replica pulling binlog from primary) takes time, causing temporary inconsistency; reads during this window may return stale data.

Possible remedies:

Ignore the delay if the business can tolerate it.

Force reads from the primary (primary‑standby) and use caching to improve read performance; ensure cache is highly available to avoid cascade failures.

Read‑from‑primary strategy: generate a cache key based on DB/table/business characteristics, store it with a TTL longer than the sync lag; on read, check cache first—if hit, read primary, else read replica.

Semi‑synchronous replication: wait for replica acknowledgment before confirming writes (increases write latency, reduces throughput).

Database middleware (e.g., MyCat) to route reads; adds complexity and cost but offers flexible routing.

Category 2: DB and Cache Consistency Solutions

Typical cache workflow:

Evict cache.

Write to DB.

Read cache? Return DB read.

After DB read, write back to cache.

If step 4 executes before step 3, stale cache data appears. Solutions include setting appropriate cache TTLs and using the strategies listed above.

4. Personal Insights

1. Architecture Evolution

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

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

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

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

2. Personal Opinions

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

Sharding brings huge benefits but also introduces challenges (see separate discussion on vertical vs. horizontal sharding).

Choosing between primary‑standby + sharding or primary‑replica + read/write separation + sharding depends heavily on business scenarios; many enterprises still use Scheme 1 or Scheme 1 + sharding.

Never adopt an architecture without considering the specific business context.

Author: Gagagā Rénwù

www.cnblogs.com/littlecharacter

Recommended Reading: Why Microservice Design Experience Is So Valuable?