How MySQL Architecture Scales from a Single Instance to Cloud‑Native SaaS

Preface

This article describes how MySQL architecture evolves under different levels of concurrent traffic.

Scalability

Scalability is closely tied to concurrency. Two common expansion methods are:

Scale‑up : vertical expansion by replacing machines with more powerful ones.

Scale‑out : horizontal expansion by adding nodes, which is the preferred path for high‑traffic Internet applications.

The ideal scalable state is a service that can increase capacity simply by adding machines without any downtime.

Architecture Evolution

V1.0 Simple Site Architecture

A small website can rely on a single MySQL instance for all reads and writes (ignoring backup instances). Bottlenecks appear when any of the following exceed a single machine’s capacity: total data size, index size, or mixed read/write request volume.

V2.0 Vertical Partitioning

When V1.0 hits a bottleneck, the simplest remedy is vertical partitioning: split loosely related data into separate MySQL instances based on business domains, optionally adding a cache layer for read‑heavy workloads.

V3.0 Master‑Slave Architecture

To solve the read bottleneck of V2.0, a master‑slave setup replicates data in real time. The master handles writes, while slaves serve read traffic, making it suitable for write‑light, read‑heavy applications.

V4.0 Horizontal Sharding

When V2.0 or V3.0 reaches limits, horizontal sharding distributes data across multiple clusters, each holding a fraction (1/n) of the total dataset. Sharding keys ensure balanced traffic.

Sharding Strategies

Range Sharding : split by continuous key ranges (e.g., userId 1‑30M, 30M‑60M).

List Sharding : assign non‑continuous keys to specific clusters (e.g., store IDs per region).

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

Horizontal sharding introduces the need for a sharding key in every query and may require auxiliary reverse indexes (e.g., Redis mapping username → userId) to support lookups on non‑key attributes, which can create consistency challenges.

V5.0 Cloud‑Native SaaS

In the cloud era, MySQL must become a SaaS offering. The main challenges are configurability, scalability, and multi‑tenant storage design. With a well‑designed sharding key, scalability issues disappear, but automated, zero‑downtime scaling and shrinking remain essential.

Implementing a proxy that parses the MySQL protocol, routes queries based on the sharding key, and directs reads to slaves and writes to masters makes scaling transparent to applications.

During scaling, a sync‑slave can perform full and incremental synchronization while the proxy handles hot‑switching of back‑ends, achieving zero‑impact scaling.

Future Directions

The article concludes with a note that further evolution will continue as new challenges arise.