Scaling MySQL to Tens of Millions QPS: Practical Optimization Steps

MySQL is a core component of large‑scale architectures; achieving tens of millions of QPS requires systematic optimization across multiple stages.

Stage 1 – Single‑Node Performance Tuning

Before considering distribution, extract maximum performance from a single instance. Under ideal conditions (read‑only, small tables, cache hits), a single MySQL instance can handle 5 k–20 k QPS.

Key actions:

Upgrade hardware: use NVMe SSDs, increase memory to keep more data in the Buffer Pool, and adopt high‑frequency multi‑core CPUs.

Adjust configuration, e.g., set innodb_buffer_pool_size to 60%–80% of total RAM and tune innodb_flush_log_at_trx_commit based on safety vs. performance trade‑offs.

Optimize indexes and SQL: avoid full table scans, use covering indexes, and simplify complex JOINs.

Stage 2 – Read/Write Splitting

When read traffic dominates, introduce a master‑slave architecture to offload reads.

Architecture: one master (writes) and multiple slaves (reads). Middleware such as ProxySQL, MyCat, or ShardingSphere can automatically route read/write requests.

Typical bottleneck: write capacity of the master and replication lag of slaves.

Stage 3 – Adding a Caching Layer

To reach the ten‑million‑QPS level, a cache must absorb the majority of read traffic.

Use Redis or Memcached for hot‑data caching. Frequently accessed data stored in Redis can boost QPS by up to 100×.

Pattern: application checks cache first; on miss, it queries MySQL and writes back to the cache. This can offload 80%–90% of read requests.

Stage 4 – Database Sharding

When a single database exceeds tens of millions of rows or write throughput hits the node limit, horizontal or vertical sharding is required.

Vertical sharding: split tables by business domain into separate DB instances (e.g., users, orders, products).

Horizontal sharding: distribute rows of a single table across multiple databases/tables using a sharding key (e.g., UserID % N). Choose a sharding key carefully to avoid cross‑shard queries.

Tools: ShardingSphere, Vitess.