Buffering and Caching

8.9 Buffering and Caching

MySQL uses several strategies that cache information in memory buffers to increase performance.

8.9.1 The InnoDB Buffer Pool

InnoDB maintains a storage area called the buffer pool for caching data and indexes in memory. Knowing how theInnoDB buffer pool works, and taking advantage of it to keep frequently accessed data in memory, is an important aspect of MySQL tuning.

Guidelines

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, reading data from disk once and then accessing the data from memory during subsequent reads. The buffer pool even caches data changed by insert and update operations, so that disk writes can be grouped together for better performance.

Depending on the typical workload on your system, you might adjust the proportions of the parts within the buffer pool. You can tune the way the buffer pool chooses which blocks to cache once it fills up, to keep frequently accessed data in memory despite sudden spikes of activity for operations such as backups or reporting.

With 64-bit systems with large memory sizes, you can split the buffer pool into multiple parts, to minimize contention for the memory structures among concurrent operations. For details, see Section 14.13.1.4, “Using Multiple Buffer Pool Instances”.

Internal Details

InnoDB manages the pool as a list, using a variation of the least recently used (LRU) algorithm. 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. This “midpoint insertion strategy” treats the list as two sublists:

• At the head, a sublist of “new” (or “young”) blocks that were accessed recently.

• At the tail, a sublist of “old” blocks that were accessed less recently.

This algorithm keeps blocks that are heavily used by queries in the new sublist. The old sublist contains less-used blocks; these blocks are candidates for eviction.

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 because it is required for a user-specified operation such as an SQL query, or as part of aread-ahead operation performed automatically by InnoDB.

• Accessing a block in the old sublist makes it “young”, moving it 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 the database operates, blocks in the buffer pool that are not accessed “age” 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.

By default, blocks read by queries immediately move into the new sublist, meaning they will stay in the buffer pool for a long time. A table scan (such as performed for a mysqldump operation, or a SELECT statement with noWHERE clause) can bring a large amount of data into the buffer pool and evict an equivalent amount of older data, even if the new data is never used again. Similarly, blocks that are loaded by the read-ahead background thread and then accessed only once move to the head of the new list. These situations can push frequently used blocks to the old sublist, where they become subject to eviction.

Configuration Options

Several InnoDB system variables control the size of the buffer pool and let you tune the LRU algorithm:

• innodb_buffer_pool_size

  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.

• innodb_buffer_pool_instances

  Divides the buffer pool into a user-specified number of separate regions, each with its own LRU list and related data structures, to reduce contention during concurrent memory read and write operations. This option takes effect only when you set the innodb_buffer_pool_size to a size of 1 gigabyte or more. The total size you specify is divided among all the buffer pools. For best efficiency, specify a combination ofinnodb_buffer_pool_instances and innodb_buffer_pool_size so that each buffer pool instance is at least 1 gigabyte.

• innodb_old_blocks_pct

  Specifies the approximate percentage of the buffer pool that InnoDB uses for the old block sublist. The range of values is 5 to 95. The default value is 37 (that is, 3/8 of the pool).

• innodb_old_blocks_time

  Specifies how long in milliseconds (ms) a block inserted into the old sublist must stay there after its first access before it can be moved to the new sublist. The default value is 0: A block inserted into the old sublist moves to the new sublist when Innodb has evicted 1/4 of the inserted block's pages from the buffer pool, no matter how soon after insertion the access occurs. If the value is greater than 0, blocks remain in the old sublist until an access occurs at least that many ms after the first access. For example, a value of 1000 causes blocks to stay in the old sublist for 1 second after the first access before they become eligible to move to the new sublist.

Setting innodb_old_blocks_time greater than 0 prevents one-time table scans from flooding the new sublist with blocks used only for the scan. Rows in a block read in for a scan are accessed many times in rapid succession, but the block is unused after that. If innodb_old_blocks_time is set to a value greater than time to process the block, the block remains in the “old” sublist and ages to the tail of the list to be evicted quickly. This way, blocks used only for a one-time scan do not act to the detriment of heavily used blocks in the new sublist.

innodb_old_blocks_time can be set at runtime, so you can change it temporarily while performing operations such as table scans and dumps:

SET GLOBAL innodb_old_blocks_time = 1000;
... perform queries that scan tables ...
SET GLOBAL innodb_old_blocks_time = 0;

This strategy does not apply if your intent is to “warm up” the buffer pool by filling it with a table's content. For example, benchmark tests often perform a table or index scan at server startup, because that data would normally be in the buffer pool after a period of normal use. In this case, leave innodb_old_blocks_time set to 0, at least until the warmup phase is complete.

Monitoring the Buffer Pool Using the InnoDB Standard Monitor

The output from the InnoDB Standard Monitor contains several fields in the BUFFER POOL AND MEMORY section that pertain to operation of the buffer pool LRU algorithm:

• Old database pages: The number of pages in the old sublist of the buffer pool.

• Pages made young, not young: The number of old pages that were moved to the head of the buffer pool (the new sublist), and the number of pages that have remained in the old sublist without being made new.

• youngs/s non-youngs/s: The number of accesses to old pages that have resulted in making them young or not. This metric differs from that of the previous item in two ways. First, it relates only to old pages. Second, it is based on number of accesses to pages and not the number of pages. (There can be multiple accesses to a given page, all of which are counted.)

• young-making rate: Hits that cause blocks to move to the head of the buffer pool.

• not: Hits that do not cause blocks to move to the head of the buffer pool (due to the delay not being met).

The young-making rate and not rate will not normally add up to the overall buffer pool hit rate. Hits for blocks in the old sublist cause them to move to the new sublist, but hits to blocks in the new sublist cause them to move to the head of the list only if they are a certain distance from the head.

The preceding information from the Monitor can help you make LRU tuning decisions:

• If you see very low youngs/s values when you do not have large scans going on, that indicates that you might need to either reduce the delay time, or increase the percentage of the buffer pool used for the old sublist. Increasing the percentage makes the old sublist larger, so blocks in that sublist take longer to move to the tail and be evicted. This increases the likelihood that they will be accessed again and be made young.

• If you do not see a lot of non-youngs/s when you are doing large table scans (and lots of youngs/s), to tune your delay value to be larger.

Note

Per second averages provided in InnoDB Monitor output are based on the elapsed time between the current time and the last time InnoDB Monitor output was printed.

For more information about InnoDB Monitors, see Section 14.15, “InnoDB Monitors”.

The INNODB_BUFFER_POOL_STATS table and InnoDB buffer pool server status variables provide much of the same buffer pool information that is provided by SHOW ENGINE INNODB STATUS output.

8.9.1.1 Resizing the InnoDB Buffer Pool Online

As of MySQL 5.7.5, the innodb_buffer_pool_size configuration option can be set dynamically, which allows you to resize the buffer pool without restarting the server. For example:

mysql> SET GLOBAL innodb_buffer_pool_size=2147483648

The resizing operation is performed by a background thread. When increasing the size of the buffer pool, the resizing operation:

• Moves pages to a new location in memory

• Coverts hash tables, lists, and pointers to use new addresses in memory

• Adds new pages to the free list

While these operations are in progress, other threads are blocked from accessing the buffer pool.

When decreasing the size of the buffer pool, the resizing operation:

• Defragments the buffer pool and withdraws pages

• Moves pages to a new location in memory

• Converts hash tables, lists, and pointers to use new addresses in memory

Of these operations, only defragmenting the buffer pool and withdrawing pages allows other threads to access to the buffer pool concurrently.

Active transactions, and operations performed through InnoDB APIs, should be completed before resizing the buffer pool. When initiating a resizing operation, the resize operation does not start until all active transactions are completed. Once the resizing operation is in progress, new transactions and operations that require access to the buffer pool must wait until the resizing operation finishes. The exception to this rule is that concurrent access to the buffer pool is permitted when defragmenting the buffer pool and withdrawing pages during an operation to decrease buffer pool size. A drawback of allowing concurrent buffer pool access while withdrawing pages is that there could be a temporary shortage of available pages during this phase of the resizing operation.

Note

Nested transactions could fail if initiated after the buffer pool resizing operation begins.

Configuring Chunk Size for the Buffer Pool Resizing Operation

The resizing operation, which involves moving pages to a new location in memory, is performed in chunks. Chunk size is configurable using the innodb_buffer_pool_chunk_size configuration option.innodb_buffer_pool_chunk_size is a read-only option that can only be configured at startup, on the command line or in a configuration file.

Command line:

./mysqld --innodb_buffer_pool_chunk_size=134217728

Configuration file:

[mysqld]
innodb_buffer_pool_chunk_size=134217728

The innodb_buffer_pool_chunk_size default value is 128MB (134217728 bytes) and can only be increased or decreased in 1MB (1048576 byte) units. The following conditions apply when altering theinnodb_buffer_pool_chunk_size value:

• If innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances is larger than the current buffer pool size when the buffer pool is initialized, innodb_buffer_pool_chunk_size is truncated toinnodb_buffer_pool_size / innodb_buffer_pool_instances.

• The buffer pool size must always be a multiple of innodb_buffer_pool_chunk_size *innodb_buffer_pool_instances. For example, for a configuration withinnodb_buffer_pool_chunk_size=1MB (134217728 bytes) and innodb_buffer_pool_instances=1, valid buffer pool size values would be 1MB (134217728 bytes), 2MB (268435456 bytes), 3MB (402653184 bytes), and so on. If you increase innodb_buffer_pool_chunk_size, the size of the buffer pool may be increased automatically, as necessary, to ensure that innodb_buffer_pool_size remains a multiple ofinnodb_buffer_pool_chunk_size * innodb_buffer_pool_instances.

• Changing the innodb_buffer_pool_chunk_size cannot decrease the size of the buffer pool. If you decreaseinnodb_buffer_pool_chunk_size, the buffer pool size is set to a multiple ofinnodb_buffer_pool_chunk_size * innodb_buffer_pool_instances that is not less than current buffer pool size.

Important: Care should be taken when changing innodb_buffer_pool_chunk_size. Under certain circumstances, changing innodb_buffer_pool_chunk_size can significantly increase the size of the buffer pool (innodb_buffer_pool_size). Before you change innodb_buffer_pool_chunk_size, calculate the effect it will have on innodb_buffer_pool_size to ensure that the resulting buffer pool size is acceptable.

To avoid potential performance issues, it is also recommended that the number of chunks (innodb_buffer_pool_size / innodb_buffer_pool_chunk_size) not exceed 1000.

Monitoring Online Buffer Pool Resize Progress

The Innodb_buffer_pool_resize_status variable reports the progress of the buffer pool resizing operation. For example:

mysql> SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';
+----------------------------------+----------------------------------+
| Variable_name                    | Value                            |
+----------------------------------+----------------------------------+
| Innodb_buffer_pool_resize_status | Resizing also other hash tables. |
+----------------------------------+----------------------------------+
1 row in set (0.00 sec)
        

The progress of the resizing operation is also logged in the server error log file. This example shows notes that are logged when increasing the size of the buffer pool:

[Note] InnoDB: Resizing buffer pool from 134217728 to 4294967296. (unit=134217728)
[Note] InnoDB: disabled adaptive hash index.
[Note] InnoDB: buffer pool 0 : 31 chunks (253952 blocks) was added.
[Note] InnoDB: buffer pool 0 : hash tables were resized.
[Note] InnoDB: Resized hash tables at lock_sys, adaptive hash index, dictionary.
[Note] InnoDB: completed to resize buffer pool from 134217728 to 4294967296.
[Note] InnoDB: re-enabled adaptive hash index.
        

This example shows notes that are logged when decreasing the size of the buffer pool:

[Note] InnoDB: Resizing buffer pool from 4294967296 to 134217728. (unit=134217728)
[Note] InnoDB: disabled adaptive hash index.
[Note] InnoDB: buffer pool 0 : start to withdraw the last 253952 blocks.
[Note] InnoDB: buffer pool 0 : withdrew 253952 blocks from free list. tried to relocate 0 pages. (253952/253952)
[Note] InnoDB: buffer pool 0 : withdrawn target 253952 blocks.
[Note] InnoDB: buffer pool 0 : 31 chunks (253952 blocks) was freed.
[Note] InnoDB: buffer pool 0 : hash tables were resized.
[Note] InnoDB: Resized hash tables at lock_sys, adaptive hash index, dictionary.
[Note] InnoDB: completed to resize buffer pool from 4294967296 to 134217728.
[Note] InnoDB: re-enabled adaptive hash index.

8.9.2 The MyISAM Key Cache

To minimize disk I/O, the MyISAM storage engine exploits a strategy that is used by many database management systems. It employs a cache mechanism to keep the most frequently accessed table blocks in memory:

• For index blocks, a special structure called the key cache (or key buffer) is maintained. The structure contains a number of block buffers where the most-used index blocks are placed.

• For data blocks, MySQL uses no special cache. Instead it relies on the native operating system file system cache.

This section first describes the basic operation of the MyISAM key cache. Then it discusses features that improve key cache performance and that enable you to better control cache operation:

• Multiple sessions can access the cache concurrently.

• You can set up multiple key caches and assign table indexes to specific caches.

To control the size of the key cache, use the key_buffer_size system variable. If this variable is set equal to zero, no key cache is used. The key cache also is not used if the key_buffer_size value is too small to allocate the minimal number of block buffers (8).

When the key cache is not operational, index files are accessed using only the native file system buffering provided by the operating system. (In other words, table index blocks are accessed using the same strategy as that employed for table data blocks.)

An index block is a contiguous unit of access to the MyISAM index files. Usually the size of an index block is equal to the size of nodes of the index B-tree. (Indexes are represented on disk using a B-tree data structure. Nodes at the bottom of the tree are leaf nodes. Nodes above the leaf nodes are nonleaf nodes.)

All block buffers in a key cache structure are the same size. This size can be equal to, greater than, or less than the size of a table index block. Usually one these two values is a multiple of the other.

When data from any table index block must be accessed, the server first checks whether it is available in some block buffer of the key cache. If it is, the server accesses data in the key cache rather than on disk. That is, it reads from the cache or writes into it rather than reading from or writing to disk. Otherwise, the server chooses a cache block buffer containing a different table index block (or blocks) and replaces the data there by a copy of required table index block. As soon as the new index block is in the cache, the index data can be accessed.

If it happens that a block selected for replacement has been modified, the block is considered “dirty.” In this case, prior to being replaced, its contents are flushed to the table index from which it came.

Usually the server follows an LRU (Least Recently Used) strategy: When choosing a block for replacement, it selects the least recently used index block. To make this choice easier, the key cache module maintains all used blocks in a special list (LRU chain) ordered by time of use. When a block is accessed, it is the most recently used and is placed at the end of the list. When blocks need to be replaced, blocks at the beginning of the list are the least recently used and become the first candidates for eviction.

The InnoDB storage engine also uses an LRU algorithm, to manage its buffer pool. See Section 8.9.1, “TheInnoDB Buffer Pool”.