InnoDB Architecture

 

Buffer Pool

In MySQL, buffer pool is an area in main memory where InnoDB caches table and index data as it is accessed. The buffer pool permits frequently used data to be accessed directly from memory, which speeds up processing. On dedicated servers, up to 80% of physical memory is often assigned to the buffer pool.

For efficiency MySQL use Least Recently Used (LRU) algorithm in order to manage new pages.

Buffer Pool LRU Algorithm

The buffer pool is managed as a list using a variation of the LRU algorithm. When room is needed to add a new page to the buffer pool, the least recently used page is evicted and a new page is added to the middle of the list. This midpoint insertion strategy treats the list as two sub-lists:

• At the head, a sub-list of new (“young”) pages that were accessed recently

• At the tail, a sub-list of old pages that were accessed less recently

The algorithm keeps frequently used pages in the new sub-list. The old sub-list contains less frequently used pages; these pages are candidates for eviction.

By default, the algorithm operates as follows:

• 3/8 (37%) of the buffer pool is devoted to the old sub-list.

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

• When InnoDB reads a page into the buffer pool, it initially inserts it at the midpoint (the head of the old sub-list). A page can be read because it is required for a user-initiated operation such as an SQL query, or as part of a read-ahead operation performed automatically by InnoDB.

• Accessing a page in the old sub-list makes it “young”, moving it to the head of the new sub-list. 

• As the database operates, pages in the buffer pool that are not accessed “age” by moving toward the tail of the list.  

• 
  

 

Monitoring the Buffer Pool Using the InnoDB Standard Monitor

InnoDB Standard Monitor output, which can be accessed using SHOW ENGINE INNODB STATUS, provides metrics regarding operation of the buffer pool. Buffer pool metrics are located in the BUFFER POOL AND MEMORY section of InnoDB Standard Monitor output:

---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 2198863872 Dictionary memory allocated 776332 Buffer pool size 131072 Free buffers 124908 Database pages 5720 Old database pages 2071 Modified db pages 910 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 4, not young 0 0.10 youngs/s, 0.00 non-youngs/s Pages read 197, created 5523, written 5060 0.00 reads/s, 190.89 creates/s, 244.94 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 5720, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0]

InnoDB Buffer Pool Metrics