How MySQL Architecture Evolves to Handle High Concurrency: From Single Instance to Cloud SaaS

Scalability

Scalability is closely tied to concurrency; without growing traffic there is no need for highly scalable designs. Two common approaches are:

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

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

The ideal scalable service can increase concurrency simply by adding machines without any downtime.

Architecture Evolution

V1.0 Simple Architecture

A small site can rely on a single MySQL instance for both reads and writes. Bottlenecks appear when any of the following exceed capacity: total data size, index size (B+ Tree), or mixed read/write load.

V2.0 Vertical Partitioning

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

V3.0 Master‑Slave Architecture

To alleviate read pressure, a master‑slave setup replicates data in real time. The master handles writes, while slaves serve reads, fitting write‑light, read‑heavy scenarios.

V4.0 Horizontal Sharding

Horizontal sharding distributes data across multiple clusters, each holding a fraction (1/n) of the total dataset. Sharding keys determine routing.

Sharding Methods

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

List Sharding : assign non‑continuous key sets to clusters (e.g., grouping stores by region).

Hash Sharding : apply a hash function to the key (e.g., userid % n) to decide the target cluster.

Horizontal sharding introduces challenges such as the need for a sharding key for every operation, extra reverse‑index mechanisms (e.g., Redis mapping username→userid), and potential data inconsistency between caches and MySQL.

Cluster expansion requires data re‑distribution, which involves stopping a slave, capturing incremental logs, splitting data, replaying logs, and switching to the new cluster set—an operation comparable to “air‑to‑air refueling” in complexity.

V5.0 Cloud‑Native SaaS

In a SaaS context, MySQL must become a multi‑tenant service with configurable, scalable, and transparent scaling. After V4.0, the focus is on automating expansion/shrinkage without affecting front‑end applications, typically by introducing a proxy that parses SQL, routes based on the sharding key, and hides master/slave details.

Open‑source solutions like MySQL Fabric illustrate approaches to automated scaling in cloud environments.