Kepiye sampeyan nyekala MySQL nalika lalu lintas mundhak 100x?

Question

IT Interview Practice · Accepted Answer

Skala kanthi bertahap: **read replica + caching kanggo pamaca dhisik, banjur sharding (Vitess) kanggo tulisan** — lan tambahake connection pooling sadurunge apa wae, amarga iku 10x sing paling murah. Pamaca lan tulisan nyekala kanthi beda, mula aja njangkau sharding ing dina kapisan.

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

## Langkah 0: connection pooling

Saben koneksi MySQL ngentekake memori lan thread. Ing lalu lintas 100x sampeyan bakal ngentekake `max_connections` adoh sadurunge CPU. Pooler kaya **ProxySQL** utawa **PgBouncer** nge-multipleks ewonan koneksi app dadi sawetara atus koneksi DB. Iki owah-owahan konfigurasi, dudu arsitektur ulang — lakonana dhisik.

## Langkah 1: cache pamaca

Umume beban kerja iku akeh pamaca (asring 90%+). Selehke **Redis** ing ngarep kanggo row panas — profil pangguna, kaca produk. Cache hit ora tau ndemek MySQL. Nganggo cache-aside: maca cache → miss → maca DB → isi cache. Iki wae bisa nyerap sapérangan gedhé saka 100x.

## Langkah 2: read replica

Tambahake **async read replica** lan arahake SELECT menyang kono, tulisan menyang primary. Iki nyekala pamaca kanthi horizontal: N replika ≈ N× kapasitas pamaca. Sing kudu digatèkake yaiku **replication lag** — replika bisa keri milidetik-nganti-detik, mula read-your-own-writes butuh ati-ati (arahake pamaca pangguna kuwi menyang primary, utawa maca saka cache).

## Langkah 3: shard tulisan

Replika ora mbantu tulisan — saben tulisan isih ndemek siji primary. Nalika throughput tulisan njenuhi siji kotak, **shard**. **Vitess** (dibangun ing YouTube) lungguh ing ngarep MySQL lan nindakake sharding, routing query, lan resharding online tanpa nulis ulang app. Shard miturut `user_id` supaya umume query tetep siji-shard.

## Sudut pandang wawancara

Pewawancara nggatèkake **urutan**: kandidat sing langsung nge-shard nuduhake yen dheweke ora ngerti replika/caching ngrampungake skala pamaca kanthi luwih murah. Apik: "tambah replika lan cache." Apik banget: sampeyan misahake read path saka write path, nyebutake replication lag minangka biaya replika, lan mung ngenalake sharding nalika *tulisan* — dudu pamaca — sing dadi bottleneck.

**Jebakan umum:** "cukup tambah read replica" nalika bottleneck sing sanyatane yaiku throughput tulisan. Replika ndadekake kuwi *luwih ala* (luwih akeh beban replikasi ing primary).

## Trade-off lan mode kegagalan

- **Replication lag:** pamaca basi; pin pamaca kritis menyang primary utawa nganggo semi-sync replication.
- **Cache stampede:** akeh miss ndemek DB bebarengan nalika kadaluwarsa — nganggo lock utawa TTL sing digeser-geser.
- **Kerumitan sharding:** ilang join lan transaksi antar-shard; mung bayar iki nalika tulisan meksa.
- **Failover:** matine primary butuh promosi otomatis (Orchestrator); replika sing dipromosekake nanging basi bisa ilang tulisan anyar.

Langkah 0: connection pooling

Langkah 1: cache pamaca

Langkah 2: read replica

Langkah 3: shard tulisan

Sudut pandang wawancara

Trade-off lan mode kegagalan

Pitakon sing gegandhengan