InnoDB Architecture

Clustered index

InnoDB stores the table itself inside the primary key B-tree. There is no separate heap. Two consequences follow, and they explain much of InnoDB's behaviour:

1. Primary key lookups are one traversal. The row is in the leaf.
2. Secondary index lookups are two. The secondary index leaf holds the primary key, which is then used to find the row.

Therefore the primary key is copied into every secondary index. A 36-byte character UUID costs 36 bytes per entry in every index; an 8-byte BIGINT costs 8.

If no primary key is declared, InnoDB uses the first suitable unique NOT NULL index, or generates a hidden 6-byte row id. The hidden id is a single global counter and becomes an insert bottleneck — always declare a primary key.

Buffer Pool

The buffer pool caches data and index pages. It is the single most important memory setting in MySQL.

innodb_buffer_pool_size = 48G    # ~70-75% of RAM on a dedicated database host
innodb_buffer_pool_instances = 8 # reduces mutex contention on large pools

The conventional 70–75% of RAM applies to a dedicated database server: unlike PostgreSQL, InnoDB does not rely on the OS page cache, so giving it the memory directly is correct. On a shared host, or in a container with a memory limit, size it well below the limit — per-connection buffers, the redo log buffer and the operating system all need room too.

Measure whether it is large enough:

SELECT variable_name, variable_value
FROM performance_schema.global_status
WHERE variable_name IN ('Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads');
-- hit ratio = 1 - (reads / read_requests)

Sustained physical reads mean the working set exceeds the pool. SHOW ENGINE INNODB STATUS shows the same in its BUFFER POOL section, along with eviction and read-ahead behaviour.

The pool uses a modified LRU with a young and an old sublist, so a large scan does not immediately evict the hot working set. innodb_old_blocks_time controls how long a page must survive before being promoted.

Redo log

The redo log makes committed transactions durable and drives crash recovery.

innodb_redo_log_capacity = 8G          # MySQL 8.0.30+; replaces innodb_log_file_size
innodb_flush_log_at_trx_commit = 1     # 1 = flush and sync at every commit

A redo log that is too small forces aggressive flushing and produces write stalls under load. Too large lengthens crash recovery.

Undo log and MVCC

Previous row versions live in undo tablespaces so readers can reconstruct their snapshot. A long-running transaction holds the read view open, so undo cannot be purged and the history list grows.

SELECT count FROM information_schema.innodb_metrics
WHERE name = 'trx_rseg_history_len';

A history list length in the millions means the purge thread is behind — typically caused by a forgotten open transaction. The symptoms are growing disk usage and progressively slower reads, the InnoDB equivalent of PostgreSQL bloat.

Doublewrite buffer

InnoDB writes pages twice — once to the doublewrite buffer, once to the tablespace — so a torn page during a power failure can be recovered. Disable it only on storage that guarantees atomic writes at the page size, and know that being wrong means undetectable corruption.