InnoDB Buffer Pool

InnoDB plugin feature Description: Keep buffer pool from being contaminated

A big advantage over MyISAM is that InnoDB caches blocks of data. If the amount of data in the system is not large, or if the amount of active data is not large, InnoDB will cache all of these blocks into buffer Pool (memory), which maximizes the response speed provided, especially reading. When a user requests a data block to be queried, InnoDB first finds the data in buffer pool, and if the block is not present in buffer pool, InnoDB will load the corresponding block of data from the disk and replace the current buffer with the LRU algorithm. The cache block for the pool.

1. LRU drawbacks, details in Buffer pool

One drawback of this LRU algorithm is that if a query does a full table scan (such as a backup, temporary DDL, etc.), it can cause the data blocks in the LRU list to be replaced throughout the buffer pool, and even many hotspot data will be replaced. These new data blocks may not be read again after this query. We also call this situation "Buffer pool contaminated".

In InnoDB, a number of new mechanisms were introduced to avoid this situation. The algorithm is still the LRU algorithm, but adds a midpoint strategy (similar to the midpoint strategy in the MyISAM key buffer). The parameter innodb_old_blocks_time is also introduced to control the buffer pool from being contaminated.

The data in the LRU list is divided into two parts: Sublist of New and sublist of old. The latter includes access to data blocks that have not been accessed recently (the later the data block of the linked list, the more recently it is not accessed). By default, the former accounts for 63% (5/8), the latter 37% (3/8).

When a chunk needs to be read from a data file, it is first placed in the head of the old sublist (midpoint). Then, if there is access to the block, then the data block is placed in the header of the new sublist. In general, when a chunk is removed, it is immediately read and will soon be placed on the head of new sublist. In a bad situation, if mysqldump accesses all blocks of data, it will result in all data blocks being put into new sublist. This way, the buffer pool is completely contaminated.

2. How the LRU queue in Buffer pool prevents contamination

InnoDB plugin to prevent "contamination" by introducing the following parameters:

innodb_old_blocks_pct: Control the length of the old sublist in the LRU queue

Innodb_old_blocks_time: This parameter determines that after the block is inserted into midpoint (old sublist), it must be over Innodb_old_blocks_time (MS) time in old sublist. Could be transferred to new sublist. For example, if Innodb_old_blocks_time is set to 1000 (that is, 1s), when a table scan appears, InnoDB first loads the data block onto midpoint (old sublist), and the program reads the data block, because at this point, The data block's dwell time in the old sublist is less than innodb_old_blocks_time, so it will not be transferred to the new sublist. This avoids the situation where the buffer pool is contaminated. The cool thing is that this parameter is dynamically adjusted, so when you do an unexpected table scan, adjust the parameter dynamically.

3. Anti-pollution efficiency observation

InnoDB Buffer Pool