Scaling MySQL: From Single Instance to Cloud‑Native Sharding Architecture

Introduction

This article describes how MySQL architecture evolves as website concurrency grows, from a single instance to cloud‑native sharding solutions.

Scalability

Scalability is closely tied to concurrency. Two common approaches are:

Scale‑up : vertical expansion by replacing a server with a more powerful one.

Scale‑out : horizontal expansion by adding more nodes.

For high‑traffic internet applications, scale‑out is the preferred path. The ideal scalability state is to increase capacity by adding machines without any downtime.

Architecture Evolution

V1.0 Simple Site Architecture

A small site can use a single MySQL instance for all reads and writes. Bottlenecks appear when any of the following exceed the single server’s limits: total data size, index size that fits in memory, or read/write traffic.

V2.0 Vertical Partitioning

When V1.0 hits limits, vertical partitioning separates loosely related tables into different MySQL instances, possibly adding a cache layer for read‑heavy workloads.

V3.0 Master‑Slave Architecture

To relieve read pressure, a master‑slave setup replicates data in real time. The master handles writes, while slaves serve reads, suitable for write‑light, read‑heavy applications.

V4.0 Horizontal Partitioning (Sharding)

When previous solutions still cannot meet demand, horizontal sharding distributes data across multiple clusters, each holding only a fraction (1/n) of the total dataset.

Sharding Routing Strategies

Range Sharding : split by continuous key ranges (e.g., user ID ranges).

List Sharding : assign non‑continuous keys to specific clusters (e.g., stores grouped by region).

Hash Sharding : apply a hash function to the sharding key (e.g., user_id % n).

Horizontal sharding introduces challenges such as the need for a sharding key in every query, maintaining reverse indexes (e.g., username → user_id in Redis), and handling data consistency between MySQL and cache layers.

Scaling out clusters also requires data migration. A typical procedure involves detaching a slave, capturing incremental logs, copying static data to a new slave, replaying increments, and switching traffic, which can be complex and error‑prone.

V5.0 Cloud‑Native MySQL (SaaS)

In the cloud era, MySQL is offered as a SaaS service. The main challenge is transparent, automated scaling without affecting the front‑end application. This is achieved by inserting a proxy that parses the MySQL protocol, routes queries based on the sharding key, and directs them to the appropriate master or slave.

Advanced implementations use a “sync‑slave” that mimics a regular slave but also performs data splitting and merging, enabling fully automated expansion or contraction.

Oracle’s MySQL Fabric is an example of a tool that supports such cloud‑native scaling.

Future Directions

Further evolution awaits new breakthroughs.

Additional Resources

Links to Baidu DBProxy design, Taobao RDS cloud database design, MySQL Fabric articles, and other related material.