The 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.
Buffer pool is a primary area of memory used to cache InnoDB table and index data. Buffer pools can keep frequently used data in memory for direct access. In a dedicated database server, up to 80% of the physical memory can be used as buffer pools.
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 sublists:
At the head, a sublist of new (“young” )pages that were accessed recently
At the tail, a sublist of old pages that were accessed less recently
Buffer pools are managed using an LRU algorithm. When a new page is inserted into the buffer pool, the least recently used pages in the buffer pool are ejected and the new page is added to the intermediate list. The middle insert policy divides the list into two sublists:
In the header, it’s a newer list, and it’s going to be used a lot.
At the end, you have older lists that are used less often.
The algorithm keeps frequently used pages in the new sublist. The old sublist contains less frequently used pages; these pages are candidates for eviction.
The LRU algorithm keeps frequently accessed pages in a new sublist. The old sublist contains pages that are less visited, and these pages are candidates for expulsion.
Buffer Pool Configuration
You can configure the various aspects of the buffer pool to improve performance.
Buffer pools can be configured in many ways to improve performance.
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. See
You can improve database performance by increasing the size of the buffer pool. The larger the buffer pool, the more data pages InnoDB can hold, the more InnoDB is like a page-game in-memory database.
On 64-bit systems with sufficient memory, you can split the buffer pool into multiple parts to minimize contention for memory structures among concurrent operations. For details
In 64-bit operating systems, buffer pools can be divided into multiple parts if there is sufficient memory.
You can keep frequently accessed data in memory regardless of sudden spikes of awww.sangpi.comctivity from operations that would bring large amounts of infrequently accessed data into the buffer pool. For details,
Frequently accessed data can be persisted in the buffer pool.
You can control how and when to perform read-ahead requests to prefetch pages into the buffer pool asynchronously in Brimming with anticipation that the pages are needed soon. For details, see the buffer pool can be read in advance.
You can control when background flushing occurs and whether or not the rate of flushing is dynamically adjusted based on workload. For details, see
You can control the buffer pool refresh frequency machine refresh ratio.
You can configure how InnoDB preserves the current buffer pool state to avoid a lengthy warmup period after a server restart. For details, see
You can save the state of the buffer pool to avoid a long restart.
Monitoring the Buffer Pool Using the InnoDB Standard Monitor
InnoDB Standard Monitor output, which can be accessed using 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:
Show Engine InnoDB Status Command to monitor the status of buffer pools. Buffer Pool AND Memory: Buffer Pool AND Memory