Scaling means:

increasing system capacity to handle more traffic, more data, and more users

In MySQL, scaling is mainly split into:

Vertical Scaling (Scale Up) Horizontal Scaling (Scale Out)

Improve a single database server by increasing its resources:

CPU RAM SSD / Disk speed

Before:

 DB Server: - 2 CPU - 4 GB RAM 

After scaling up:

 DB Server: - 8 CPU - 32 GB RAM - NVMe SSD 

simple to implement no application change no architecture change

limited by hardware max expensive at high scale single point of failure

small to medium systems early stage startups quick performance improvement

Add more database servers instead of upgrading one.

 Instead of 1 big DB:

→ add multiple DB nodes

Architecture:

 App / \ Primary Replica 1 Replica 2 Replica 3 

high scalability high availability better fault tolerance can handle large traffic

complex architecture data consistency challenges more DevOps effort

Most common approach.

 Primary → handles writes Replicas → handle reads 

 GET /users → Replica DB GET /orders → Replica DB POST /orders → Primary DB 

Benefit:

reduces load on primary improves read performance

Split data into multiple databases.

 Users DB split by user_id 

Shard rule:

 Shard 1 → user_id 1–1M Shard 2 → user_id 1M–2M Shard 3 → user_id 2M–3M 

 Each DB stores only part of data 

very high scalability reduces per-node load

complex joins across shards difficult migrations hard transactions across shards

Common production setup:

 App | ---------------- | | Read LB Write Router | | | Replica Replica Primary 

Tools:

ProxySQL MySQL Router custom routing logic

slow queries missing indexes heavy joins

Solution:

index tuning query optimization

too many DB connections

Solution:

connection pool max_connections tuning

primary DB overloaded

Solution:

sharding queue-based writes caching

too many SELECT queries

Solution:

replicas caching (Redis)

 App / | \ Redis Read Write | LB | Cache Replica Primary 

Flow:

read → Redis first miss → replica DB write → primary DB + invalidate cache

System:

10M users 5K requests/sec

Solution:

 DB Layer: - 1 Primary - 5 Read Replicas

Cache Layer:

Redis Cluster

App Layer:

10+ backend pods 

Result:

reads handled by replicas + cache writes isolated to primary system scales horizontally