Deep Dive Into InnoDB Page Merging and Page Splitting
summary
Explains how InnoDB physically stores pages, shows that page splits happen on inserts and page merges on deletes/updates, and measures ~3x more splits with non‑sequential primary keys (process id or reversed UUID) versus auto‑increment. Demonstrates that merges/splits take exclusive latches, hurt spindle and SSD I/O, and that maintenance and schema changes (partitioning, sequential keys) cut fragmentation; shows before/after saving ~800 pages. Includes merge threshold 50% (8KB) and monitor DB_METRICS: PURGE_PAGE_MERGE, INNODB_PAGE_SPLITS for visibility.
AI-written summary · about
chapters (13)
- 0:00Intro
- 0:44Why Care
- 1:49Tables
- 3:05Data Structure
- 4:40Record Length
- 6:56Table Definition
- 11:15Merging Threshold
- 12:44Merging Threshold Example
- 15:08Merging Rule
- 16:56Page Splitting
- 19:45Page Dislocation
- 25:03Impact
- 27:07Exclusive Latch
related talks
34mMySQL and InnoDB Performance for the Rest of Us
48mA Deep Dive Into Database Internals
53mIndexing Internals for Developers & DBAs
47mMySQL Indexing Best Practices
36mMySQL & InnoDB Fundamentals and Configuration
31mDesigning Effective Schema for Innodb- 53mIndexing Internals for Developers & DBAs
52mMySQL replication deep dive, and ideas for PostgreSQL