InnoDB Internals Interview Questions

Row formats COMPACT/DYNAMIC/COMPRESSED change how off-page BLOBs and variable columns overflow from the clustered leaf. DYNAMIC (Barracuda) stores a 20-byte pointer for large BLOBs, keeping more rows per page. COMPRESSED adds a compression page. Antelope REDUNDANT/COMPACT cannot do large prefix indexes the same way. ROW_FORMAT=DYNAMIC is the modern default to know.

File-per-table tablespaces (innodb_file_per_table) put each table in its own .ibd, which lets DROP TABLE return space to the OS and enables TRANSPORTABLE tablespace tricks. ibdata1 still holds undo (unless separate undo tablespaces) and the change buffer. Huge tables in shared ibdata1 cannot shrink after delete. Partitioning then means many .ibd files.

A secondary index lookup is two B-trees: secondary leaf → PK → clustered leaf, unless the query is covering. MVCC may then walk undo if the clustered row version is too new for the read view. Change-buffer merge can happen when the secondary page is first read. That is why a random UUID PK turns the second hop into random IO even with innodb_buffer_pool warm on the secondary.

Compressed row format (Barracuda, KEY_BLOCK_SIZE) compresses pages in the tablespace but keeps decompressed copies in the buffer pool, so RAM savings are smaller than file size suggests. Extra CPU sits on the page cleaner and on reads. innodb_compression_level tunes CPU versus size. For most OLTP, DYNAMIC plus application-level compression of BLOBs is simpler.

The change buffer merge happens when the secondary page is later read; a crash recovery must replay redo that mentions ibuf entries so the merge is not lost or doubled. A replica applying ROW events still hits the change buffer on cold secondary pages. Huge merges after a backup restore show up as IO spikes. You cannot flush the change buffer with FLUSH TABLES.

Tablespace encryption (innodb_encrypt_tables / ENCRYPTION='Y') encrypts pages with a tablespace key wrapped by the keyring key; the buffer pool holds plaintext pages. Redo may contain encrypted payloads depending on version—keyring must be present before crash recovery. Physical backups must include keyring state. GRANT FILE does not decrypt pages; SELECT as an authorized user does.

The read view for MVCC is a list of active transaction IDs; a row's hidden DB_TRX_ID compared with that view plus undo roll_ptr walks older versions. REPEATABLE READ assigns the view at first consistent read; READ COMMITTED rebuilds it per statement. A transaction ID in the future relative to the view is not visible. This is why a long-running SELECT sees yesterday’s balances while writers commit.

The clustered index IS the table: InnoDB stores full rows in PRIMARY KEY B+tree leaves. A secondary lookup finds the PK then the row. A hidden 6-byte GEN_CLUST_INDEX is created if you omit a PK, which is worse than an explicit BIGINT. Wide string PKs bloat every secondary index that stores the PK bookmark.

Secondary indexes store the indexed columns plus the primary key as a bookmark; a lookup that is not covering does two B-tree searches. If the PK is a 36-byte UUID, every secondary index pays that width on every row. Changing a PK value is a delete plus insert in both trees. Covering indexes skip the second tree.

The buffer pool is an LRU of 16KB pages with a midpoint insertion (old/new sublists) so a full table scan does not wipe hot pages. innodb_old_blocks_time keeps scan pages in the old sublist. Multiple pool instances each have an LRU. A 99% hit rate can still hide a hot latch on one instance.

Redo (the WAL) records page changes before the dirty page is flushed; crash recovery replays redo to redo committed work. Undo logs hold old row versions for MVCC and rollback. You need both: redo for durability, undo for readers and ROLLBACK. Losing ib_logfile* without a clean shutdown is why backups copy redo too.

Undo logs hold old row versions for MVCC and rollback; purge later deletes versions no active snapshot needs. A long transaction stalls purge and the history list grows. Secondary-index delete-marks are also purged. SHOW ENGINE INNODB STATUS History list length is the health metric.

The change buffer caches secondary-index updates for pages not in the buffer pool, merging them later when the page is read. It speeds INSERT-heavy workloads whose secondary pages are cold. Unique secondary indexes cannot always use it. Crash recovery must merge or apply the buffered changes consistently with redo.

Purge lag (history list length) grows when a long snapshot or a slow purge thread cannot reclaim undo. A replica with a held dump transaction, or innodb_purge_threads=1 on a delete-heavy primary, is typical. Disk fills in the undo tablespace, not in the user .ibd. Kill the oldest trx; raising purge threads will not help a still-open read view.

The insert buffer was renamed change buffer; it also handles DELETE-MARK and purge of secondary indexes for off-pool pages. Unique checks still need to read the secondary page. Turning innodb_change_buffering=none is a debug move when you suspect merge storms. ibuf merges in InnoDB status show the backlog.

Adaptive flushing watches redo checkpoint age and dirty-page percentage to avoid a sharp flush storm. innodb_adaptive_flushing=OFF plus a tiny redo log is a self-inflicted stall. The page cleaner coordinates with innodb_io_capacity. Foreground threads doing sync flush is the status line you do not want.

The lock manager stores record and gap bitmaps on the heap of the index page plus a hash table of lock structs. That is why locking a range of a secondary index is not “locking the row” in the clustered tree only. Too many lock structs blow innodb_lock_wait or memory. PERFORMANCE_SCHEMA data_locks shows the index name that is actually locked.

Doublewrite: InnoDB writes a page to a sequential buffer then to its real location, so a crash mid-write cannot leave a half-page that redo cannot apply. DETECT_AND_RECOVER is the 8.0.20+ default mode. Atomic write filesystems can use DETECT_ONLY. Skipping doublewrite to win a microbenchmark is how silent corruption appears after power loss.

ibdata1 still holds the data dictionary (on older versions), undo (unless innodb_undo_tablespaces), and the doublewrite/change-buffer remnants depending on version. You cannot delete ibdata1 because some tables are file-per-table. Shrinking ibdata1 requires a dump/restore. 8.0 moved a lot of dictionary data into mysql.ibd.

Mini-transactions (mtr) group redo records that must apply atomically to a page; a crash cannot leave a B-tree page half-split. Nested mtrs latch pages in order to avoid deadlock. This is below SQL transaction granularity—one UPDATE can be many mtrs. Corruption that fails a checksum often started as an incomplete mtr plus a disabled doublewrite.