Cruxly › System design › Databases by Name: Internals and Terms

MySQL and InnoDB: locks, buffer pool, replication and Vitess

the clustered-index engine behind Slack, Shopify and GitHub, and the knobs that decide whether it holds

This lesson goes inside MySQL with the InnoDB engine: the locks you can name, how the buffer pool decides what stays in memory, how the redo log turns into write stalls, how replication and automatic failover can lose data, and how Vitess and Shopify's pods spread one MySQL fleet across many shards. It builds on Isolation levels, in depth, Storage engines: B-trees, LSM-trees, row vs column and Scaling Postgres and MySQL before you leave them, and gives the defaults and the production stories.

InnoDB locks you can name: record, gap, next-key, insert intention

At REPEATABLE READ, InnoDB locks ranges, not just rows. The behaviour table by isolation level is in Isolation levels, in depth. This section names the lock types so you can read a deadlock report (MySQL 8.4 reference manual, InnoDB locking).

• Record lock: a lock on an index record. SELECT ... WHERE c1 = 10 FOR UPDATE stops other sessions from inserting, updating or deleting the row with c1 = 10.
• Gap lock: a lock on the gap between index records, or before the first or after the last. Its job is to stop inserts into the gap. Gap locks do not conflict with each other, so two sessions can hold the same gap lock at once.
• Next-key lock: a record lock plus a gap lock on the gap before the record. This is the default at REPEATABLE READ for searches and index scans, and it is how InnoDB blocks phantom rows.
• Insert intention lock: a kind of gap lock set by an INSERT before it inserts. Inserts into the same gap at different positions do not block each other, which is why concurrent inserts mostly work. A gap lock held by a reader, though, blocks all of them.

The classic production deadlock. Because gap locks do not conflict, this happens: two sessions each run SELECT ... FOR UPDATE over the same empty range (both get a gap lock, no conflict), then each tries to INSERT into it. Each insert needs an insert intention lock, which conflicts with the other session's gap lock. Each waits for the other and InnoDB rolls one back. No row existed to lock, yet the sessions deadlocked.

A range scan blocks a range. A batch job that does FOR UPDATE over a date range holds next-key locks across the whole scanned range, so a checkout flow inserting into that range waits until the batch commits. The surprise is that the data being inserted does not exist yet.

READ COMMITTED as the escape hatch. At that level gap locking is disabled for searches and index scans; it is kept only for foreign-key and duplicate-key checks (MySQL 8.4 docs). That removes the gap-lock deadlock above at the cost of phantoms being possible again. The source advice is to pair it with row-based binary logging. This is a trade-off, not a free fix: say which anomaly you are now accepting.

Where the batch job belongs. Process in small ranges with a primary-key bound, commit between batches, and keep the lock-wait timeout in mind so a stuck job fails instead of piling up. Arithmetic with illustrative numbers: a job locking 1,000,000 rows in one transaction holds those locks for the whole run; ten thousand batches of 100 rows hold each lock for a fraction of a second.

:::key What experts say in the room "Repeatable Read in InnoDB locks ranges, not rows." The number is two ingredients: next-key equals record plus gap. The failure is a batch FOR UPDATE that blocks checkout inserts and deadlocks. What a senior checks: the latest deadlock section of SHOW ENGINE INNODB STATUS, the lock-wait timeout, which queries take range locks at REPEATABLE READ, and how many rows each batch transaction touches. :::

The buffer pool: sizing, midpoint insertion, scan resistance

The buffer pool is InnoDB's cache of table and index pages, and sizing it is the most argued knob. innodb_buffer_pool_size defaults to 128 MB (134,217,728 bytes) in the MySQL 8.4 docs. On a dedicated database server, setting it to up to about 80 percent of RAM is common guidance in the docs. It can be changed while the server runs, and InnoDB allocates about 10 percent more than the setting for control structures. Arithmetic, not a measured number: on a 64 GB machine 80 percent is 51.2 GB, and about 10 percent more than that is allocated, so memory planning must include it. Too large and the host swaps; too small and every read is a disk read.

Midpoint insertion keeps scans from wrecking the cache. The pool is kept as one list split into a "young" head and an "old" tail. A new page enters at the midpoint, at the head of the old sublist, and becomes young only if it is accessed again. The old sublist is 37 percent of the pool by default (innodb_old_blocks_pct, which the docs equate to 3/8; 3/8 is 37.5 percent). innodb_old_blocks_time (default 1000 ms) says how long a page must sit in the old sublist after its first access before a second access can promote it. Together they mean a full table scan, where each page is touched in quick succession, does not push the hot working set out. Raising innodb_old_blocks_time gives more protection.

Read-ahead has the same risk. InnoDB prefetches pages in the background, and read-ahead pages are not made young until they are actually used. If the prefetch reads data nobody wants, it still occupies space.

The morning p99 incident. A nightly mysqldump or reporting scan reads a whole table. If the scan-resistance settings are disabled or the dump is large relative to the old sublist, hot pages are evicted overnight and the morning traffic starts against a cold cache, so queries that were memory hits become disk reads and p99 climbs until the pool warms. This is a pattern the mechanism predicts rather than a company's published figure.

:::key Verified October 2026: the change buffer default changed The change buffer caches secondary-index changes for pages not in memory, so random secondary-index writes need not read the page immediately. The 8.4 docs say innodb_change_buffering defaults to none; before 8.4 it was all. Advice from older posts about change-buffer tuning assumed it was on. :::

Why the working set matters more than the total. The pool needs to hold the pages queries touch repeatedly (the hot index levels and hot rows), not the whole database. Whether it does is measured, not guessed: the hit ratio and disk reads per second tell you. A table with a random primary key (see Storage engines: B-trees, LSM-trees, row vs column) spreads inserts across all leaves, which enlarges the working set.

:::key What experts say in the room "Working set versus RAM." The numbers are the 128 MB default, the 80 percent guidance and the 3/8 old sublist. The failure is a nightly dump that evicts the working set and triples morning latency. What a senior checks: buffer pool hit ratio, pages read from disk per second, dirty page percentage, and what the backup and reporting jobs do to the pool. :::

Redo log, flush settings and durability

Redo size is a latency knob as much as a recovery knob. In MySQL 8.4, innodb_redo_log_capacity replaces innodb_log_file_size; the older settings are ignored when capacity is defined. The default is 104,857,600 bytes (100 MiB) with a range up to 512 GiB, per the 8.4 parameter reference. InnoDB keeps 32 redo files, each 1/32 of the capacity, and stores the checkpoint LSN in a file header (MySQL 8.4 redo log docs). Arithmetic: 100 MiB / 32 is 3.125 MiB per file.

The checkpoint link. The redo log holds changes to pages that are still dirty in the buffer pool. InnoDB cannot reuse redo space until the pages it describes are flushed. When redo occupancy is low relative to capacity, flushing is gentle; as it approaches capacity, InnoDB flushes dirty pages more aggressively, and that shows up as write stalls, because foreground writes compete with the flush. So a burst of writes bigger than the redo can absorb becomes a latency spike. Arithmetic, not a benchmark: at a sustained 50 MiB/s of redo generation, a 100 MiB capacity represents about 2 seconds of writes. The docs do not give that number; it shows why the default is small for busy servers.

Redo capacity trade-off. A larger capacity smooths bursts, but crash recovery may need to replay more, and the redo files consume disk. That is the same trade-off as the Postgres checkpoint timeout in the previous lesson.

innodb_flush_log_at_trx_commit controls how much of a commit is on disk when you get the acknowledgement (MySQL 8.4 parameter reference):

Group commit means that at value 1 several transactions can share one flush, so the cost of 1 is less than one flush per transaction. For the analogue in Postgres, see synchronous_commit in ACID, precisely.

Doublewrite buffer. innodb_doublewrite defaults to ON, with DETECT_AND_RECOVER and DETECT_ONLY also valid in 8.4. It protects against torn pages, as explained in Storage engines: B-trees, LSM-trees, row vs column.

Reading the signals. The server exposes the redo checkpoint age; when it climbs toward capacity, stalls follow. The plan to avoid them is to size capacity for your burst, not your average.

:::warn Durability settings are per environment, not per tradition Setting innodb_flush_log_at_trx_commit to 2 on a primary to make a benchmark look better, and then relying on replication for durability, is a decision about data loss that must be written down. :::

:::key What experts say in the room "Redo capacity is a latency knob." The numbers are 32 files, capacity divided by 32 each, and 100 MiB by default. The failure is periodic write stalls under bursts. What a senior checks: the redo checkpoint age against innodb_redo_log_capacity, log waits, and that the flush setting on every primary matches the stated durability promise. :::

Replication: binlog, GTID, semisync and what GitHub learned

A GTID names a transaction globally. A global transaction identifier has the form source_id:transaction_id, and the MySQL 8.4 docs say a transaction committed on the source is applied at most once on a replica, because a replica skips a GTID it already has. That is what makes it safe to point a replica at a different source after a failover: position is by transaction, not by binlog file and offset. Read the auto-positioning section of your version before writing a runbook.

Semisynchronous replication narrows, but does not close, the loss window. The source blocks a commit until at least one replica has written the events to its relay log and flushed them. By default it waits after the binlog sync and before the storage-engine commit (MySQL 8.4 semisync docs). The key point is the documented fallback to asynchronous replication on timeout: if no replica acknowledges in time, the source continues. So semisync bounds the loss of acknowledged commits only while the fallback has not triggered. It is not a guarantee. In the 8.4 manual rpl_semi_sync_source_timeout defaults to 10,000 ms, so after ten seconds without a replica acknowledgement commits quietly continue asynchronously.

Failover is a data-loss decision made by software. Orchestrator is a tool that detects failures and fails over MySQL topologies, and Orchestrator itself runs on the Raft consensus protocol. The risk it carries is promoting a replica that does not have everything the old primary accepted.

GitHub, 21 October 2018. GitHub's post-incident analysis tells it this way. At 22:52 UTC on 21 October 2018, maintenance on 100G optical equipment cut connectivity between the US East Coast network hub and the primary data center for 43 seconds. The Orchestrator node in the East Coast data center began leadership deselection under Raft; the West Coast and public-cloud nodes formed a quorum and failed database clusters over to US West primaries, which the application tier then wrote to. But a brief period of writes had landed on the East Coast primaries that were not replicated: for one busy cluster, GitHub counted 954 writes. When connectivity returned, both coasts had writes the other lacked. GitHub chose, in its words, data integrity over site usability and time to recovery, and the degraded service totalled 24 hours 11 minutes (1,451 minutes, arithmetic). More than five million webhook events and 80 thousand Pages builds queued, and about 200,000 webhook payloads were dropped after exceeding an internal time limit. Follow-ups, per the post, were to stop Orchestrator promoting a primary across regions and to move toward an active/active/active design.

The mechanism to take away. A 43-second partition was short, but replication across a continent lags by some amount; whichever side had the newer writes could not simply be discarded. Reconciling two divergent histories took hours and a deliberate decision to preserve data over speed.

:::key What experts say in the room "Automatic failover is a data-loss decision made by software." The numbers are 43 seconds, 24 hours 11 minutes and 954 writes on one cluster. The failure is split writes on two coasts and hours of reconciliation. What a senior checks: replica lag per replica, whether the orchestrator may promote across regions and why, whether semisync has silently fallen back to async, how unreplicated transactions would be recovered, and binlog retention against the slowest replica or backup. :::

Vitess: keyspaces, vindexes and the cost of a cross-shard query

A vindex maps a column value to a keyspace ID, and the keyspace ID picks the shard. Vitess is a proxy tier over MySQL, so applications talk to it as if it were one database. According to the Vitess vindex docs, every sharded table needs a unique primary vindex, and the value of its column cannot be updated after insertion (an update that keeps the row on the same shard is supported; otherwise delete and re-insert). That is why the sharding key is effectively permanent: it is the design decision.

Vindex types differ by cost. The docs rank them: identity at 0, functional at 1, unique lookup at 10 and non-unique lookup at 20. A functional vindex computes the shard from the value (a hash such as xxhash is the standard primary vindex: it spreads rows evenly but kills range scans on that key). A lookup vindex stores a mapping in a table, so finding a shard costs an extra read, and maintaining it costs extra writes, but it lets you query by a column that is not the sharding key without scattering to every shard. A consistent lookup vindex avoids two-phase commit with ordering and locking, but cannot handle inserting and then updating or deleting the same value inside one transaction. That is the sort of limitation seniors ask about.

Cross-shard queries. A query that carries the sharding key goes to one shard. One that does not is a scatter query to every shard; latency is the slowest shard plus merge work, and load multiplies. The question that decides everything is the percentage of your queries that are single-shard.

Citus co-locates rows with the same distribution value, so related tables sharing that column keep their joins local. Small tables shared by all can be reference tables replicated to every node (Citus data-modelling docs). Both Vitess and Citus force one sharding key per table family; the difference is the base database and where the logic sits.

Slack on Vitess (company's own account, December 2020). Slack's engineering post says it ran MySQL sharded by workspace id on thousands of sharded hosts, and large customers created hot spots. It started migrating to Vitess in 2017. At the time of writing in December 2020, Vitess served 99 percent of query load, at 2.3 million queries per second at peak (about 2 million reads and 300,000 writes), with median latency 2 ms and p99 11 ms. Arithmetic: 2.0M of 2.3M is about 87 percent reads. The post describes plans to shard messages by channel id rather than workspace. It does not state the total shard count or VSchema details, so neither appears here.

Design question, not a company claim: Vitess, Citus or hand-rolled sharding. No company source in this lesson compares them directly, so frame it by cost: a team that already runs MySQL at thousands of hosts gets a proxy that hides routing; a Postgres team gets a co-located extension; a team that does neither owns every edge case. What each team shares is the warning that the sharding key mirrors the biggest customer.

:::key What experts say in the room "Pick the sharding key for the queries, not the schema." The numbers are vindex cost 0 to 1 for single-shard routing against 10 to 20 for lookups. The failure is a key that mirrors the largest customer, as Slack's workspace-id hot spots show. What a senior checks: the percentage of queries that are single-shard, the resharding runbook, lookup table consistency and size, and how the biggest tenant compares to one shard. :::

Shopify pods: moving a tenant between shards without downtime

Shard by tenant, not by row. Shopify's engineering post on balancing MySQL shards describes a pod: an isolated Shopify instance made of one MySQL shard plus Redis, Memcached and other services. An NGINX ingress consults a routing table, kept in a separate unsharded database, to send each shop's requests to its pod. Every shop-owned table carries a shop_id, which is the sharding key, so a whole tenant can be moved as a unit. The routing table is the one place you must keep tiny and replicated, because every request depends on it.

Moving a shop has three phases in Shopify's post: copy and tail, cutover, then update the routing table, serve traffic and prune. Ghostferry (open source) does the copy and binlog tailing; the steps below spell the phases out:

1. Copy rows in batches with SELECT ... FOR UPDATE.
2. Tail the row-based binlog to apply changes that arrive during the copy.
3. When replay lag is down to seconds, cut over: take a lock, finish replay, update the routing table.
4. Clean up the source.

This is the same copy, tail, cutover pattern used by online schema-change tools (see Operating a database: backups, migrations, multi-region, cost).

The cutover is not magic. A zero-downtime move still serialises writes for a moment. The shop mover (the orchestration around Ghostferry) takes a Redis-backed multi-reader single-writer lock: it waits for the readers (in-flight requests for that shop) to drain, takes the writer lock, and finishes. If it cannot acquire the lock in time, the move fails rather than waiting forever. The design makes a failed move cheap and a stuck move impossible. Shopify also runs verifiers before, during and after a move, and the post says the algorithm was modelled in TLA+.

Imbalance is the trigger. The post reports that the least-used and most-used shards varied by almost four times, and that a simulation of the proposed moves projected closer to two times (a forecast, not a measured result). Rebalancing is a scheduled process driven by a measured spread, not a reaction to an alert.

Where it fails. A single tenant larger than a shard cannot be balanced by moving it. For that, the sharding key must change (compare Slack's plan to shard messages by channel id) or the tenant needs its own hardware.

:::key What experts say in the room "Shard by tenant, and keep every tenant movable." The number is a nearly four-times spread that simulation projected down to about two. The failure is a hot tenant bigger than a shard. What a senior checks: per-shard CPU and IOPS spread, the size of the largest tenant against shard capacity, the size and replication of the routing table, and the cutover failure rate. :::

Running MySQL at scale: the senior's list

Alert on the mechanisms, not on CPU. Every section above produced one signal. This section orders them by how incidents unfold. Defaults are from the MySQL 8.4 reference manual read in October 2026; older versions differ.

Purge lag is MySQL's version of the same problem. InnoDB keeps old row versions in undo logs for consistent reads, and purge can only discard a version once no open read view needs it. One long-running transaction therefore makes the undo history grow, which shows as a rising "History list length" in SHOW ENGINE INNODB STATUS. Alert on it next to the oldest transaction age, for the same reason as the oldest xmin in the Postgres lesson.

Version-sensitive traps. innodb_change_buffering is none in 8.4 and was all before. innodb_redo_log_capacity replaces innodb_log_file_size. Buffer pool instance defaults depend on pool size. Re-read the page for your version.

Semisync wait point. rpl_semi_sync_source_wait_point defaults to AFTER_SYNC: the source syncs the binlog, waits for a replica to acknowledge receipt, then commits to the storage engine. AFTER_COMMIT commits first and waits afterwards (8.4 manual). Record both the wait point and the 10-second timeout in your runbook.

The interview-room answer. Asked "how would you scale a MySQL fleet", say it in order: fix the data model and indexes; size the buffer pool and redo for the working set and burst; add replicas with GTID and decide failover policy deliberately; and past one primary, shard by a tenant-like key with a movable unit, either with a proxy like Vitess or with your own routing as Shopify does. Quote one number from each stage and one failure story (GitHub for failover, Slack for sharding key hot spots).

:::warn Do not copy a company's architecture without its constraint Slack's workspace-id sharding hit hot spots for large customers; Shopify's pods work because every table has a shop_id. Check that your data has such a key before borrowing either design. :::

:::key What experts say in the room "Which of these will hurt first, and what will I see?" The numbers are 128 MB, 80 percent, 37 percent, 100 MiB, 43 seconds. The failure is relying on a default written for a small server. What a senior checks: that each signal in the table has an owner, a threshold and a tested response. :::

How to say it in the interview

"At REPEATABLE READ, InnoDB locks ranges with next-key locks, so a FOR UPDATE range scan can block inserts and cause gap-lock deadlocks; READ COMMITTED is the usual escape. The buffer pool defaults to 128 MB and is commonly set to about 80 percent of RAM on a dedicated server, with a midpoint list so a table scan does not evict the working set. For failover I want to know whether the orchestrator may promote across regions, because GitHub's 2018 incident was a 43-second partition that left unreplicated writes on both coasts and 24 hours of degraded service. And past one primary, I would use Vitess, as Slack did, with a sharding key chosen so most queries hit one shard."

Checklist

• Named record, gap, next-key and insert intention locks and read a deadlock report with them
• Decided between REPEATABLE READ and READ COMMITTED for batch-heavy workloads, with row-based binlog
• Sized innodb_buffer_pool_size from the working set, not from the 128 MB default
• Checked what nightly dumps and reports do to the buffer pool and the old-blocks settings
• Sized innodb_redo_log_capacity for the write burst and watch checkpoint age
• Wrote down the innodb_flush_log_at_trx_commit value on every primary and the loss it allows
• Decided whether the orchestrator may promote across regions, and how unreplicated writes are recovered
• Monitored whether semisync replication has fallen back to async, and replica lag per replica
• Measured the percentage of single-shard queries and the largest tenant against shard capacity
• Kept the shard routing table tiny, replicated and covered by a cutover failure-rate alert

Related lessons

• PostgreSQL internals: MVCC cost, VACUUM, wraparound and WAL
• Redis in depth: cluster slots, eviction, persistence and the lock debate
• Cassandra and ScyllaDB: the ring, repair, compaction and Discord's migration
• DynamoDB under the hood: partitions, adaptive capacity, quotas and the 2015 outage
• MongoDB internals: oplog, WiredTiger, shard keys, the balancer and Stripe's DocDB
• Kafka as a log store: ISR, acks, compaction, transactions and LinkedIn scale