MySQL

Why it exists

MySQL (specifically InnoDB, the default storage engine since MySQL 5.5) is the most-deployed relational database on the internet. It powers Facebook, GitHub, YouTube, Airbnb, Uber, Shopify, and thousands of others. Its combination of a clustered primary-key index, mature MVCC, best-in-class replication, and the Vitess sharding layer makes it the default relational DB for consumer-facing internet products at every scale from startup MVP to planetary.

How it works

MySQL InnoDB stores rows INSIDE the primary-key B+tree (clustered index) — the leaf pages contain the actual row data, not a pointer to a heap. This means a PK lookup like `SELECT ... WHERE pk = ?` is 1 disk I/O for warm data, not 2. MVCC is implemented via undo tablespaces: on each modification, the previous row version goes to undo; readers reconstruct their snapshot from undo. The redo log (InnoDB's Write-Ahead Log) captures every change before disk; `innodb_flush_log_at_trx_commit=1` gives full ACID durability. Buffer pool sized to 70-80% of RAM holds the hot working set — very different from Postgres's 25% philosophy. Replication is via binlog (statement-based, row-based, or mixed) — async by default, semi-sync opt-in for read-your-writes. GTID (Global Transaction ID) tracks replication position robustly across failovers.

Scaling characteristics

A well-tuned MySQL m6i.large (2 vCPU, 8 GB RAM, InnoDB buffer pool sized to 6 GB) handles ~20-25K point-lookup QPS at 60-70% CPU with a 90% warm buffer pool hit rate. Writes cap at ~5-10K QPS on Multi-AZ semi-sync (dominated by cross-AZ commit round-trip). Read scaling: async binlog-replicated read replicas — each additional replica adds ~20K reads at some staleness. Write scaling: Vitess (YouTube-tested), the horizontal-sharding layer that gives you routing, resharding without downtime, and cross-shard query planning. Vitess powers Slack (thousands of shards), GitHub (moving to Vitess in 2023), HubSpot, and Cash App. Storage: single-instance managed MySQL scales to ~64 TB (Aurora MySQL); sharded Vitess fleets scale to petabytes.

When to use it

• Point-lookup-dominant workloads where the clustered PK index gives you 1 I/O per read (URL shorteners, user profile lookups, cart lookups)
• You need Vitess for L6+ sharding — the most mature horizontal-sharding layer in the SQL world
• You're on AWS and want Aurora MySQL (write-optimized shared-storage cloud-native flavor)
• Team is familiar with MySQL / your existing app stack (Rails, Django ORM, Laravel) speaks MySQL
• Consumer-facing products with cost sensitivity and moderate write volume — MySQL RDS is significantly cheaper than DynamoDB above ~1M requests/day

When NOT to use it

• You need PostGIS-quality geospatial queries — MySQL Spatial is less mature
• You need pgvector-quality vector similarity — Postgres or a dedicated vector DB is stronger
• You need extension richness (hstore, pgvector, tsvector for full-text, TimescaleDB) — Postgres is the ecosystem winner
• You need a JSONB with GIN indexes for a document-heavy workload — Postgres JSONB is more mature than MySQL JSON
• You need >100K writes/sec sustained without sharding investment — consider Cassandra or a wide-column store

Failure modes

• Primary DB down → automatic failover to a Multi-AZ standby (~30-90s outage window on RDS)
• Replication lag on binlog under heavy write pressure → replicas serve stale data; write-then-read from the same session fails read-your-writes
• Long-running SELECT holds an old snapshot → InnoDB undo log grows; disk pressure and increased purge lag
• Bad query without a covering index → clustered PK still helps but a secondary-index lookup requires PK dereference (still 2 I/Os)
• Connection storm → 'too many connections' errors; mitigate with ProxySQL, RDS Proxy, or HikariCP-style pooling
• `innodb_buffer_pool_size` too small → high cold-hit rate, latency spikes as buffer pool churns
• Vitess resharding under load → temporary lookup latency spikes as vindexes rebuild; must be planned during low-traffic windows

Alternatives

• PostgreSQL — richer type system + extensions (PostGIS, pgvector), but 2 I/Os per cold PK lookup vs MySQL's 1; Citus for sharding is less mature than Vitess
• Aurora MySQL — MySQL-compatible cloud-native shared-storage flavor with 5x-15x more read replicas and near-zero failover; higher cost
• TiDB — MySQL-wire-compatible NewSQL with horizontal scale built in; ~2x cost vs sharded MySQL
• PlanetScale — Vitess-as-a-service; excellent developer experience, generous free tier, but paid tier cost accumulates at scale
• CockroachDB — Postgres-wire-compatible; different DB entirely but often compared for global multi-region use cases

Interview questions

• Explain the clustered index — why does MySQL InnoDB give you 1 I/O per PK lookup where Postgres gives you 2?
• You're at 10K writes/sec on a single MySQL primary. What breaks first — CPU, disk IOPS, or replication?
• How does Vitess route a query — walk through VTGate, VTablet, and VSchema.
• What's the difference between binlog_format=STATEMENT vs ROW vs MIXED, and when does each matter?
• Your read replica lag is 30 seconds during peak. What are the 3 most likely causes?
• You want to add a new NOT NULL column to a 100 GB table with zero downtime. How do you do it?
• How would you size `innodb_buffer_pool_size` for a workload with 200 GB dataset but 10 GB hot set?
• Explain the difference between REPEATABLE READ (MySQL default) and SERIALIZABLE and when you'd want SERIALIZABLE.