Start practising

SYSTEM DESIGN · TECHNOLOGIES · 2 OF 12

MySQL: InnoDB, Replication and Sharding Read-Heavy Web Apps

How MySQL with InnoDB stores rows in a clustered primary-key index, why secondary lookups cost two trips, redo and undo logs, the binlog and replica fan-out, repeatable read and gap locks, why random UUID keys slow inserts, scaling with replicas, ProxySQL and Vitess, where it hurts and how to justify it in an interview.

  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
  12. 12
1 / 15

Press play to hear the narration, or step through at your own pace.

Notes

What it is

MySQL is an open-source relational database. Under the SQL layer sits a pluggable storage engine, and today that is InnoDB, which gives you transactions, row-level locks and crash recovery. It is offered by every cloud, including Aurora and PlanetScale. Unlike Postgres it uses a thread per connection, which is lighter, though a pooler still helps at scale.

MySQL in one line
  • open-source relational DB
  • owned by Oracle; MariaDB is
  • a community fork
  • storage engine: InnoDB
  • (transactions, row locks)
  • managed: RDS, Aurora MySQL,
  • Cloud SQL, PlanetScale
  • thread per connection

Clustered index

The most important internal: InnoDB stores the table itself as a B plus tree ordered by primary key. The leaf pages hold whole rows. A secondary index, say on email, stores the email and the primary key, not a row pointer. So a lookup by email walks two trees: first the email index, then the primary key tree. Lookups by primary key are one walk, and range scans by primary key read neighbouring rows.

InnoDB layout
primary key B+tree (the table):
  leaf = PK + the whole row

secondary index on email:
  leaf = email + PK

SELECT * WHERE email = ?
  1. find PK in email index
  2. find row in PK tree
  = two B+tree lookups

Primary keys

That layout makes the primary key choice matter. With an auto-increment or time-ordered id, every insert goes to the right edge of the tree, pages fill up neatly and stay in memory. With random version four UUIDs, inserts land all over the tree, pages split, and more of the index must be cached. Every secondary index also carries the key, so a long key makes them all bigger. Concepts episode 32 covers ordered ids.

Pick an ordered primary key
  • auto-increment or time-ordered
  • (UUIDv7, Snowflake-style id)
  • → inserts append to the right
  • edge of the tree, pages fill
  • random UUIDv4
  • → inserts land anywhere,
  • page splits, cold pages,
  • bigger secondary indexes
  • see Concepts episode 32 (IDs)

Write path

On a write, InnoDB records three things. The redo log holds page changes, replayed after a crash. The undo log holds the previous row versions, used for rollback and so readers can see an older snapshot. And the server writes the change to the binlog as a row event, which replicas and change data capture tools consume. On commit, redo and binlog are flushed together so they never disagree.

Three logs on commit
  • redo log: physical page
  • changes, replayed after crash
  • undo log: old row versions
  • for rollback and MVCC reads
  • binlog: logical row events
  • for replicas and CDC
  • commit flushes redo + binlog
  • (two-phase, kept in sync)

Isolation

Isolation is a classic interview detail. MySQL defaults to repeatable read, stricter than the Postgres default. Plain selects read a snapshot without locking. But a locking read, like select for update, also locks the gaps between index entries, so nobody can insert into that range. That prevents phantoms but causes more lock waits and deadlocks on hot tables. InnoDB detects the deadlock and rolls one transaction back, so your code must retry.

Locks and isolation
  • default: REPEATABLE READ
  • (Postgres: READ COMMITTED)
  • plain SELECT: snapshot read,
  • no locks
  • SELECT ... FOR UPDATE:
  • row + gap ("next-key") locks
  • gap locks block inserts into
  • a range: more deadlocks on
  • busy tables, retry them

Replication

Replication is MySQL's long-time strength. Each replica pulls the binlog and replays it, now in parallel. It is asynchronous by default, so replicas can lag. Semi-synchronous mode waits until at least one replica has received the event before commit returns, which shrinks the loss window. Global transaction ids make it easy to repoint replicas during failover. A proxy such as ProxySQL sends writes to the primary and spreads reads across replicas.

Binlog replication
  • replica pulls binlog, applies
  • it (parallel apply by default)
  • async: default, may lag
  • semi-sync: one replica acks
  • receipt before commit returns
  • GTIDs: each txn has a global
  • id, failover is simpler
  • Group Replication / InnoDB
  • Cluster: consensus-based

Read your writes

Replica lag creates a classic bug. A user saves their profile, the next page reads from a replica that is half a second behind, and the old profile appears. Three fixes. Send that user's reads to the primary for a few seconds after a write. Or wait until the replica has applied the transaction id of the write. Or serve the fresh value from a cache you just updated. Concepts episode 26 calls this read your writes.

Handling replica lag
  • user saves profile → reads
  • from a lagging replica →
  • sees the old profile
  • fixes:
  • • read from primary for N
  • seconds after a write
  • • wait for the replica to reach
  • the write’s GTID
  • • cache the fresh value

Sharding

When writes outgrow one primary, MySQL teams shard. The simple way is application routing by user id across many databases. The well-known tool is Vitess, which grew out of a large video site's MySQL fleet. A gateway parses each query, routes it by the sharding key, pools connections and can reshard online. Each shard is still an ordinary MySQL primary with replicas.

Sharding MySQL
  • app-level: route by user_id
  • to one of N databases
  • Vitess: VTGate routes SQL by
  • a sharding key (vindex),
  • reshards online, pools
  • connections; PlanetScale
  • runs it as a service
  • each shard: primary + replicas

Great at

MySQL shines for read-heavy web applications: a primary plus a fleet of replicas carries a lot of traffic. Primary key lookups and range scans are fast because rows sit in key order. The tooling is mature, including online schema change tools and instant column adds. And with Vitess there is a proven path to very large horizontal scale.

Where MySQL shines
  • • read-heavy web apps with
  • many replicas
  • • fast PK lookups and PK range
  • scans (clustered layout)
  • • mature replication, tooling,
  • online schema change
  • (gh-ost, pt-osc, INSTANT DDL)
  • • Vitess for horizontal scale
  • • huge hiring pool

Where it hurts

Where it hurts. One write primary per shard, like any classic relational database. Fewer advanced features than Postgres: fewer index types, a smaller extension ecosystem and weaker JSON. Gap locks cause deadlocks under contention. Random keys slow inserts. Replicas lag. And once you shard, joins and transactions across shards are something you design around, not something you get.

Pain points
  • • single primary per shard
  • • fewer advanced features than
  • Postgres (index types,
  • extensions, rich JSON)
  • • gap locks → deadlocks
  • • random PKs hurt inserts
  • • replica lag, stale reads
  • • big ALTERs need a tool
  • • cross-shard joins: avoid

Use it when

Use MySQL for read-heavy transactional apps, especially when the team already operates it, or when you expect to shard and want Vitess. Prefer Postgres when you need rich features like geo queries, vectors or advanced indexes. Use a column store for heavy analytics. And neither is the natural fit for active writes in several regions at once.

Use it when / avoid it when
  • USE when
  • read-heavy OLTP web app
  • team already knows MySQL
  • plan to shard (Vitess)
  • AVOID when
  • heavy analytics, geo, vectors
  • (Postgres or a column store)
  • multi-region active writes
  • schemaless, nested records

Numbers

Numbers to remember. InnoDB pages are sixteen kilobytes. The default isolation is repeatable read and the default connection limit is one hundred and fifty-one. Give the buffer pool about seventy to eighty percent of memory on a dedicated server. A single node does tens of thousands of primary-key reads a second, and simple writes in the thousands to tens of thousands. Replication lag is usually milliseconds, but spikes under heavy writes.

Numbers worth knowing
InnoDB page size       16 KB
default isolation      REPEATABLE READ
default max_connections 151
PK point reads/node    10k-100k+/s
simple writes/node     5k-30k+/s
buffer pool            ~70-80% of RAM
replicas per primary   often 5-10+
replica lag            ms to seconds

Say it like this

How to say it. MySQL with InnoDB, with ordered big integer ids so inserts append to the tree. One primary and three replicas behind a proxy that splits reads from writes, and a user's reads go to the primary for a few seconds after they write. At ten times the writes, shard with Vitess by user id, so each user's rows live on one shard and most queries never cross shards.

In the interview
  • "MySQL with InnoDB. Ordered
  • bigint ids so inserts append.
  • One primary, three replicas
  • behind ProxySQL; reads after
  • a write go to the primary for
  • a few seconds. At 10x writes,
  • Vitess sharded by user_id so
  • a user’s rows stay together."

Interview questions

What is a clustered index in InnoDB?

InnoDB stores the table rows inside the primary-key B+tree, so the table is ordered by primary key. Secondary indexes store the primary key value, so a lookup through them needs a second walk of the primary-key tree.

Why are random UUID primary keys a problem in MySQL?

Random keys make inserts land on random leaf pages, causing page splits, poor cache locality and larger secondary indexes, since each one stores the key. Auto-increment or time-ordered ids such as UUIDv7 append to the end of the tree instead.

What is the binlog used for?

The binary log records committed changes as logical events, usually row-based. Replicas pull and replay it, and change data capture tools such as Debezium read it to stream changes to other systems.

What is the default isolation level in MySQL and what are gap locks?

InnoDB defaults to REPEATABLE READ. Locking reads take next-key locks that cover index records and the gaps between them, which blocks inserts into the range and prevents phantoms but increases lock contention and deadlocks.

How do you handle replica lag for read-your-writes?

Route a user to the primary for a short window after they write, wait until the replica has applied the write by checking its GTID position, or serve the fresh value from a cache that was updated on write.

What is semi-synchronous replication?

The primary waits until at least one replica confirms it has received the transaction before acknowledging the commit, which reduces the chance of losing committed data during failover at a small latency cost.

How would you shard MySQL?

Pick a key present in most queries, such as user or tenant id, and route by it either in the application or with Vitess, which provides query routing, connection pooling and online resharding. Each shard keeps its own primary and replicas.

MySQL or PostgreSQL?

Both are solid relational choices. MySQL is a natural fit for read-heavy web apps and has Vitess for sharding, while PostgreSQL offers richer SQL, index types and extensions such as PostGIS and pgvector; team familiarity often decides.