MySQL's InnoDB Buffer pool internal mechanism

1. Basic structure: InnoDB uses the least recently used (LRU) algorithm to manage his buffer_pool. The Buffer_pool is internally separated into two lists. A young list and a old list.

The young list stores cached data that is used by the high frequency (the default is 5/8 of the entire buffer)

The old list stores data that is used at low frequencies (the default is 3/8 of the total buffer)

2. Usage mechanism: When a new block of data is cached from the disk into buffer, it is placed by default on the old list's head, which is midpoint. Any block data within the old list is accessed again, and InnoDB will add it to the young list's head. But if the block data is accessed by the read-ahead mechanism, it is not immediately loaded into the young list. When unused chunks of data are squeezed to the old list's tail, they are reclaimed by the system.

3. How to configure:

innodb_old_blocks_pct: Sets the ratio of the young list to the old list. (The default value is PNs (that is, 3/8 of the pool).

Innodb_old_blocks_time: Sets the delay time after the data block in the old list has been accessed to join the young list. The default is 0, which is immediately added to the young list. When using some dump or full table scans temporarily, use this parameter to avoid a large number of data blocks being recycled.

4. Monitoring: The buffer POOL and memory portion of Show engine InnoDB status contains the usage of the Old/young list

Old database pages: number of data pages included in the old list

Pages made young:the number of old pages this were moved to the head of the buffer pool (the new sublist)

Not young:he number of pages this has remained in the old sublist without being made new

YOUNGS/S: The number of access processes that caused the data block to join the young list (note: This is the number of processes, not the number of blocks of data)

NON-YOUNGS/S: No cause ...

Young-making rate:hits that cause blocks to move to the head of the buffer pool

Not:hits that don't cause blocks to move to the head of the buffer pool (due to the delay not being met)

(young-making rate and not rate won't normally add up to the overall buffer pool hits rate)

5. New version of the 5.5 multi-pool mechanism: set innodb_buffer_pool_instances greater than 1, can use multiple pool, so that access to the competition is dispersed. The size of each pool is equal to innodb_buffer_pool_size/innodb_buffer_pool_instances. (Note: Each block must be at least less than 1G.) ）