Ta yaya za ka scale MySQL lokacin da traffic ya girma sau 100?

Question

IT Interview Practice · Accepted Answer

Scale a matakai: **read replicas + caching ga reads da farko, sannan sharding (Vitess) ga writes** — kuma ka ƙara connection pooling kafin komai, domin shi ne mafi arha na 10x. Reads da writes suna scale daban, don haka kada ka nemi sharding rana ta farko.

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

## Mataki 0: connection pooling

Kowace MySQL connection tana kashe memory da thread. A traffic na 100x za ka gaji `max_connections` tun kafin CPU. Pooler kamar **ProxySQL** ko **PgBouncer** yana multiplex dubban app connections zuwa 'yan ɗari na DB connections. Canjin config ne, ba rearchitecture ba — yi shi da farko.

## Mataki 1: cache reads

Yawancin workloads suna da read-heavy (sau da yawa 90%+). Sanya **Redis** a gaba ga hot rows — user profiles, product pages. Cache hit ba ya taɓa MySQL. Yi amfani da cache-aside: read cache → miss → read DB → populate cache. Wannan kaɗai na iya ɗauke da yawancin 100x.

## Mataki 2: read replicas

Ƙara **async read replicas** kuma ka tura SELECTs zuwa gare su, writes zuwa primary. Wannan yana scale reads horizontally: N replicas ≈ N× read capacity. Matsalar ita ce **replication lag** — replica na iya kasancewa milliseconds-zuwa-seconds a baya, don haka read-your-own-writes yana buƙatar kulawa (tura reads na wannan user zuwa primary, ko karanta daga cache).

## Mataki 3: shard writes

Replicas ba sa taimaka writes — kowane write har yanzu yana bugun single primary. Lokacin da write throughput ya cika box ɗaya, **shard**. **Vitess** (an gina a YouTube) yana zaune a gaban MySQL kuma yana yin sharding, query routing, da online resharding ba tare da sake rubuta app ba. Shard da `user_id` domin yawancin queries su kasance single-shard.

## Ra'ayin hira

Masu yin hira suna kallon **oda**: 'yan takara da nan take suke shard suna nuna ba su san replicas/caching suna warware read scaling da arha sosai ba. Mai kyau: "ƙara replicas da cache." Mai kyau sosai: ka raba read path daga write path, ka ambaci replication lag a matsayin farashin replicas, kuma ka kawo sharding kawai lokacin da *writes* — ba reads ba — su ne bottleneck.

**Tarkon gama gari:** "ƙara read replicas kawai" lokacin da ainihin bottleneck shi ne write throughput. Replicas suna mai da hakan *muni* (ƙarin replication load a primary).

## Sulhu & hanyoyin gazawa

- **Replication lag:** stale reads; pin critical reads zuwa primary ko yi amfani da semi-sync replication.
- **Cache stampede:** misses da yawa suna bugun DB a lokaci ɗaya a expiry — yi amfani da locks ko staggered TTLs.
- **Sharding complexity:** yana rasa cross-shard joins da transactions; biya wannan kawai lokacin da writes suka tilasta.
- **Failover:** mutuwar primary tana buƙatar automated promotion (Orchestrator); stale promoted replica na iya rasa recent writes.

Mataki 0: connection pooling

Mataki 1: cache reads

Mataki 2: read replicas

Mataki 3: shard writes

Ra'ayin hira

Sulhu & hanyoyin gazawa

Tambayoyi masu alaƙa