This post covers the In-Memory Structure of InnoDB, the main storage engine.
The main components of the In-Memory Structure are listed below. We look at each in order.
- Buffer Pool
- Change Buffer
- Adaptive Hash Index
- Log Buffer
Buffer Pool
The InnoDB Buffer Pool is the main memory area for caching table and index data. Besides table and index data, it also caches various other types of data, as shown in the figure below, and manages them with the Buffer Pool List.
With the Buffer Pool, frequently used data can be accessed directly in memory, which speeds up processing. The larger the Buffer Pool, the more data can be accessed from memory, so InnoDB can behave like an in-memory database. In other words, Buffer Pool size is critical to system performance, and on dedicated database servers up to 80% of physical memory is often allocated to the Buffer Pool.
The Buffer Pool is organized in pages for efficient large reads, and these pages are managed efficiently with a linked list structure.
When InnoDB accesses data, it checks the Buffer Pool first. If the data is not there, it reads the page from the data file and adds it to the Buffer Pool List.
Not all data fits in the Buffer Pool (memory), so an LRU algorithm is used to keep mainly frequently used data.
📢 The LRU algorithm links the pages of the Buffer Pool into a list and moves a page to the head of the list (MRU: Most Recently Used) every time it is accessed. Unused pages move toward the tail of the list (LRU: Least Recently Used), and pages that exceed the Buffer Pool capacity are evicted from memory.
Change Buffer (older versions - Insert Buffer)
The Change Buffer is an area inside the InnoDB Buffer Pool: a temporary space for caching changes to secondary index pages. (On disk, the Change Buffer resides in the system tablespace.)
DML operations such as Insert, Update, and Delete require changes to secondary indexes, which costs a lot of disk resources. If the secondary index page to be changed is in the Buffer Pool, it can be changed in memory right away. If it is not, only the change is stored in the Change Buffer and applied later, which improves performance.
Changes stored in the Change Buffer are merged later, when the page is loaded into the Buffer Pool by some other read. This reduces the I/O needed to read secondary index pages from disk into the Buffer Pool.
The merge is performed by the InnoDB main thread while the database server is idle or during a slow shutdown.
📢 A slow shutdown is a type of shutdown that performs additional InnoDB flush operations before completing. It is also called a clean shutdown. The shutdown itself can take longer, but it saves time at startup.
Because the Change Buffer shares the Buffer Pool, it can reduce the space available for caching data pages. If the data set nearly fits in the Buffer Pool, or if there are few secondary indexes, it is better to disable the Change Buffer. (The Change Buffer applies only to pages that are not in the Buffer Pool, so it has no effect in these cases.)
You can also use the
innodb_change_buffering system variable to define which types of changes are stored in the Change Buffer, enabling or disabling buffering for INSERT, DELETE, and PURGE operations.📢 A unique index must check whether the same data exists in memory and on disk to enforce uniqueness, so the data has to be loaded into the cache for comparison. For this reason, unique indexes do not use the Change Buffer.
Adaptive Hash Index
Like the Change Buffer, InnoDB uses a portion of the Buffer Pool (1/64) as a space called the Adaptive Hash Index.
The Adaptive Hash Index is a key InnoDB feature that compensates for the limits of the B-Tree index. It defines frequently used columns as hashes so that data can be accessed directly without going through the B-Tree index.
With this feature, InnoDB can perform like an in-memory database on the Buffer Pool without sacrificing transactional features or reliability.
📢 As in other RDBMSs, the representative index in MySQL is the B-Tree. Data is stored sorted by primary key, and secondary indexes are sorted by the combination of the PK and the index key.
The Adaptive Hash Index is managed as pairs of an "index key value" and the "data page address" where that index key value is stored, and it is built only for frequently accessed index pages.
The "index key value" is a combination of the B-Tree index ID and the actual key value. The "data page address" is the memory address of the data page that stores the actual key value (the address of the page loaded in the Buffer Pool).
You can turn the feature on or off with the
innodb_adaptive_hash_index system variable. However, the optimizer decides which data is frequently used and turns it into hash keys, so you cannot choose the targets yourself, which makes it hard to control.Access to the Adaptive Hash Index can cause contention in heavy operations such as joins across multiple tables, and it brings no benefit for queries using the LIKE operator with "%". In cases like these where there is no benefit, you can disable the feature to reduce load.
Log Buffer (older versions - Redo Log Buffer)
The Log Buffer is a memory area that holds data to be written to the redo log file.
When InnoDB changes data in the Buffer Pool, it first records the changes in the Log Buffer and then writes them to disk at a configured interval or at transaction commit. (This complies with the force-log-at-commit principle.)
The Log Buffer size is set with the
innodb_log_buffer_size system variable, and the contents of the Log Buffer are flushed to disk periodically. Increasing the Log Buffer size lets large transactions run without writing redo log data to disk before they commit. Conversely, if the redo log file is too small, checkpoints occur often and changed data is written to disk more frequently.Changed data in the Buffer Pool is written to disk after the redo log is written to disk, following the checkpoint mechanism based on the WAL principle. The
innodb_flush_log_at_trx_commit system variable controls how the contents of the Log Buffer are written and flushed to disk, and innodb_flush_log_at_timeout controls the flush frequency (interval).innodb_flush_log_at_trx_commit
innodb_flush_log_at_trx_commit, which controls how the Log Buffer is flushed to disk, can be set to 0, 1, or 2. Each value flushes to disk as follows.
- 0 : Writes the redo log and flushes it to disk every second, regardless of commits. If MySQL or the node fails, up to 1 second of transactions before the failure can be lost.
- 1 : Writes the redo log and flushes it to disk at every commit. Frequent writes and flushes put load on the disk and can affect performance, but data for completed transactions is guaranteed if a failure occurs.
- 2 : At every commit, the redo log in the Log Buffer is written to the OS cache, and the disk flush happens every 1 second. If only MySQL fails, transactions stored in the OS cache are likely to make it into the redo log, but if the node running MySQL fails, up to 1 second of transactions can be lost.
- It is usually set to 1 to minimize data loss from failures.
System Variable
Parameter | Default Value | Range | Description |
innodb_change_buffering | all | none, inserts, deletes, changes, purges, all | Specifies whether InnoDB performs change buffering.
• all : Default. Buffers inserts, delete-marking operations, and purges
• none : No buffering
• inserts : Buffers insert operations
• deletes : Buffers delete-marking operations
• changes : Buffers both inserts and delete-marking operations
• purges : Buffers physical deletion operations that happen in the background |
innodb_adaptive_hash_index | ON | ON | OFF | • Specifies whether InnoDB uses the Adaptive Hash Index.
• Enabled by default. It can be changed with a "SET GLOBAL" statement without restarting the server. |
innodb_log_buffer_size | 16777216(16MB) | 1048576 - 4294967295 | • The size of the buffer InnoDB uses to write log files to disk. A large Log Buffer lets many transactions run without writing the log to disk before they commit.
• If you have transactions that update, delete, or insert many rows, setting this value higher can reduce disk I/O. |
innodb_flush_log_at_trx_commit | 1 | 0, 1, 2 | • This system variable controls when commit-related log records are written & flushed.
• The value affects both data loss and I/O performance, so set it based on how important the data is and the performance you need. |
innodb_flush_log_at_timeout | 1 | 1 - 2700 | Writes and flushes the log every N seconds as configured. You can use this system variable to reduce flushes. |
Related articles
