SoFunction
Updated on 2025-04-12

Detailed explanation of the storage structure and memory elimination mechanism of mysql Buffer Pool

MySQL InnoDB storage engineBuffer PoolIt is the core component of database performance optimization, used to cache data pages and index pages to reduce disk I/O operations. Its storage structure and memory elimination mechanism are designed in complex and efficient. The following is a detailed analysis:

Buffer Pool Storage Structure

1. Infrastructure

  • Data Page:Buffer Pool's basic storage unit, default size for each page16KB(Available throughinnodb_page_sizeAdjustment). Data page stores table data, indexes, undo logs, etc.
  • Control Block: Each data page corresponds to a control block, containing the meta information of the page (such as page number, LSN, number of visits, dirty page marks, etc.), the size is about5%–10%Buffer Pool memory.

2. Link List Management

Buffer Pool manages the allocation and status of pages through three core linked lists:

Free List:

Maintain all unused free pages. When a new data page needs to be loaded, free pages are preferred from Free List.

LRU List (Least Recently Used Linked List):

Manage used pages, sorted by access time, for memory phasing decisions. InnoDB optimizes traditional LRUs and adoptsGeneration LRU (Segmented LRU)：

  • Young SubList(New Generation): Store frequently visited hot pages.
  • Old SubList(Old age): Store newly loaded pages or fewer pages are accessed.
  • Midpoint Insertion: The new page is inserted into the LRU List when it is loaded for the first time3/8(Depend oninnodb_old_blocks_pctControl, default 37%) to avoid operations such as full table scanning to contaminate hot spot data.

Flush List:

Record all modified dirty pages (Dirty Pages), sorted by the earliest modification time, and are regularly flushed by the background thread.

3. Multiple instances and partitions

  • Buffer Pool Instances:passinnodb_buffer_pool_instancesDivide Buffer Pool into multiple independent instances to reduce lock competition.
  • ChunkDistribution mechanism: Each Buffer Pool instance consists of multiple Chunks (default 128MB) and supports dynamic resize (innodb_buffer_pool_chunk_size）。

Memory elimination mechanism

1. Trigger condition

  • When Free List is empty, you need to eliminate the old page from the LRU List to free up space.
  • The background thread (Page Cleaner) actively cleans up dirty pages to maintain the proportion of free pages.

2. Improved LRU algorithm

Access frequency and timeliness:

  • The new page loads to the head of the Old SubList for the first time.
  • If the page survives more than the Old SubListinnodb_old_blocks_time(Default 1000ms) and then be accessed again, then moved to Young SubList.
  • When the page of Young SubList is accessed, it only moves to the head of the Young area (no overall adjustment of the linked list to reduce overhead).

Elimination strategy:

  • Prioritize pages at the end of Old SubList.
  • If the length of Young SubList exceeds the threshold, the page at its tail may be eliminated.

3. Dirty page processing

  • Background threads regularly brush dirty pages in Flush List (advance Checkpoint according to LSN).
  • The dirty pages after the brushing disk become clean pages and can be released to the Free List or retained on the LRU List.

4. Parameter tuning

  • innodb_buffer_pool_size: Total memory size, it is recommended to set to 50%~80% of physical memory.
  • innodb_old_blocks_pct: Control the proportion of Old SubList (default 37%), and the full table scanning scenario can be appropriately lowered.
  • innodb_old_blocks_time: Protect Old SubList from being contaminated by short-term access, and this value can be increased when scanning frequently.

Monitoring and Optimization

1. Key monitoring indicators

SHOW ENGINE INNODB STATUS;  -- Check Buffer Pool state
  • Pages young / Pages not young: Number of page movements in Young and Old areas.
  • Buffer pool hit rate: Cache hit rate (target close to 100%).
  • Modified db pages: Current number of dirty pages.

2. Optimization suggestions

  • Preheat cache: After restartingSELECT * FROM table;Actively load data.
  • Avoid full table scanning: Large table scans may cause Old SubList to be filled with invalid data.
  • Using SSD: Reduce the impact of brushing disk on performance.

Summarize

InnoDB Buffer Pool balances memory utilization and access efficiency through generations of LRUs and linked list structures, and combines the dirty page refresh mechanism to ensure data consistency. Rational configuration of parameters and monitoring hit rate are the key to optimizing database performance.

The above is personal experience. I hope you can give you a reference and I hope you can support me more.