In this post, we look at the On-Disk Structure of InnoDB, the main storage engine in MySQL. The components of the InnoDB disk structure are listed below. InnoDB stores all data in logical spaces on disk called tablespaces.
- Tablespace
- Table
- Index
- Doublewrite Buffer
- Redo Log
- Undo Log
Tablespace
A tablespace is the largest logical unit used to store data. Internally, it is organized as Segment → Extent → Page → Row.
MySQL tablespaces fall into 5 types depending on what data they store and how. Let's look at each tablespace.
Tablespace | File |
System Tablespace | ibdata1 |
File-Per-Table Tablespace | table_name.ibd |
General Tablespace | tablespace_name.ibd |
Temporary Tablespace | ibtmp1, temp_1.ibt |
Undo Tablespace | undo_001, undo_002 |
System Tablespace
The System Tablespace is the shared tablespace MySQL uses by default. It is the storage area for the Change Buffer, and if you create tables in the System Tablespace, it can also store table and index data.
📢 In earlier MySQL versions, the System Tablespace contained the InnoDB Data Dictionary (a per-storage-engine data dictionary). MySQL 8.0 integrates a Transactional Data Dictionary that stores information about database objects, so all metadata is stored in the MySQL Data Dictionary as tables, not files. The Doublewrite Buffer, which we cover later, was in the System Tablespace in earlier versions but was moved to separate files starting with MySQL 8.0.20.
The System Tablespace can have one or more data files. By default, a System Tablespace data file named ibdata1 is created in the MySQL data directory. The size and number of System Tablespace data files can be set with the
innodb_data_file_path system variable.File-Per-Table Tablespace
A File-Per-Table Tablespace is a tablespace that manages the data and indexes of a single InnoDB table in its own file.
InnoDB normally creates tables in File-Per-Table Tablespaces. There is no separate step to create the tablespace: if
innodb_file_per_table is ON, the table is created in a File-Per-Table Tablespace. The data file is created in the tablename.ibd format.If you disable the
innodb_file_per_table system variable, tables are created in the System Tablespace.Advantages
- When a table is truncated or dropped, the disk space is returned to the OS.
- Data files of File-Per-Table Tablespaces can be created on a separate storage device for I/O optimization, space management, or backup.
- You can import a table in a File-Per-Table Tablespace from another MySQL instance.
- When
innodb_flush_methodis set toO_DIRECT, using File-Per-Table Tablespaces can improve performance. (Other tablespaces hold multiple tables, so concurrent writes to a single file are not allowed.)
- A File-Per-Table Tablespace can allocate enough space for an individual table to grow. (Shared tablespaces have a 64TB size limit, so table size is limited as well.)
Disadvantages
- Free space can only be used by rows of the same table, which can lead to wasted unused space.
- fsync operations are performed per file. With File-Per-Table Tablespaces, writes to multiple tables are performed on as many data files as there are tables, so the number of fsync operations grows accordingly.
- mysqld must keep an open file handle for each File-Per-Table Tablespace, so having many tables in File-Per-Table Tablespaces can affect performance.
📢 An open file handle is a temporary reference number that the operating system assigns to a file the user has asked to open. The system uses that reference number to call, access, and interact with the file throughout the session, until the user closes the file or ends the system session.
- When you drop a table in a File-Per-Table Tablespace, the Buffer Pool is locked and scanned, so with a large Buffer Pool this takes longer and can delay other work.
General Tablespace
A General Tablespace can be created with the
CREATE TABLESPACE statement. Like the System Tablespace, a General Tablespace is a shared tablespace, so it can store data for multiple tables.Limitations
- An existing tablespace of another type cannot be converted to a General Tablespace.
- Creating a temporary General Tablespace is not supported, and General Tablespaces do not support temporary tables.
- Truncating or dropping tables leaves free space in the data file. This space is not returned to the operating system and can only be used for new InnoDB data.
- Starting with MySQL 5.7.24, General Tablespaces do not support partitioned tables.
Temporary Tablespace
The Temporary Tablespace has changed a lot across MySQL versions. First introduced in version 5.7, it was split into the Session Temporary Tablespace and the Global Temporary Tablespace in version 8.0.
Session Temporary Tablespace
The Session Temporary Tablespace stores user-created temp tables and temp tables created internally by the optimizer (internal temporary tables).
💡 "Temporary Table" is shortened to "Temp Table" here.
📢 Starting with MySQL 8.0.16, the storage engine used for on-disk internal temp tables is always InnoDB. In earlier versions, it was set with the internal_tmp_disk_storage_engine system variable.
When a temp table needs to be created on disk, a tablespace is allocated to the session from the Temporary Tablespace Pool. A session can be allocated at most 2 tablespaces: one for user-created temp tables and one for internal temp tables created by the optimizer.
Session Temporary Tablespace files are created in .ibt format in the path set by
innodb_temp_tablespaces_dir. The Temporary Tablespace space allocated to a session is truncated and returned to the Tablespace Pool when the connection ends. (The Temporary Tablespace Pool is removed on normal shutdown or on initialization after an aborted shutdown, and is recreated when the server starts.)Global Temporary Tablespace
The Global Temporary Tablespace stores rollback segments for changes made to user-created temp tables. The relative path, name, size, and attributes of the Global Temporary Tablespace data file are defined with the
innodb_temp_data_file_path system variable. If no value is set, a single auto-extending data file is created in the innodb_data_home_dir directory, named ibtmp1.The Global Temporary Tablespace is removed on normal shutdown or on initialization after an aborted shutdown, and is recreated each time the server starts.
📢 In MySQL 5.7, the ibtmp1 file also served as the Session Temporary Tablespace. Starting with 8.0, despite its name, it is used as the Global Temporary Tablespace file that stores undo logs for temp tables.
Undo Tablespace
The Undo Tablespace contains undo logs for clustered indexes (information used to undo the latest changes).
An Undo Tablespace consists of one or more undo log files. Before MySQL 5.7, undo lived in the System Tablespace, but it was separated from the System Tablespace starting with 5.7.
When the instance is initialized, two default Undo Tablespaces are created, and their files are created in the location defined by the
innodb_undo_directory system variable. (If it is not defined, they are created in the MySQL data directory.)Starting with MySQL 8.0.14, you can create additional Undo Tablespaces with SQL while the server is running.
Table
InnoDB tables and indexes can be created in the System Tablespace, a File-Per-Table Tablespace, or a General Tablespace. By default, InnoDB tables are created in File-Per-Table Tablespaces. To create an InnoDB table in the System Tablespace, disable
innodb_file_per_table. To create a table in a General Tablespace, use the CREATE TABLE ~ TABLESPACE ~ statement.CREATE TABLE table_name ... TABLESPACE [=] tablespace_name
When a table is created in a File-Per-Table Tablespace, MySQL stores it in a separate file named table_name.ibd in the database directory. When the System Tablespace is used, it is stored in the existing ibdata file.
InnoDB stores every table sorted by its primary key (PK).
Because table data is stored sorted by PK value, secondary indexes use the PK value as a logical address instead of the physical address of the data. This clustered index makes range scans fast.
You do not have to define a PK when creating a table, but since the PK is closely tied to performance, it is best to define it at creation time, especially for large or frequently used tables.
📢 The structure that Oracle DBMS calls an IOT (Index Organized Table) is the normal table structure in MySQL InnoDB.
Index
Most MySQL indexes use a B-Tree structure. The exceptions: data types that represent geographic values (spatial data) use R-Trees, and InnoDB full-text indexes are created on text-based columns and use an inverted index design. (They store a list of words and, for each word, a list of the documents it appears in.)
Clustered Index(=Primary Key)
Every InnoDB table has a special index called the clustered index. The clustered index stores data sorted by primary key (PK) value. It is a regular B-Tree structure, and its leaf nodes store all columns together.
A clustered index is actually closer to a storage structure than a specific index type. That is because the term covers both the B-Tree index sorted by primary key and the actual data sorted by the same values. So access through the clustered index leads straight to the page that contains the row, which makes it fast.
- If you define a PK when creating a table, InnoDB uses the PK as the clustered index.
- If there is no unique, NOT NULL column or set of columns suitable as a PK, the clustered index is built by adding an auto-increment column.
- If the table has no PK and no suitable unique index, InnoDB creates and uses a hidden clustered index on a column that contains row ID values.
Secondary Index
All indexes added by users, other than the clustered index described above, are called secondary indexes. In other DBMSs, an index stores the physical address of the target table block in its leaf nodes and uses it to fetch the data. In MySQL, however, the index stores the PK instead of an address, so it uses that PK to search the clustered index once more to reach the data. (That is, the lookup goes Secondary Index → Primary Key Value → Data.)
Because a secondary index stores the PK value along with the indexed columns, there is something to watch out for. If the PK consists of many columns, secondary indexes can grow in size, so choose the PK carefully when you plan to build many secondary indexes.
Doublewrite Buffer
To prevent data loss, InnoDB uses a file flush feature called Doublewrite. The Doublewrite Buffer is an area where pages flushed from the Buffer Pool are stored before they are written to the data files. Despite the name "buffer", it actually exists as files on disk.
- Dirty pages in the Buffer Pool are flushed.
- Multiple dirty pages are written to the Doublewrite Buffer at once.
- The dirty pages written to the Doublewrite Buffer are written one by one, in order, from the Buffer Pool to the data files.
⚡ If the mysqld process stops, or the operating system or storage system fails, while pages are being written from the Buffer Pool to the data files, InnoDB can recover by finding a copy of the page in the Doublewrite Buffer.
📢 Before MySQL 8.0.20, the Doublewrite Buffer storage area was inside the System Tablespace. In later versions, it lives in doublewrite files.
Redo Log
The redo log is used during crash recovery to apply data changed by incomplete transactions. The log buffer in memory and the redo log files on disk make up the redo log.
MySQL does not write every InnoDB change to disk right away; it stores changes in memory (the Buffer Pool). So if changed data has not been applied to disk (the data files) because of an instance failure, redo is performed while the database restarts.
📢 Following the WAL principle, the redo log is written before the data in the Buffer Pool.
By default, the redo log uses the following 2 files in a circular fashion.
- ib_logfile0
- ib_logfile1
Because redo log files are circular, old data is overwritten and lost. Data passed through the redo log is tracked by an ever-increasing LSN value.
📢 An LSN (Log Sequence Number) is an arbitrary, ever-increasing value that points to the point in time of an operation recorded in the redo log. It is used internally for crash recovery and Buffer Pool management.
- InnoDB flushes a transaction's redo log to disk before the transaction commits. InnoDB uses group commit to avoid a flush on every commit, grouping multiple flushes and performing them at once. This can greatly improve throughput for many transactions committing at the same time.
- Backup utilities that copy redo log records sometimes cannot keep up with redo log generation, which can lead to the loss of redo log records as the files are reused in a circle. With the Redo Log Archiving feature introduced in MySQL 8.0.17, redo log records are also written sequentially to archive files in addition to the redo log files, which avoids this problem. You can enable Redo Log Archiving with the
innodb_redo_log_archive_dirssystem variable.
- Starting with MySQL 8.0.21, redo logging can be disabled in certain situations. It can be used to speed up data loading by stopping redo log writes and doublewrite buffering when loading large amounts of data to build a new MySQL instance. You can run the server with redo logging disabled, but an unexpected server stop can cause data loss and instance corruption, so do not use this feature on production systems.
Undo Log
An undo log is a collection of records associated with a single transaction. It holds information about the data before the transaction's most recent change. If another transaction wants to read the data before the change (the original), it can read it from the undo log.
Undo logs live in undo log segments contained in rollback segments. Rollback segments are contained in Undo Tablespaces or the Global Temporary Tablespace. Before 8.0, undo data files were in the System Tablespace, but they were later separated into their own files.
Transactions on regular tables are assigned undo logs in an Undo Tablespace, and transactions on temporary tables are assigned undo logs in the Global Temporary Tablespace. Undo logs in the Global Temporary Tablespace are used by transactions that modify temporary table data, so they are only used for rollback while the server is running. (Because these are temporary tables, crash recovery is not needed, so they are not redo-logged.)
Each transaction is assigned up to 4 undo logs, one per target and DML type, as follows.
- INSERT operations on user-defined tables
- UPDATE and DELETE operations on user-defined tables
- INSERT operations on user-defined temporary tables
- UPDATE and DELETE operations on user-defined temporary tables
For example, a transaction that performs INSERT, UPDATE, and DELETE on both a regular table and a temporary table must be assigned up to 4 undo logs, while a transaction that only performs INSERT on a regular table is assigned 1 undo log.
An undo log assigned to a transaction stays attached to it until the transaction completes. For example, an undo log assigned to an INSERT on a regular table is used for all INSERT operations on regular tables performed by that transaction.
System Variable
Parameter | Default Value | Range | Description |
innodb_file_per_table | ON | ON, OFF | • System variable that determines whether innodb_file_per_table is used.
• If ON, tables are created in File-Per-Table Tablespaces by default. If OFF, they are created in the System Tablespace. |
innodb_flush_method | (unix) fsync,(windows) unbuffered | (unix) fsync, O_DSYNC, littlesync, nosync, O_DIRECT, O_DIRECT_NO_FSYNC(windows) unbuffered, normal | UNIX
• Defines the method used to flush data to the data files and log files, which can affect I/O throughput.
• fsync or 0: Flushes both data and log files with the fsync() system call.
• O_DSYNC or 1: Opens and flushes log files with O_SYNC, and flushes data files with fsync(). InnoDB does not use O_DSYNC directly.
• littlesync or 2: Used for internal performance testing and currently unsupported. Use at your own risk.
• nosync or 3: Used for internal performance testing and currently unsupported. Use at your own risk.
• O_DIRECT or 4: Opens data files with O_DIRECT and flushes both data and log files with fsync().
• O_DIRECT_NO_FSYNC: Uses O_DIRECT while flushing I/O, but skips the fsync() system call after each write.
WINDOWS
• unbuffered or 0: Uses simulated asynchronous I/O and unbuffered I/O.
• normal or 1: Uses simulated asynchronous I/O and buffered I/O. |
innodb_undo_directory | NULL | ㅤ | • Path where Undo Tablespaces are created.
• Typically used to place Undo Tablespaces on a different storage device. If this system variable is not defined, Undo Tablespaces are created in the data directory. |
internal_tmp_disk_storage_engine | INNODB | MYISAM, INNODB | • Determines the storage engine used for on-disk internal temporary tables.
• From MySQL 8.0.16, on-disk internal temporary tables always use the InnoDB storage engine, and this system variable was removed in MySQL 8.0.16 and is no longer supported. |
innodb_temp_tablespaces_dir | #innodb_temp | ㅤ | Defines where the Session Temporary Tablespace Pool is created when InnoDB starts. |
innodb_temp_data_file_path | ibtmp1:12M:autoextend | ㅤ | • Defines the relative path, name, size, and attributes of the Global Temporary Tablespace data file.
• If not set, a single auto-extending data file named ibtmp1 is created in the innodb_data_home_dir directory.
• The Global Temporary Tablespace data file cannot have the same name as another data file. |
innodb_data_home_dir | MySQL datadir | ㅤ | • The common part of the directory path for InnoDB System Tablespace data files.
• If this value is not defined as an absolute path, it is combined with the innodb_data_file_path setting. |
innodb_doublewrite | ON | ON, OFF | Controls whether the Doublewrite Buffer is used. |
innodb_doublewrite_dir | MySQL datadir | ㅤ | Location where doublewrite files are created. |
innodb_doublewrite_files | innodb_buffer_pool_instances * 2 | 2- 256 | • Defines the number of doublewrite files.
• By default, 2 doublewrite files are created for each Buffer Pool instance. (Flush List, LRU List)
• The maximum number of doublewrite files is 2 times the number of Buffer Pool instances. (The number of Buffer Pool instances is controlled by the innodb_buffer_pool_instances system variable.) |
innodb_doublewrite_pages | innodb_write_io_threads | innodb_write_io_threads - 512 | • Maximum number of doublewrite pages each thread can handle.
• If not set, it is set to the innodb_writer_io_threads value. |
innodb_doublewrite_batch_size | 0 | 0 - 256 | Controls the number of doublewrite pages to write in a batch. Used for advanced performance tuning. |
innodb_log_file_size | 50331648(48MB) | 4194304 - 512GB | • Size of each log file in a log group.
• Larger values mean fewer checkpoint flushes needed for the Buffer Pool, saving disk I/O. (However, crash recovery can be slower.) |
innodb_log_files_in_group | 2 | 2 - 100 | • Number of log files in a log group.
• Files are written in a circular fashion. |
innodb_log_group_home_dir | MySQL datadir | ㅤ | Directory path for InnoDB redo log files.
• The number of log files is set by innodb_log_files_in_group. |
innodb_redo_log_archive_dirs | NULL | ㅤ | • Defines labeled directories where redo log archive files can be created.
• Multiple labeled directories can be defined, separated by semicolons. The directories must exist and their paths must be specified. |
Related articles
