Summary: in this tutorial, you will learn about MySQL InnoDB architecture including its in-memory and on-disk structures.
InnoDB is a default storage engine for MySQL, designed with a focus on reliability and performance.
The following picture illustrates the InnoDB architecture of MySQL 8.0:
The InnoDB architecture has two main kinds of structures:
- In-memory structures
- on-disk structures
In-memory structures
The in-memory structures are responsible for managing and optimizing the storage and retrieval of data. The in-memory structures include:
- Buffer pool
- Change buffer
- Adaptive hash index
- Log buffer
Buffer pool
The buffer pool caches frequently accessed data. The buffer pool allows MySQL to read and write data directly in memory, reducing expensive I/O accesses, and significantly improving query performance.
MySQL allows you to configure the size of the buffer pool by allocating a portion system’s memory for caching. If you have a dedicated MySQL server, you can allocate up to 80% of physical memory to the buffer pool for optimal performance.
To optimize the MySQL server performance, you can adjust the buffer pool size, the number of buffer pool instances, and the buffer pool chunk size.
Change Buffer
The change buffer is in charge of caching changes to the secondary index pages when these pages are not in the buffer pool.
When you execute an INSERT, UPDATE, or DELETE statement, it changes the data of the table and the secondary index pages. The change buffer caches these changes when the relevant pages are not in the buffer pool to avoid time-consuming I/O operations.
Adaptive hash index
The adaptive index is an in-memory structure to optimize the performance of certain read options. It is designed to speed up access to frequently queried index pages by providing a quick in-memory lookup mechanism.
Log Buffer
The log buffer is a memory area that holds the changes to be written to transaction logs.
The log buffer improves performance by writing logs to memory before periodically flushing them to the redo log on disk.
The default size of the log buffer is often sufficient for most applications. But if you have a write-intensive application, you can configure the log buffer size to enhance the MySQL server performance.
On-disk structures
InnoDB storage engine uses the on-disk structures to store data permanently on disks. These structures ensure data integrity, offer efficient storage and support transactional features.
The on-disk structures include:
- System tablespace
- File-per-table tablespaces
- General tablespaces
- Undo tablespaces
- Temporary tablespaces
- Doublewrite buffer
- Redo log
- Undo logs
System tablespace
The system tablespace serves as the storage area for the change buffer.
InnoDB uses one or more data files for the system tablespace. By default, MySQL creates the ibdata1 file in the data directory.
The innodb_data_file_path startup option determines the size and number of system tablespace data files.
File-per-table tablespaces
The file-per-table tablespaces store the actual data of the InnoDB tables.
When you create new tables using the InnoDB storage engine, InnoDB stores each table and its associated indexes in a file-per-tablespace file with the .ibd extension.
For example, if you create a table called tbl_name, InnoDB will create a corresponding file-per-tablespace data file as tbl_name.idb in the data directory.
General tablespaces
General tablespaces are shared tablespaces that can store multiple tables. General table spaces are created using the CREATE TABLESPACE statement.
General tablespaces help reduce the duplication of tablespace metadata in memory when multiple tables share the same general tablespace. Therefore, general tablespaces have potential memory advantages in comparison with file-per-table tablespaces.
Undo tablespaces
The undo tablespace stores undo logs that contain the information for undoing the latest changes by a transaction.
MySQL has two default undo tablespace files innodb_undo_001 and innodb_undo_002.
Temporary tablespaces
When you create temporary tables, InnoDB stores them in the temporary table spaces more specifically session temporary tablespaces.
If you make changes to the temporary tables, InnoDB stores rollback segments for changes in the global temporary tablespace.
Doublewrite buffer
InnoDB uses the Doublewrite Buffer to store pages that have been flushed from the buffer pool before their actual writing to InnoDB data files.
The Doublewrite Buffer allows InnoDB to retrieve a reliable page copy for recovery in case a storage issue occurs.
Redo log
A redo log is a disk-based data structure that stores the changes made to tables. InnoDB uses the redo log during crash recovery to correct the data written by incomplete transactions.
For example, when you execute an SQL statement that changes the database such as INSERT, UPDATE, and DELETE, the redo log stores the requests in a redo log file.
If a crash occurs, MySQL replays the modification in the redo log that did not finish before accepting the connection.
InnoDB uses a set of redo log files (ib_logfile0, iblogfile1, …) to store the changes to the table data.
Undo logs
The undo logs store the information needed for rollback operations.
For example, if you execute a transaction and decide to roll it back, InnoDB will utilize the undo log to reverse the changes made during that transaction.
InnoDB uses a set of undo log files, often named undo_001.ibd, udo_002.ibd, and so on to store the logs.
Summary
- InnoDB architecture includes in-memory and on-disk structures.
- InnoDB uses a buffer pool to cache frequently accessed data, thereby, enhancing performance by allowing direct in-memory access to data and reducing disk I/O operations.
- InnoDB uses on-disk structures such as doublewrite buffers, undo logs, and redo logs to maintain data integrity and facilitate recovery, ensuring database consistency.
