Unascale vipi MySQL wakati traffic inakua mara 100?

Question

IT Interview Practice · Accepted Answer

Scale kwa hatua: **read replicas + caching kwa reads kwanza, kisha sharding (Vitess) kwa writes** — na ongeza connection pooling kabla ya kitu chochote kingine, kwa sababu ndiyo 10x ya bei nafuu zaidi. Reads na writes huscale tofauti, hivyo usikimbilie sharding siku ya kwanza.

```text
        ┌─▶ Cache (Redis) ── hit ──▶ return
App ─▶ Pool(PgBouncer/ProxySQL)      miss ▼
        │                    ┌─▶ Read replica 1 ┐
        ├──── reads ─────────┼─▶ Read replica 2 ┼─ (async replication)
        │                    └─▶ Read replica N ┘
        └──── writes ──▶ Primary ──▶ (later) Vitess shards ─▶ Primary A / Primary B
```

## Hatua 0: connection pooling

Kila MySQL connection hugharimu memory na thread. Kwenye traffic ya 100x utaishiwa `max_connections` muda mrefu kabla ya CPU. Pooler kama **ProxySQL** au **PgBouncer** humultiplex maelfu ya app connections kwenye DB connections mia chache. Ni config change, si rearchitecture — fanya kwanza.

## Hatua 1: cache reads

Workloads nyingi ni read-heavy (mara nyingi 90%+). Weka **Redis** mbele kwa hot rows — user profiles, product pages. Cache hit kamwe haigusi MySQL. Tumia cache-aside: soma cache → miss → soma DB → jaza cache. Hii peke yake inaweza kunyonya sehemu kubwa ya 100x.

## Hatua 2: read replicas

Ongeza **async read replicas** na elekeza SELECTs kwao, writes kwa primary. Hii huscale reads kwa usawa: N replicas ≈ N× read capacity. Tatizo ni **replication lag** — replica inaweza kuwa nyuma kwa milliseconds-hadi-sekunde, hivyo read-your-own-writes inahitaji uangalifu (elekeza reads za mtumiaji huyo kwa primary, au soma kutoka cache).

## Hatua 3: shard writes

Replicas hazisaidii writes — kila write bado hupiga primary moja. Wakati write throughput inajaza box moja, **shard**. **Vitess** (iliyojengwa YouTube) hukaa mbele ya MySQL na hufanya sharding, query routing, na online resharding bila kuandika app upya. Shard kwa `user_id` ili queries nyingi zibaki single-shard.

## Mtazamo wa mahojiano

Wahojiwa huangalia **mpangilio**: watahiniwa wanaoshard mara moja hufichua hawajui replicas/caching hutatua read scaling kwa bei nafuu zaidi. Nzuri: "ongeza replicas na cache." Bora sana: unatenganisha read path na write path, unataja replication lag kama gharama ya replicas, na unaanzisha sharding tu wakati *writes* — si reads — ndio bottleneck.

**Mtego wa kawaida:** "ongeza read replicas tu" wakati bottleneck halisi ni write throughput. Replicas huifanya *mbaya zaidi* (replication load zaidi kwenye primary).

## Maelewano na aina za hitilafu

- **Replication lag:** stale reads; pin critical reads kwa primary au tumia semi-sync replication.
- **Cache stampede:** misses nyingi hupiga DB kwa pamoja wakati wa expiry — tumia locks au staggered TTLs.
- **Ugumu wa sharding:** hupoteza cross-shard joins na transactions; lipa hili tu wakati writes zinalazimisha.
- **Failover:** kifo cha primary kinahitaji automated promotion (Orchestrator); replica iliyopandishwa iliyo stale inaweza kupoteza writes za hivi karibuni.

Hatua 0: connection pooling

Hatua 1: cache reads

Hatua 2: read replicas

Hatua 3: shard writes

Mtazamo wa mahojiano

Maelewano na aina za hitilafu

Maswali yanayohusiana