Part 12 · 4 chapters · ~20 min

Interview & expert mode

The module's closing argument. Sixty questions ordered by depth, with answers written the way you would actually say them; ten real production failures traced to their mechanism; and the MySQL-versus-Postgres comparison defended properly rather than as a list of features.

118

Sixty questions, ranked by depth

Answer each out loud before opening it. The ones you cannot answer name the chapter to reread.

Storage and indexing

Transactions and concurrency

Durability and recovery

Optimizer and performance

Memory, buffer pool, configuration

Replication and operations

Judgment

119

Ten war stories

Each of these is a real failure pattern. Read the symptom, try to name the mechanism before reading on.

01 The ALTER that took the site down

symptomAn ALTER TABLE on a small table hangs. Within a minute every query on that table is queued and the API is timing out.

mechanismAn idle transaction held a shared MDL. The ALTER queued for an exclusive one, and because MDL requests are FIFO, every subsequent query queued behind the ALTER.

fixKill the idle transaction — not the ALTER. Then set lock_wait_timeout so DDL fails fast, and audit the app for transactions left open across network calls.

chapter52

02 The UUID primary key

symptomWrite throughput was fine for months, then degraded sharply over two weeks with no deploy. Disk IO doubled.

mechanismThe table's CHAR(36) UUIDv4 PK grew past the buffer pool. While the index fit in memory, random inserts were cheap; once it did not, every insert became a disk read to fetch its target page. The cliff arrives without warning.

fixMigrate to BINARY(16) with UUIDv7 or a Snowflake id. Short term, enlarge the buffer pool to buy time.

chapter31

03 The transaction that filled the disk

symptomQueries slowed from 2ms to 2s across the whole database over an hour, then the disk filled and the server stopped.

mechanismA transaction opened a snapshot, then blocked on a hanging HTTP call to a payment provider. Purge could not advance past its read view, so undo accumulated. Reads of hot rows began walking chains thousands of versions deep.

fixKill the idle transaction. Then: never hold a transaction across a network call, and alert on trx_rseg_history_len.

chapter45

04 The periodic 30-second freeze

symptomEvery twenty minutes, all writes stopped for ~30 seconds, then resumed. No slow queries, no locks, no deadlocks.

mechanismCheckpoint age reached ~95% of a 100MB redo log. InnoDB entered sync flush, blocking all writes until space was reclaimed.

fixRaise innodb_redo_log_capacity to several GB — dynamic since 8.0.30 — and raise innodb_io_capacity to match the NVMe hardware.

chapter62, 73

05 The OOM during the traffic spike

symptomServer ran perfectly for months, then was OOM-killed during a promotion. Restarted, killed again 10 minutes later.

mechanismSomeone had raised sort_buffer_size and join_buffer_size to 8MB globally "for performance". At normal concurrency this was invisible; at 900 connections each running multi-join queries it exceeded RAM alongside the buffer pool.

fixReturn per-connection buffers to near-defaults and raise them per-session where genuinely needed. Recompute the worst-case total before any such change.

chapter75

06 The check-then-insert deadlock storm

symptomDeadlock rate climbed with traffic. The transactions involved inserted different rows, so it seemed impossible.

mechanismA "does this exist?" SELECT ... FOR UPDATE on a missing key took a gap lock. Gap locks are mutually compatible, so concurrent workers all held the same gap — then each insert needed an insert intention lock conflicting with the others'.

fixRemove the check: INSERT ... ON DUPLICATE KEY UPDATE. Optionally READ COMMITTED to remove gap locks entirely.

chapter55, 60

07 The replica that was never behind

symptomMonitoring showed Seconds_Behind_Source = 0 for six hours. Then failover promoted a replica missing six hours of data.

mechanismThe IO thread had died. With no events arriving there was nothing to be behind on, so the metric read 0 — total failure indistinguishable from perfect health.

fixAlert on Replica_IO_Running and Replica_SQL_Running directly, and measure lag with a heartbeat table rather than the built-in counter.

chapter95

08 The index that stopped working

symptomOne query was fast in staging and scanned 4M rows in production. Identical schema, identical index, identical plan shape expected.

mechanismThe production table had been created before a charset migration and carried a different collation from the joined column. The implicit conversion made the predicate non-sargable, silently disabling the index. No error, no warning.

fixAudit for mixed collations, then CONVERT TO CHARACTER SET utf8mb4. Add a schema check to CI.

chapter106

09 The backup nobody had restored

symptomA bad migration dropped a column. The nightly mysqldump existed. Restoring it took 14 hours, most of it rebuilding indexes.

mechanismLogical backups reinsert every row and rebuild every index on restore. Nobody had ever measured restore time, only backup success.

fixMove to physical backups (XtraBackup) with binlogs for PITR, and schedule a monthly timed restore to a scratch host. RTO is the metric, not backup duration.

chapter103

10 The 8.4 upgrade that broke every login

symptomAfter upgrading to 8.4, the application could not connect. "Access denied" for every account, including freshly created ones.

mechanism8.4 disables mysql_native_password by default. The driver could only speak that plugin, and the negotiation failure surfaces as an access-denied error rather than something informative.

fixUpgrade the driver to one supporting caching_sha2_password. The tell that distinguishes this from a credentials problem: it fails for every account, including new ones.

chapter8

120

MySQL versus Postgres, defended properly

Chapter 7 introduced the two foundational choices. Now the full argument, in both directions, because being able to argue the other side is what makes the position credible.

The two choices everything descends from

Where the row lives. InnoDB clusters rows into the PK B+tree. Postgres uses an unordered heap with all indexes pointing into it.
Where old versions go. InnoDB puts them in a separate undo log and rebuilds on read. Postgres keeps them in the heap.

Consequence	InnoDB	PostgreSQL
PK choice impact	Severe — key order is physical order	Mild — heap insert order is independent
Secondary index size	Inflated by PK width	Fixed-size tuple pointer
UPDATE cost	In-place; old version to undo	New tuple; all indexes updated unless HOT applies
Reading old versions	Costs a chain walk	Same cost as current
Defining ops problem	Undo history under long transactions	Bloat and transaction id wraparound
PK range scan	Excellent — rows are physically adjacent	Good, but heap access may be scattered

Where Postgres is genuinely ahead

The optimizer. Merge join, parallel query, better statistics including multivariate extended statistics, and generally sounder plan choices.
Types and extensibility. JSONB with GIN indexing, arrays, ranges, custom types and operators, and pluggable index access methods — which is how PostGIS and pgvector exist at all.
One WAL. No dual-log XA overhead on every commit.
Correctness defaults. Stricter behaviour historically; MySQL only closed much of that gap with 5.7's strict mode.
CTEs and window functions arrived a decade earlier and are more mature.

Where MySQL is genuinely ahead

Operations at scale. The ecosystem — Vitess, ProxySQL, gh-ost, Orchestrator, XtraBackup — is more mature for horizontal scale and online change than the Postgres equivalents.
Replication ergonomics. GTID-based failover is simpler than managing Postgres replication slots, and logical replication arrived in Postgres much later.
No VACUUM. Postgres's autovacuum tuning and wraparound risk are a genuine, permanent operational burden.
Clustered locality. When access is primary-key-ordered — multi-tenant data keyed by tenant, time-ordered events — the clustered index is a real, structural win.
Connection cost. Threads are cheaper than processes, so MySQL tolerates more direct connections before a pooler is mandatory.

how to actually answer the question

Name the two structural choices, derive two or three consequences from them, then state each system's characteristic operational burden — undo history versus bloat and wraparound. Finish by saying the decision usually turns on team experience and operational ecosystem rather than on the engines, because both are excellent.

What signals depth is not the verdict but the derivation. Anyone can list features; deriving the behaviour from the storage layout is the thing few candidates do.

121

Where MySQL is going

The release model changed in 2023 and it matters for what you run.

Line	Support	Run it?
8.0	Extended support through 2026	Still the most deployed. Fine, but plan the move.
8.4 LTS	Long-term, 8 years	The target. Everything in this module assumes it.
9.x innovation	Short — superseded quarterly	No, not in production. Watch for what lands in the next LTS.

Recent and incoming: a vector type with distance functions for embedding workloads (9.x); JavaScript stored programs (Enterprise); continued optimizer work on hash join and histograms; and ongoing HeatWave integration on Oracle Cloud, which is a separate analytics engine rather than a change to InnoDB.

the honest forecast

MySQL is a mature system, and InnoDB's fundamentals — B+trees, MVCC via undo, redo plus binlog — are not going to change. That is good news for you: the contents of this module have a very long half-life. The parts that will shift are the operational surface (cloud-managed defaults, Vitess-style sharding, vector search) rather than the internals you have just learned.

You have finished the module

121 chapters, from Monty's ISAM hack in 1994 to a storage engine you wrote yourself. What you should now be able to do, concretely:

Explain any MySQL behaviour by deriving it from page layout, the B+tree, or the storage engine boundary — rather than recalling a rule.
Diagnose the four incident classes that account for most MySQL outages: MDL queues, long transactions, checkpoint stalls, and per-connection memory.
Read a deadlock, an EXPLAIN ANALYZE tree, and an optimizer trace, and say precisely what each is telling you.
Argue MySQL versus Postgres from mechanism, in both directions.
Point at a B+tree implementation you wrote, that splits correctly and survives kill -9.

The SQL module is the natural next step — it takes the language itself to the same depth, and its execution chapters connect directly back to Part 8.