Understanding InnoDB Page Size and Its Impact on MySQL Performance

InnoDB pages are the smallest unit of storage in MySQL, and their size directly influences I/O behavior, memory usage, and overall database performance. The article begins by describing how the operating system manages memory in fixed-size pages (typically 4 KB on Linux) and how these pages are cached, read, and written to disk.

It shows how to retrieve the current OS page size using commands such as getconf PAGESIZE on x86 (returns 4096) and ARM (returns 65536), and discusses the trade‑offs of having fewer or more levels in the page‑table hierarchy.

The core of the discussion focuses on MySQL's innodb_page_size parameter. It explains that the first system tablespace file (ibdata1) size depends on this setting, that the value must be defined before initializing the MySQL instance, and that MySQL 5.7 added support for 32 KB and 64 KB pages. Default 16 KB pages work well for most workloads, while smaller pages can benefit OLTP workloads with many small writes, and larger pages can reduce I/O for bulk‑scan queries.

Impact on rows: the maximum row size is roughly half the page size (e.g., ~8 KB for 16 KB pages, ~16 KB for 64 KB pages). Larger pages allow larger rows but may increase contention. Impact on indexes: reducing the page size proportionally reduces the maximum index key length (e.g., 1536 bytes for 8 KB pages, 768 bytes for 4 KB pages).

The article provides a detailed breakdown of page structure, including file header, page header, slot directory, and row header, and shows how to calculate the number of records that can fit in a 16 KB page (approximately 16252 bytes usable after overhead, with each slot occupying 2 bytes). Sample calculations illustrate that a row of 13 bytes could allow up to 16252 records per page.

Empirical testing with sysbench demonstrates that 8 KB pages can reduce CPU usage and improve latency for small rows, while larger rows may suffer from overflow and extra I/O. Hardware considerations such as SSD or PCIe storage are also mentioned.

In conclusion, the article recommends selecting either 16 KB or 8 KB page sizes for production, based on factors like keeping rows under half the page size, hardware capabilities, and workload characteristics, while also emphasizing broader MySQL performance tuning practices.