Part 153: MySQL InnoDB Architecture: Buffer Pool, Redo & Undo Logs

Inside MySQL's default ACID-compliant storage engine.

1. The Core Components of InnoDB

[ Application Client ]
         │  SQL Queries
         ▼
[ InnoDB Buffer Pool (RAM) ] ◄── Dirty Pages Flush ──► [ Data Files (.ibd) ]
         │                                                      ▲
         ├────── Redo Log Buffer ──► [ Redo Log (ib_logfile) ] ─┘ (Crash Recovery)
         └────── Undo Log Engine ──► [ Undo Tablespaces ] (Rollback & MVCC)

2. InnoDB Buffer Pool

The primary cache where table data, indexes, and insert buffers live in memory. - Reads first check if the requested 16KB data page is in the Buffer Pool. - Updates modify pages in the Buffer Pool directly (turning them into dirty pages). - Rule of Thumb: On a dedicated database server, allocate 60% to 75% of total system RAM to innodb_buffer_pool_size.

3. Redo Log (Write-Ahead Logging / WAL)

Ensures Durability (the 'D' in ACID): - Changes are written sequentially to the Redo Log on disk before dirty data pages are flushed to .ibd tablespace files. - Sequential disk writes are 100x faster than random page writes. - If the server crashes or loses power, InnoDB replays the Redo Log upon restart to restore committed data.

4. Undo Log

Ensures Atomicity and Isolation (the 'A' and 'I' in ACID): - Records how to reverse incomplete or aborted transactions. - Powers MVCC in MySQL: concurrent readers read the historical row version reconstructed from the Undo Log!

Key Takeaways

• Redo logs guarantee crash recovery; Undo logs guarantee rollbacks and consistent reads.
• Sizing the Buffer Pool properly is the single most impactful tuning parameter in MySQL.

In Part 154, we explore MySQL Binary Logging (Binlog) Formats & Replication!