Evolution of MySQL Architecture for Different Concurrency Levels

Introduction The article outlines the evolution of MySQL architecture under varying concurrent access levels, emphasizing scalability and the need for appropriate architectural changes as traffic grows.

Scalability Concepts Scalability is closely tied to concurrency. Two main approaches are discussed: Scale‑up (vertical scaling by upgrading hardware) and Scale‑out (horizontal scaling by adding nodes). For high‑traffic internet applications, Scale‑out is the preferred solution.

Ideal Scalability State An ideal service can increase capacity simply by adding machines without downtime, ensuring continuous online operation.

V1.0 – Simple Website Architecture A small site can rely on a single MySQL instance for both reads and writes (ignoring backups). This setup suffices until data size, index memory, or request volume exceed the single‑instance limits.

V2.0 – Vertical Partitioning When V1.0 hits bottlenecks, vertical partitioning separates loosely related data into different MySQL instances, reducing contention. Adding a cache layer can further alleviate read pressure.

V3.0 – Master‑Slave Architecture To address read bottlenecks, a master‑slave setup replicates data in real time. The master handles writes, while slaves serve read traffic, suitable for read‑heavy workloads.

V4.0 – Horizontal Sharding When previous versions still cannot meet demand, horizontal sharding distributes data across multiple clusters, each holding a fraction (1/n) of the total dataset. Sharding keys determine data placement.

Sharding Strategies 1. Range Sharding : Split by continuous key ranges (e.g., user IDs). 2. List Sharding : Assign non‑continuous keys to specific clusters (e.g., stores by region). 3. Hash Sharding : Use a hash function or modulo to distribute rows evenly.

Challenges After Sharding Operations must know the sharding key to locate data, requiring auxiliary indexes (e.g., username → userID stored in Redis). Maintaining consistency between such caches and MySQL is difficult, often handled with asynchronous mechanisms like MQ.

Cluster Expansion / Shrinkage Adding or removing shards involves taking a slave offline, capturing incremental logs, re‑splitting data, replaying logs, and finally switching traffic. This process can be complex and error‑prone, so many systems pre‑allocate extra shards to avoid frequent re‑sharding.

V5.0 – Cloud‑Based SaaS MySQL In the cloud era, MySQL is offered as a SaaS service. After reaching V4.0, the focus shifts to making scaling transparent to applications: automatic, zero‑downtime expansion/shrinkage handled by a proxy that parses SQL, routes based on sharding keys, and directs reads/writes to appropriate masters or slaves.

Proxy Implementation A proxy intercepts MySQL protocol, determines the target shard, and performs read/write routing. Examples from Taobao and Baidu illustrate similar designs.

Sync‑Slave for Seamless Scaling A sync‑slave behaves like a normal MySQL slave but also captures full and incremental data. During scaling, the sync‑slave syncs data, and the proxy performs a hot‑switch to the new shard set without affecting online services.

Further Reading References include Baidu DBProxy design, Taobao RDS cloud database design, and MySQL Fabric sharding documentation.