Scaling MySQL for 20k+ Concurrent Users: Sharding and Read‑Write Separation

Why >20k Concurrent Connections Is Challenging

When a relational database must serve more than 20,000 simultaneous requests, the workload is an order of magnitude larger than a system that handles a few thousand connections. At 2 k concurrent connections time‑outs are already common; beyond 20 k the CPU, I/O, lock contention and network stack become bottlenecks, requiring a systematic scaling strategy.

Single‑Instance Architecture

The simplest deployment consists of a UI layer, an application server, and a single database server. This works for early‑stage products with a few hundred daily active users and tables that contain at most a few thousand rows. A single developer can build and maintain such a stack, but once the user base grows to hundreds of thousands and daily visits exceed 200 k, the single database becomes a hard limit.

Scaling Out with Sharding (Multi‑Instance)

When the database is the performance bottleneck, the common solution is to split the logical database into several smaller physical databases (shards). Each shard holds the same schema but a disjoint subset of the data. Assuming a baseline of 4 000 concurrent connections per server, five identical shards can comfortably support 20 k concurrent writes.

Routing Requests to Shards

Effective sharding requires a deterministic routing algorithm. A typical approach is to hash a stable user identifier (e.g., national ID, email, or UUID) and map the hash value to one of the shard identifiers. For example, the hash can be reduced modulo 5 and the result assigned to a geographic zone (East, North, West, South, Central). This evenly distributes users across shards and ensures that all operations for a given user hit the same shard.

Read‑Write Separation

Promotional events (flash sales, double‑11, etc.) generate a read‑heavy workload that can exhaust CPU, disk I/O and network bandwidth on the primary shard. Deploying a read‑only replica for each shard, synchronized in near‑real time, offloads the majority of SELECT queries while writes continue to go to the primary. This pattern is described in detail in Designing Data‑Intensive Applications .

Further Partitioning (Table Sharding)

As tables grow to billions of rows, a single shard can become unwieldy. Logical partitioning (e.g., by month or year) moves historical data to archive tables or separate physical tables while keeping the current period in the primary table. Queries that span multiple partitions can be abstracted with database views.

Implementation Checklist

Measure the baseline concurrent connections each database server can sustain (e.g., 4 000).

Determine the required number of shards: shards = ceil(target_concurrency / baseline). For 20 k target, shards = 5.

Design a deterministic routing function (hash‑mod, consistent hashing, or geographic mapping).

Provision a read‑only replica for each shard and configure asynchronous or synchronous replication according to latency requirements.

Implement table‑level partitioning for very large tables (time‑based or key‑based).

Monitor latency, CPU, I/O, and lock statistics on each shard; adjust the number of shards or replica lag settings as needed.