MySQL 5.0 Reference Manual  /  ...  /  The InnoDB Buffer Pool

8.10.2 The InnoDB Buffer Pool

InnoDB maintains a buffer pool for caching data and indexes in memory. InnoDB manages the pool as a list, using a least recently used (LRU) algorithm incorporating a midpoint insertion strategy. When room is needed to add a new block to the pool, InnoDB evicts the least recently used block and adds the new block to the middle of the list. The midpoint insertion strategy in effect causes the list to be treated as two sublists:

  • At the head, a sublist of new (or young) blocks that have been recently used.

  • At the tail, a sublist of old blocks that are less recently used.

As a result of the algorithm, the new sublist contains blocks that are heavily used by queries. The old sublist contains less-used blocks, and candidates for eviction are taken from this sublist.

The LRU algorithm operates as follows by default:

  • 3/8 of the buffer pool is devoted to the old sublist.

  • The midpoint of the list is the boundary where the tail of the new sublist meets the head of the old sublist.

  • When InnoDB reads a block into the buffer pool, it initially inserts it at the midpoint (the head of the old sublist). A block can be read in as a result of two types of read requests: Because it is required (for example, to satisfy query execution), or as part of read-ahead performed in anticipation that it will be required.

  • The first access to a block in the old sublist makes it young, causing it to move to the head of the buffer pool (the head of the new sublist). If the block was read in because it was required, the first access occurs immediately and the block is made young. If the block was read in due to read-ahead, the first access does not occur immediately (and might not occur at all before the block is evicted).

  • As long as no accesses occur for a block in the pool, it ages by moving toward the tail of the list. Blocks in both the new and old sublists age as other blocks are made new. Blocks in the old sublist also age as blocks are inserted at the midpoint. Eventually, a block that remains unused for long enough reaches the tail of the old sublist and is evicted.

In the default operation of the buffer pool, a block when read in is loaded at the midpoint and then moved immediately to the head of the new sublist as soon as an access occurs. In the case of a table scan (such as performed for a mysqldump operation), each block read by the scan ends up moving to the head of the new sublist because multiple rows are accessed from each block. This occurs even for a one-time scan, where the blocks are not otherwise used by other queries. Blocks may also be loaded by the read-ahead background thread and then moved to the head of the new sublist by a single access. These effects can be disadvantageous because they push blocks that are in heavy use by other queries out of the new sublist to the old sublist where they become subject to eviction.

The innodb_buffer_pool_size system variable specifies the size of the buffer pool. If your buffer pool is small and you have sufficient memory, making the pool larger can improve performance by reducing the amount of disk I/O needed as queries access InnoDB tables.

The MyISAM storage engine also uses an LRU algorithm, to manage its key cache. See Section 8.10.1, “The MyISAM Key Cache”.

User Comments
  Posted by Scott Simmons on October 5, 2015
I think this may be subjective based on the systems in place, "Ideally, you set the size of the buffer pool to as large a value as practical, leaving enough memory for other processes on the server to run without excessive paging. The larger the buffer pool, the more InnoDB acts like an in-memory database"

Actually the opposite may be true in some scenarios. What if I am running Solaris, OpenSolaris or an OS using a technology such as ZFS? With that I want innodb buffer cache to be as small as possible to allow the underlying FS to cache all operations that this process will require. This is also more efficient as ZFS uses a combination of MFU & MRU lists using an adaptive caching policy. Disk writes are handled by txg's (transaction groups) to batch writes together by the FS as well. LRU is less adaptive therefore less effective.
  Posted by Tomasz Korwel on October 16, 2015
To the contrary - even with ZFS increasing innodb pool and decreasing ZFS arc pool is the way to go since innodb pool has much shorter "code path" to the data in cache hence reducing number of CPU ticks needed to retrieve the data.
Sign Up Login You must be logged in to post a comment.