====== 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:

<code> DB Server: - 2 CPU - 4 GB RAM </code>

After scaling up:

<code> DB Server: - 8 CPU - 32 GB RAM - NVMe SSD </code>

===== 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 =====

<code> Instead of 1 big DB:

→ add multiple DB nodes
</code>

Architecture:

<code> App / \ Primary Replica 1 Replica 2 Replica 3 </code>

===== 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.

<code> Primary → handles writes Replicas → handle reads </code>

===== Example =====

<code> GET /users → Replica DB GET /orders → Replica DB POST /orders → Primary DB </code>

Benefit:

reduces load on primary
improves read performance

===== 2. Sharding (Data Partitioning) =====

Split data into multiple databases.

===== Example =====

<code> Users DB split by user_id </code>

Shard rule:

<code> Shard 1 → user_id 1–1M Shard 2 → user_id 1M–2M Shard 3 → user_id 2M–3M </code>

===== Result =====

<code> Each DB stores only part of data </code>

===== 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:

<code> App | ---------------- | | Read LB Write Router | | | Replica Replica Primary </code>

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 ======

<code> App / | \ Redis Read Write | LB | Cache Replica Primary </code>

Flow:

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

====== Real Production Example ======

System:

10M users
5K requests/sec

Solution:

<code> DB Layer: - 1 Primary - 5 Read Replicas

Cache Layer:

Redis Cluster

App Layer:

10+ backend pods </code>

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 |