InnoDB内存架构之Buffer Pool缓冲池

1 问题背景

前面研究了InnoDB存储引擎的架构，InnoDB包含了内存架构以及磁盘架构，本篇研究内存架构。

参考自：MySQL官方文档之InnoDB内存架构

2 回顾

InnoDB的架构如下图所示：

3 InnoDB内存架构

如上面的InnoDB架构图所知， InnoDB的内存架构 由 Buffer Pool（缓冲池）、Change Buffer（更改缓冲区）、Adaptive Hash Index（自适应哈希索引）、Log Buffer（日志缓冲区） 四个组件构成。

4 Buffer Pool 缓冲池

原文
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.

缓冲池处于计算机系统主存中的某个区域，用于InnoDB访问时缓存表以及索引数据。缓冲池允许直接从内存访问常用数据，从而加快处理速度。在专用服务器上，通常多达80%的物理内存分配给缓冲池。

原文
For efficiency of high-volume read operations, the buffer pool is divided into pages that can potentially hold multiple rows. For efficiency of cache management, the buffer pool is implemented as a linked list of pages; data that is rarely used is aged out of the cache using a variation of the least recently used (LRU) algorithm.

为了提高大量的读取操作，缓冲池被划分成可能包含多行的页。为了有效管理这些页的数据，缓冲池被实现为以页为元素的链表。通过一种 变体的LRU 算法（Least Recently Used Algorithm，最近最少使用算法）使最近最少使用的数据 老化（即清除不常用的缓存）。

Knowing how to take advantage of the buffer pool to keep frequently accessed data in memory is an important aspect of MySQL tuning.

了解如何利用缓冲池使经常访问的数据保持在内存中是MySQL调优的一个重要方面

原文
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

缓冲池使用LRU算法的变体形成一个列表进行管理，结构如下图所示。当缓冲池需要添加新页的时候，最近最少使用的页会被驱逐，并且一个新页会被添加到 列表的中间 （注意，并不是列表的正中间，具体值由LRU算法的变体决定）。这个 中点插入策略 将缓冲池列表逻辑上分割成两个子列表。

• 列表头部至中间点的数据，被视为是最近常访问的，下文称这些为年轻页
• 中间点至列表尾部的数据，被视为最近较少访问的，下问称这些为旧页

缓冲池列表如下图所示：

原文
The algorithm keeps frequently used pages in the new sublist. The old sublist contains less frequently used pages; these pages are candidates for eviction.

算法保持最常访问的页在新的子列表。旧的子列表包含较少访问的常用页，这些旧页是驱逐的候选页。

原文
By default, the algorithm operates as follows:

• 3/8 of the buffer pool is devoted to the old sublist.

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

• When InnoDB reads a page into the buffer pool, it initially inserts it at the midpoint (the head of the old sublist). 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 sublist makes it “young”, moving it to the head of the new sublist. If the page was read because it was required by a user-initiated operation, the first access occurs immediately and the page is made young. If the page was read due to a read-ahead operation, the first access does not occur immediately and might not occur at all before the page is evicted.

• As the database operates, pages in the buffer pool that are not accessed “age” by moving toward the tail of the list. Pages in both the new and old sublists age as other pages are made new. Pages in the old sublist also age as pages are inserted at the midpoint. Eventually, a page that remains unused reaches the tail of the old sublist and is evicted.

默认情况下，操作如下：

• 缓冲池的 3/8 专用于旧子列表
• 列表的中点是新子列表尾部与旧子列表头部的边界
• 当InnoDB读取页到缓冲池时，它会初始化地将它插入到中间点（即旧子列表的头部）。一页可以被读取是因为这是用户启动的操作（比如一个SQL查询）或者是InnoDB预读操作的一部分。
• 访问旧子列表中的页会使这个页变得年轻，该页会被移动到新子列表的头部。移动的 触发时机 取决于页被读取的原因（用户启动的操作或者InnoDB的预读操作）。如果页读取是由于用户启动的操作而引起的，第一次访问页后会立即将页移动到新子列表的头部。如果页读取是InnoDB预读操作引起的，第一次访问页后不会立即发生移动页的操作，或者直到该页被驱逐都不会发生。
• 随着数据库的运行，缓冲池中未被访问的页会通过向列表尾部移动来来 “老化”（即被驱逐的概率加大）。 新旧子列表的页会随着其他页的更新而被老化。旧子列表的页也会随着插到中间点的页而老化。最终，一个未使用的页面到达旧子列表的尾部并被驱逐。

原文
By default, pages read by queries are immediately moved into the new sublist, meaning they stay in the buffer pool longer. A table scan, performed for a mysqldump operation or a SELECT statement with no WHERE clause, for example, can bring a large amount of data into the buffer pool and evict an equivalent amount of older data, even if the new data is never used again. Similarly, pages that are loaded by the read-ahead background thread and accessed only once are moved to the head of the new list. These situations can push frequently used pages to the old sublist where they become subject to eviction. For information about optimizing this behavior, see Section 15.8.3.3, “Making the Buffer Pool Scan Resistant”, and Section 15.8.3.4, “Configuring InnoDB Buffer Pool Prefetching (Read-Ahead)”.

默认情况下，通过查询触发读取到缓冲池的页会立即移动到新子列表，这意味着这些页会停留在缓冲池的时间更长。一个mysqldump操作或者一个不带where条件的查询语句引起的全表扫描，会将大量的数据加载到缓冲池中并且驱逐相等量的旧数据，即使这些新加载进来的大量数据以后从未被使用。 类似地，由后台进程预读取加载到缓冲池中的页，即使只被访问过一次，这些页都会移动到新子列表的头部。这些场景都会将经常使用的页推向旧子列表，在那里他们会成为被驱逐的对象。有关优化此行为的信息，请参阅15.8.3.3节，“使缓冲池具有抵抗性”和第15.8.3.4节“配置InnoDB缓冲池预取（预读）”。

原文
InnoDB Standard Monitor output contains several fields in the BUFFER POOL AND MEMORY section regarding operation of the buffer pool LRU algorithm. For details, see Monitoring the Buffer Pool Using the InnoDB Standard Monitor.

InnoDB标准监视器输出包含有关于缓冲池LRU算法操作的“缓冲池以及内存”部分的几个字段。

5 缓冲池的有关配置

了解缓冲池的配置有助于提高MySQL的性能

原文
You can configure the various aspects of the buffer pool 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 Section 15.8.3.1, “Configuring InnoDB Buffer Pool Size”.

• 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, see Section 15.8.3.2, “Configuring Multiple Buffer Pool Instances”.

• You can keep frequently accessed data in memory regardless of sudden spikes of activity from operations that would bring large amounts of infrequently accessed data into the buffer pool. For details, see Section 15.8.3.3, “Making the Buffer Pool Scan Resistant”.

• You can control how and when to perform read-ahead requests to prefetch pages into the buffer pool asynchronously in anticipation of impending need for them. For details, see Section 15.8.3.4, “Configuring InnoDB Buffer Pool Prefetching (Read-Ahead)”.

• You can control when background flushing occurs and whether or not the rate of flushing is dynamically adjusted based on workload. For details, see Section 15.8.3.5, “Configuring Buffer Pool Flushing”.

• You can configure how InnoDB preserves the current buffer pool state to avoid a lengthy warmup period after a server restart. For details, see Section 15.8.3.6, “Saving and Restoring the Buffer Pool State”.

你可以缓冲池的各种方面来提高性能：

• 理想地，你可以根据实际情况（笔者理解是服务器的资源）将缓冲池的大小设置得尽可能大，从而留出足够多的内存给服务器上的其他进程运行，而不会出现过多的分页。缓冲池越大，InnoDB就越像一个内存数据库，从磁盘读取数据一次，后续的读取期间从内存访问数据。详情见配置InnoDB缓冲池大小。
• 在具有足够内存的64位系统上，您可以将缓冲池拆分成多个部分，以减少并发操作之间对内存结构的争用。详情见配置多个缓冲池实例。
• 你可以将经常访问的数据保留在内存中，而不管操作的活动突然激增，这些操作会将大量不常用的数据带入缓冲池。详情见使缓冲池扫描具有抵抗性
• 你可以控制如何以及何时执行预读取请求来将分页异步地预取到缓冲池中，以应对即将到来的需求。详情见配置InnoDB缓冲池预取（预读）.
• 你可以控制后台何时刷新以及是否根据工作负载动态调整刷新速率。详情见配置缓冲池刷新
• 你可以配置InnoDB如何保存当前缓冲池的状态来避免服务器重启后长时间的预热。详情见保存和回复缓冲池的状态