InnoDB Architecture: Buffer Pool, Redo Log, Undo Log0%

Database Maintenance: CHECK, ANALYZE & OPTIMIZE TABLE

InnoDB Architecture: Buffer Pool, Redo Log, Undo Log

Advanced15 min read•Updated: 2026-09-12

InnoDB Architecture: Buffer Pool, Redo Log, Undo Log

To optimize, tune, and troubleshoot MySQL at an enterprise scale, you must understand the internal memory and storage architecture of the InnoDB Storage Engine.

InnoDB's architectural design guarantees ACID compliance and maximizes throughput via three primary subsystems: the Buffer Pool, the Redo Log, and the Undo Log.

High-Level Architecture Diagram

1                    [ Client Applications ]
2                              │ (SQL Queries)
3                              ▼
4┌─────────────────────────────────────────────────────────────┐
5│                    INNODB MEMORY ENGINE                     │
6│                                                             │
7│   ┌─────────────────────────────────────────────────────┐   │
8│   │                 INNODB BUFFER POOL                  │   │
9│   │  [ Data Pages ]  [ Index Pages ]  [ Adaptive Hash ] │   │
10│   │  [ Dirty Pages (modified in memory) ]               │   │
11│   └──────────────────────────┬──────────────────────────┘   │
12│                              │                              │
13│   [ Log Buffer ]             │ (Background Flusher Thread)  │
14└──────────┬───────────────────┼──────────────────────────────┘
15           │                   │
16           ▼                   ▼
17┌─────────────────────────────────────────────────────────────┐
18│                     INNODB DISK STORAGE                     │
19│                                                             │
20│   [ Redo Log (ib_logfile) ] ──> WAL: Crash Recovery         │
21│   [ Undo Log (Rollback Segments) ] ──> MVCC & Rollbacks     │
22│   [ Tablespace (.ibd files) ] ──> Persistent Data & Indexes │
23│   [ Doublewrite Buffer ] ──> Guards against Torn Pages      │
24└─────────────────────────────────────────────────────────────┘

1. The InnoDB Buffer Pool: The Heart of Performance

The Buffer Pool is the dedicated RAM memory area where InnoDB caches data pages and index pages.

When a row is read, InnoDB fetches its entire 16 KB page from disk into the Buffer Pool. Subsequent reads occur directly in RAM at nanosecond speeds!
Dirty Pages: When a query updates a row, InnoDB modifies the page in RAM immediately (making it "dirty"). It does not write to the .ibd table disk file immediately; background threads flush dirty pages to disk asynchronously.

Production Rule: On a dedicated MySQL database server, set innodb_buffer_pool_size to 60% to 80% of total system RAM!

2. The Redo Log: Write-Ahead Logging (WAL) & Durability

If MySQL crashes while dirty pages are sitting in the Buffer Pool, how is data not lost? Answer: The Redo Log.

Before any dirty page can be modified in RAM, the change is written sequentially to the Log Buffer and flushed to the Redo Log on disk (ib_logfile0, ib_logfile1):

Sequential disk writes are fast ($O(1)$ append).
Crash Recovery: Upon server restart after a crash, InnoDB reads the Redo Log and replays any committed changes that had not yet been flushed to the tablespace files.

Flush Behavior (`innodb_flush_log_at_trx_commit`):

1 (Default / Full ACID): Redo log is flushed to disk on every transaction commit. Maximum safety.
2: Redo log is written to OS cache on commit and flushed to disk once per second. High performance, risk of losing 1 second of transactions on power failure.
0: Written and flushed once per second. Maximum performance, highest risk.

3. The Undo Log: Atomicity & MVCC

The Undo Log stores the inverse of modifications:

1
Rollback Support: If a transaction issues ROLLBACK, InnoDB uses the undo log to revert changed rows back to their original values.
2
Multi-Version Concurrency Control (MVCC): When Transaction B reads rows modified by uncommitted Transaction A, Transaction B reads the older row versions reconstructed directly from the Undo Log!

4. The Doublewrite Buffer: Guarding Against Torn Pages

Operating system file blocks are typically 4 KB, but InnoDB pages are 16 KB. If a power outage occurs midway through writing a 16 KB page (writing only 8 KB), the page becomes corrupted (a torn page).

The Doublewrite Buffer writes pages to a contiguous memory and disk buffer before writing them to actual tablespace files. If a crash tears a page, InnoDB restores the intact page from the doublewrite buffer during recovery.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What percentage of total server RAM is recommended for innodb_buffer_pool_size on a dedicated MySQL server?

Question 2

What is a "Dirty Page" in InnoDB memory architecture?

Question 3

How does the Redo Log ensure Durability after an unexpected power outage?

Question 4

What dual purpose does the Undo Log serve in MySQL InnoDB?

Question 5

What catastrophe does the InnoDB Doublewrite Buffer prevent?

Next Lesson

Database Maintenance: CHECK, ANALYZE & OPTIMIZE TABLE

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Partition Pruning Mechanics & Horizontal Scaling	Database Maintenance: CHECK, ANALYZE & OPTIMIZE TABLE

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Database Maintenance: CHECK, ANALYZE & OPTIMIZE TABLE

InnoDB Architecture: Buffer Pool, Redo Log, Undo Log

Advanced15 min read•Updated: 2026-09-12

Study Materials Cheatsheet

InnoDB Architecture: Buffer Pool, Redo Log, Undo Log

To optimize, tune, and troubleshoot MySQL at an enterprise scale, you must understand the internal memory and storage architecture of the InnoDB Storage Engine.

InnoDB's architectural design guarantees ACID compliance and maximizes throughput via three primary subsystems: the Buffer Pool, the Redo Log, and the Undo Log.

High-Level Architecture Diagram

1                    [ Client Applications ]
2                              │ (SQL Queries)
3                              ▼
4┌─────────────────────────────────────────────────────────────┐
5│                    INNODB MEMORY ENGINE                     │
6│                                                             │
7│   ┌─────────────────────────────────────────────────────┐   │
8│   │                 INNODB BUFFER POOL                  │   │
9│   │  [ Data Pages ]  [ Index Pages ]  [ Adaptive Hash ] │   │
10│   │  [ Dirty Pages (modified in memory) ]               │   │
11│   └──────────────────────────┬──────────────────────────┘   │
12│                              │                              │
13│   [ Log Buffer ]             │ (Background Flusher Thread)  │
14└──────────┬───────────────────┼──────────────────────────────┘
15           │                   │
16           ▼                   ▼
17┌─────────────────────────────────────────────────────────────┐
18│                     INNODB DISK STORAGE                     │
19│                                                             │
20│   [ Redo Log (ib_logfile) ] ──> WAL: Crash Recovery         │
21│   [ Undo Log (Rollback Segments) ] ──> MVCC & Rollbacks     │
22│   [ Tablespace (.ibd files) ] ──> Persistent Data & Indexes │
23│   [ Doublewrite Buffer ] ──> Guards against Torn Pages      │
24└─────────────────────────────────────────────────────────────┘

1. The InnoDB Buffer Pool: The Heart of Performance

The Buffer Pool is the dedicated RAM memory area where InnoDB caches data pages and index pages.

When a row is read, InnoDB fetches its entire 16 KB page from disk into the Buffer Pool. Subsequent reads occur directly in RAM at nanosecond speeds!
Dirty Pages: When a query updates a row, InnoDB modifies the page in RAM immediately (making it "dirty"). It does not write to the .ibd table disk file immediately; background threads flush dirty pages to disk asynchronously.

Production Rule: On a dedicated MySQL database server, set innodb_buffer_pool_size to 60% to 80% of total system RAM!

2. The Redo Log: Write-Ahead Logging (WAL) & Durability

If MySQL crashes while dirty pages are sitting in the Buffer Pool, how is data not lost? Answer: The Redo Log.

Before any dirty page can be modified in RAM, the change is written sequentially to the Log Buffer and flushed to the Redo Log on disk (ib_logfile0, ib_logfile1):

Sequential disk writes are fast ($O(1)$ append).
Crash Recovery: Upon server restart after a crash, InnoDB reads the Redo Log and replays any committed changes that had not yet been flushed to the tablespace files.

Flush Behavior (`innodb_flush_log_at_trx_commit`):

1 (Default / Full ACID): Redo log is flushed to disk on every transaction commit. Maximum safety.
2: Redo log is written to OS cache on commit and flushed to disk once per second. High performance, risk of losing 1 second of transactions on power failure.
0: Written and flushed once per second. Maximum performance, highest risk.

3. The Undo Log: Atomicity & MVCC

The Undo Log stores the inverse of modifications:

1
Rollback Support: If a transaction issues ROLLBACK, InnoDB uses the undo log to revert changed rows back to their original values.
2
Multi-Version Concurrency Control (MVCC): When Transaction B reads rows modified by uncommitted Transaction A, Transaction B reads the older row versions reconstructed directly from the Undo Log!

4. The Doublewrite Buffer: Guarding Against Torn Pages

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What percentage of total server RAM is recommended for innodb_buffer_pool_size on a dedicated MySQL server?

Question 2

What is a "Dirty Page" in InnoDB memory architecture?

Question 3

How does the Redo Log ensure Durability after an unexpected power outage?

Question 4

What dual purpose does the Undo Log serve in MySQL InnoDB?

Question 5

What catastrophe does the InnoDB Doublewrite Buffer prevent?

Next Lesson

Database Maintenance: CHECK, ANALYZE & OPTIMIZE TABLE

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Partition Pruning Mechanics & Horizontal Scaling	Database Maintenance: CHECK, ANALYZE & OPTIMIZE TABLE

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

InnoDB Architecture: Buffer Pool, Redo Log, Undo Log

High-Level Architecture Diagram

1. The InnoDB Buffer Pool: The Heart of Performance

2. The Redo Log: Write-Ahead Logging (WAL) & Durability

Flush Behavior (innodb_flush_log_at_trx_commit):

3. The Undo Log: Atomicity & MVCC

4. The Doublewrite Buffer: Guarding Against Torn Pages

Multiple Choice Questions

Database Maintenance: CHECK, ANALYZE & OPTIMIZE TABLE

Related Lessons

Practice Quiz

Loading Page Content...

InnoDB Architecture: Buffer Pool, Redo Log, Undo Log

High-Level Architecture Diagram

1. The InnoDB Buffer Pool: The Heart of Performance

2. The Redo Log: Write-Ahead Logging (WAL) & Durability

Flush Behavior (innodb_flush_log_at_trx_commit):

3. The Undo Log: Atomicity & MVCC

4. The Doublewrite Buffer: Guarding Against Torn Pages

Multiple Choice Questions

Database Maintenance: CHECK, ANALYZE & OPTIMIZE TABLE

Related Lessons

Practice Quiz

Flush Behavior (`innodb_flush_log_at_trx_commit`):

Flush Behavior (`innodb_flush_log_at_trx_commit`):