InnoDB Architecture in MySQL

The simple InnoDB data flow

When query to update data, we will have 2 flows:

• Flush to Redo Log File: To reduce the size of the redo log, InnoDB selectively logs changes rather than entire pages into a memory buffer known as the log buffer.
• These changes are then flushed to the redo log file located on the disk. By default, the redo log compires at least two files (ib_logfile0, ib_logfile1).
• Redo log files are managed in a circular manner, so once the last redo log file in the sequence is filled, InnoDB loops back to the first redo log file to begin writing again, creating a continous cycle of redo log usage.
• Write to buffer pool: InnoDB write changes here, marking them as "Dirty", before flushing them through the doublewrite buffer and invoking "fsynced" to transfer them to the files on general tablespace.

In-memory Structures:

Buffer Pool: is divided into pages that can potentially hold multiple rows and servers as a cache for storing frequently accessed data and index pages, which significantly reduces the need to read from disk.

Besides that, buffer pool use a variation of the least recently used (LRU) algorithm to manage and remove outdated data.

Log buffer: is utilized to record changes executed on the database through Data Manipulation Language (DML) statements.

Change buffer: is in charge of caching changes to the secondary index pages when these pages are not in the buffer pool.

The buffered changes, which may result from INSERT, UPDATE, or DELETE operation (DML), are merged later when the pages are loaded into the buffer pool by other read operation.

Adaptive hash index: is a feature that automatically creates and maintains hash indexes for frequently accessed data fields, enhancing SELECT query performance.

It dynamically scans hash tables to locate data in memory instead of traditional tree-based index searches, reducing query time and improving efficiency.

On-disk Structrure:

System Tablespace (ibdata1): created during setup of the database to store the internal system information, such as: user privileges and information about the database structure(tables, filed, constrains).

File-Per-Table Tablespaces: with this configuration, each table has its own .ibd file, making it easier to manage and optimize individual tables.

General Tablespaces: These tablespaces provide flexibility by allowing multiple tables to share the same storage file. They are useful for organizing and optimizing storage for related tables.

Undo Tablespaces: These are used to store undo logs, which are essential for transaction rollback and crash recovery. They ensure that any uncommitted changes can be reversed if needed.

Redo Log: The Redo Log records all changes to the database. In case of a crash, InnoDB uses the Redo Log to restore the database to its last consistent state.

Doublewrite Buffer: This buffer helps prevent data corruption by writing data to a temporary storage area before it is written to the final location on disk.

Reference:

[1]. Tr?n Qu?c Huy (May 16, 2024) Understand the entire MySQL Database in 1 hour 42 minutes [link: here]

[2]. Kh??ng Ph?m (September 21, 2022) Basic factors to know when tuning InnoDB in MySQL [link: here]

[3]. Mysql document (version 8.3) Chapter 17 The InnoDB Storage Engine [link: here]