The worst insert throughput regression I ever caused was a one-line config change. A batch-processing cluster was saturating at around 9,000 single-row inserts per second against hardware I knew could push past 30,000, and the fix that turned up in an internal wiki was to set innodb_autoinc_lock_mode=0 "for safety", because somebody had read that 0 was the most conservative value. Throughput fell to 3,000 inserts per second overnight. The wiki was not lying about conservatism. Mode 0 is conservative in exactly the way a stoplight at every intersection is conservative: nothing crashes, and nothing moves.
The three modes are one of those knobs everyone copies from a runbook without knowing what is being traded. Here is the version I wish someone had handed me: what the AUTO-INC table lock actually serializes, why 8.0 defaults to the interleaved mode 2, what that choice means for statement-based replication and bulk inserts, how to prove AUTO-INC contention in performance_schema, and why the gaps in your IDs are not a bug.
What does the AUTO-INC table lock actually serialize?
It serializes allocation of auto-increment values, not the insert itself. Every insert into a table with an AUTO_INCREMENT column must advance that table's counter, and InnoDB guards the counter with a special internal table lock called AUTO-INC. How long that lock is held is the whole difference between the modes. In mode 0, traditional, every insert statement takes the lock and keeps it until the statement ends, so a 200,000-row INSERT ... SELECT serializes every other insert on that table behind it. In mode 1, consecutive, simple inserts whose row count is known up front — single-row statements and multi-row VALUES lists — skip the table lock entirely and grab their whole block of IDs under a brief mutex; only statements with an unknowable row count, like INSERT ... SELECT and LOAD DATA, still hold the AUTO-INC lock to the end. In mode 2, interleaved, no statement ever takes the table lock: each row pulls the next value under the mutex, even in the middle of a multi-row statement, which is why concurrent statements can receive interleaved, non-consecutive ranges.
Why does MySQL 8.0 default to mode 2?
Because 8.0 also defaults binlog_format to ROW, and row-based binlogging removes the hazard that made interleaved allocation dangerous. Once the binary log records the exact values written rather than the statements, the replica never has to regenerate auto-increment IDs, so the source is free to use the fastest allocation strategy. And it is the fastest: mode 2 is what let a 6-billion-row event table I ran sustain parallel bulk loads without the AUTO-INC lock turning into a global turnstile. Under mode 1, one reporting job's INSERT ... SELECT that took 40 seconds blocked every single-row writer for those same 40 seconds, and the queue backing up behind it looked, from the application side, like a full database outage. Under mode 2 the identical job never touches the table lock.
What breaks on statement-based replication?
Bulk inserts can produce different IDs on the replica, which means wrong data. With binlog_format=STATEMENT the replica re-executes the statement and regenerates auto-increment values itself, and for that to be correct the allocation has to be deterministic. Modes 0 and 1 are deterministic: each statement gets a consecutive block of IDs, and the binlog carries enough information for replay to match. Mode 2 is not — the interleaving on the source depends on thread timing that cannot be reproduced during replay, so a multi-row insert can land on the replica with a different set of IDs, silently disagreeing with the source. The manual is blunt about it: statement-based logging with mode 2 is unsafe for statements that insert multiple rows. If you are pinned to statement-based binlogs because of a legacy consumer, keep mode 1. Everyone else should be on row-based binlogs anyway, which is also the position I argue in binlog row image and size. And notice what mode 2 does to a single INSERT ... SELECT with concurrent writers: the rows of one statement receive non-consecutive IDs. Perfectly legal, and a shock the first time you see it.
How do you prove AUTO-INC contention in performance_schema?
Look for table-level locks with LOCK_MODE = 'AUTO_INC' in performance_schema.data_locks, joined through data_lock_waits to see who is blocking whom. Since 8.0 the AUTO-INC table lock is visible in data_locks with LOCK_TYPE = 'TABLE', which turns the diagnosis into a query instead of a log-reading exercise:
SELECT
r.THREAD_ID AS waiting_thread,
r.OBJECT_SCHEMA,
r.OBJECT_NAME,
r.LOCK_MODE AS waiting_mode,
b.THREAD_ID AS blocking_thread,
b.LOCK_MODE AS blocking_mode
FROM performance_schema.data_lock_waits w
JOIN performance_schema.data_locks r
ON r.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID
JOIN performance_schema.data_locks b
ON b.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID
WHERE r.LOCK_MODE = 'AUTO_INC';
SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME = 'wait/synch/mutex/innodb/autoinc_mutex';SHOW ENGINE INNODB STATUS still tells the same story in its TRANSACTIONS section as "lock mode AUTO-INC waiting". Two traps to avoid. First, AUTO-INC waits are table locks, so they do not appear in the row-lock output people usually screenshot, and they never trigger the deadlock detector; the workflow in reading InnoDB lock waits in data_locks covers the general case, but filter on LOCK_MODE or you will miss these entirely. Second, a hot autoinc_mutex in mode 1 or 2 is usually a symptom of extreme insert concurrency on one table, and the real fix is the mode change or spreading the write load, not more CPU. The performance_schema low-overhead setup note shows how to keep this instrumentation cheap enough to leave on permanently.
Are gaps in AUTO_INCREMENT values a problem?
No, and treating them as one causes real harm. AUTO_INCREMENT generates unique identifiers, not sequence numbers, and gaps are the normal byproduct of that guarantee: rolled-back transactions burn their IDs, failed inserts burn theirs, mode 2 interleaves ranges across statements, auto_increment_increment greater than 1 on multi-primary topologies deliberately skips values, and server restarts used to reset the counter from MAX(id)+1 until 8.0 finally made the counter durable. I have watched teams build retry loops that re-insert failed rows just to "fill" gaps, adding load and deadlocks to fix a cosmetic issue. The numbers that do deserve attention are different: signed versus unsigned ranges and how close you are to exhausting the type, which is the subject of auto-increment exhaustion, and whether anything in the application quietly assumes consecutiveness — ordering by id instead of created_at, for example, breaks subtly once IDs interleave. When finance genuinely needs gapless invoice numbers, build an application-level sequence table that assigns numbers at commit time; asking AUTO_INCREMENT to do that job is asking for lock contention you will meet again at scale.
Where MonPG stands on MySQL
I build MonPG, so the honest line first: MonPG monitors PostgreSQL today, and MySQL support is in active development, not shipped. The signals in this article — AUTO-INC lock waits in data_locks, mutex hot spots in events_waits summaries, insert throughput cliffs after config changes — are exactly what the MySQL work is designed to surface as timelines rather than 2 a.m. surprises. The MySQL monitoring (coming soon) page tracks that work as it lands. Until it ships, the same evidence-first monitoring runs on the PostgreSQL side, and more of these MySQL field notes live on the blog.