Table of Contents
Database Scaling (MySQL)
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)
1. Vertical Scaling (Scale Up)
What is it?
Improve a single database server by increasing its resources:
CPU RAM SSD / Disk speed
Example
Before:
DB Server: - 2 CPU - 4 GB RAM
After scaling up:
DB Server: - 8 CPU - 32 GB RAM - NVMe SSD
Pros
simple to implement no application change no architecture change
Cons
limited by hardware max expensive at high scale single point of failure
When to use
small to medium systems early stage startups quick performance improvement
2. Horizontal Scaling (Scale Out)
What is it?
Add more database servers instead of upgrading one.
Example
Instead of 1 big DB: → add multiple DB nodes
Architecture:
App / \ Primary Replica 1 Replica 2 Replica 3
Pros
high scalability high availability better fault tolerance can handle large traffic
Cons
complex architecture data consistency challenges more DevOps effort
Common Horizontal Scaling Strategies
1. Read Replicas (Read Scaling)
Most common approach.
Primary → handles writes Replicas → handle reads
Example
GET /users → Replica DB GET /orders → Replica DB POST /orders → Primary DB
Benefit:
reduces load on primary improves read performance
2. Sharding (Data Partitioning)
Split data into multiple databases.
Example
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
Result
Each DB stores only part of data
Pros
very high scalability reduces per-node load
Cons
complex joins across shards difficult migrations hard transactions across shards
3. Master-Slave + Load Balancer
Common production setup:
App | ---------------- | | Read LB Write Router | | | Replica Replica Primary
Tools:
ProxySQL MySQL Router custom routing logic
Scaling Bottlenecks
1. CPU bottleneck
slow queries missing indexes heavy joins
Solution:
index tuning query optimization
2. Connection bottleneck
too many DB connections
Solution:
connection pool max_connections tuning
3. Write bottleneck
primary DB overloaded
Solution:
sharding queue-based writes caching
4. Read bottleneck
too many SELECT queries
Solution:
replicas caching (Redis)
Scaling + Caching Architecture
App / | \ Redis Read Write | LB | Cache Replica Primary
Flow:
read → Redis first miss → replica DB write → primary DB + invalidate cache
Real Production Example
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
Summary
| Type | Description | Use Case |
|---|---|---|
| Vertical Scaling | Upgrade single DB machine | small/medium systems |
| Horizontal Scaling | Add more DB nodes | large systems |
| Read Replicas | Scale reads | high traffic SELECT |
| Sharding | Split data across DBs | massive datasets |
| Caching | Reduce DB load | performance optimization |