【MySQL精通之路】InnoDB配置(8)-缓存池配置调优

目录

1 配置InnoDB缓冲池大小

1.1 配置InnoDB缓存池块大小

1.2 在线配置InnoDB缓冲池大小

1.3 监视联机缓冲池调整大小的进度

1.4 在线缓冲池内部调整大小

2 配置多个缓冲池实例

3 使缓冲池抗扫描

4 配置InnoDB缓冲池预取（预读）

5 配置缓冲池刷新

5.1 页清理线程

5.2 自适应刷新

5.3 限制空闲期间的缓冲区刷新

6 保存和恢复缓冲池状态

6.1 配置缓冲池页面的转储百分比

6.2 关闭时保存缓冲池状态，启动时恢复

6.3 联机保存和恢复缓冲池状态

6.5 中止缓冲池加载操作

6.6 使用性能架构监视缓冲池加载进度

7 从核心文件中排除缓冲池页面

---

本节提供InnoDB缓冲池的配置和调优信息。

1 配置InnoDB缓冲池大小

当增加或减少innodb_buffer_pool_size时，操作是分块执行的

区块大小由innodb_buffer_pool_chunk_size 配置选项定义，默认值为128M。

缓冲池大小必须始终等于或等于（n倍于 块大小 x 缓冲池实例数）

innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数。

否则缓冲池大小将自动调整为等于innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances倍数的值。

在以下示例中，innodb_buffer_pool_size设置为8G，innodl_buffer_poor_instances设置为16。innodb_buffer_pool_chunk_size为128M，为默认值。

8G是一个有效的。

8G是innodb_buffer_pool_instance=16*innob_buffer_pool_chunk_size=128M的倍数，即2G。

$> mysqld --innodb-buffer-pool-size=8G --innodb-buffer-pool-instances=16

mysql> SELECT @@innodb_buffer_pool_size/1024/1024/1024;
+------------------------------------------+
| @@innodb_buffer_pool_size/1024/1024/1024 |
+------------------------------------------+
|                           8.000000000000 |
+------------------------------------------+

在本例中，innodb_buffer_pool_size设置为9G，innodl_buffer_poor_instances设置为16。innodb_buffer_pool_chunk_size为128M，为默认值。在这种情况下，9G不是innodb_buffer_pool_instance=16*innodb_buffer_pool_chunk_size=128M的倍数，因此innodb_uffer_pool_size被调整为10G，这是innodd_buffer_pool_chunk_size*innodb_buffer_poor_instances的倍数。

$> mysqld --innodb-buffer-pool-size=9G --innodb-buffer-pool-instances=16

mysql> SELECT @@innodb_buffer_pool_size/1024/1024/1024;
+------------------------------------------+
| @@innodb_buffer_pool_size/1024/1024/1024 |
+------------------------------------------+
|                          10.000000000000 |
+------------------------------------------+

1.1 配置InnoDB缓存池块大小

innodb_buffer_pool_chunk_size可以以1MB（1048576字节）为单位增加或减少，但只能在启动时、命令行字符串或MySQL配置文件中进行修改。

命令行：

$> mysqld --innodb-buffer-pool-chunk-size=134217728

配置文件：

[mysqld]
innodb_buffer_pool_chunk_size=134217728

更改innodb_buffer_pool_chunk_size时，以下条件适用：

如果在初始化缓冲池时，新的innodb_buffer_pool_chunk_size值*innodb_buffer_pool_instances大于当前缓冲池大小，则innodb_uffer_pool_chunk_size将被截断为innodb_缓冲池_size/innodb_buffer_pool _instances。

例如，如果缓冲池被初始化为2GB（2147483648字节）的大小、4个缓冲池实例和1GB（1073741824字节）的块大小，则块大小被截断为等于innodb_buffer_pool_size/innodb_buffer_pool_instances的值，如下所示：

$> mysqld --innodb-buffer-pool-size=2147483648 --innodb-buffer-pool-instances=4
--innodb-buffer-pool-chunk-size=1073741824;

mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                2147483648 |
+---------------------------+

mysql> SELECT @@innodb_buffer_pool_instances;
+--------------------------------+
| @@innodb_buffer_pool_instances |
+--------------------------------+
|                              4 |
+--------------------------------+

# Chunk size was set to 1GB (1073741824 bytes) on startup but was
# truncated to innodb_buffer_pool_size / innodb_buffer_pool_instances

mysql> SELECT @@innodb_buffer_pool_chunk_size;
+---------------------------------+
| @@innodb_buffer_pool_chunk_size |
+---------------------------------+
|                       536870912 |
+---------------------------------+

缓冲池大小必须始终等于或等于innodb_Buffer_pool_chunk_size*innodb_Buffer_pool_instances的倍数。

如果您更改innodb_buffer_pool_chunk_size，innodb_buffer_pool_size会自动调整为等于innodb_uffer_pool_chunk_size*innodb_buffer_pool_instances或其倍数的值。

在初始化缓冲池时进行调整。以下示例演示了这种行为：

# The buffer pool has a default size of 128MB (134217728 bytes)

mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                 134217728 |
+---------------------------+

# The chunk size is also 128MB (134217728 bytes)

mysql> SELECT @@innodb_buffer_pool_chunk_size;
+---------------------------------+
| @@innodb_buffer_pool_chunk_size |
+---------------------------------+
|                       134217728 |
+---------------------------------+

# There is a single buffer pool instance

mysql> SELECT @@innodb_buffer_pool_instances;
+--------------------------------+
| @@innodb_buffer_pool_instances |
+--------------------------------+
|                              1 |
+--------------------------------+

# Chunk size is decreased by 1MB (1048576 bytes) at startup
# (134217728 - 1048576 = 133169152):

$> mysqld --innodb-buffer-pool-chunk-size=133169152

mysql> SELECT @@innodb_buffer_pool_chunk_size;
+---------------------------------+
| @@innodb_buffer_pool_chunk_size |
+---------------------------------+
|                       133169152 |
+---------------------------------+

# Buffer pool size increases from 134217728 to 266338304
# Buffer pool size is automatically adjusted to a value that is equal to
# or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances

mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                 266338304 |
+---------------------------+

此示例演示了相同的行为，但具有多个缓冲池实例：

# The buffer pool has a default size of 2GB (2147483648 bytes)

mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                2147483648 |
+---------------------------+

# The chunk size is .5 GB (536870912 bytes)

mysql> SELECT @@innodb_buffer_pool_chunk_size;
+---------------------------------+
| @@innodb_buffer_pool_chunk_size |
+---------------------------------+
|                       536870912 |
+---------------------------------+

# There are 4 buffer pool instances

mysql> SELECT @@innodb_buffer_pool_instances;
+--------------------------------+
| @@innodb_buffer_pool_instances |
+--------------------------------+
|                              4 |
+--------------------------------+

# Chunk size is decreased by 1MB (1048576 bytes) at startup
# (536870912 - 1048576 = 535822336):

$> mysqld --innodb-buffer-pool-chunk-size=535822336

mysql> SELECT @@innodb_buffer_pool_chunk_size;
+---------------------------------+
| @@innodb_buffer_pool_chunk_size |
+---------------------------------+
|                       535822336 |
+---------------------------------+

# Buffer pool size increases from 2147483648 to 4286578688
# Buffer pool size is automatically adjusted to a value that is equal to
# or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances

mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                4286578688 |
+---------------------------+

更改innodb_buffer_pool_chunk_size时应小心，因为更改此值会增加缓冲池的大小，如上面的示例所示。

在更改innodb_buffer_pool_chunk_size之前，请计算对innodb_buffer_pool_size的影响，以确保生成的缓冲池大小是可接受的。

注意：

为了避免潜在的性能问题，块的数量（innodb_buffer_pool_size/innodb_buffer_pool_chunk_size）不应超过1000。

1.2 在线配置InnoDB缓冲池大小

innodb_buffer_pool_size配置选项可以使用set语句动态设置，允许您在不重新启动服务器的情况下调整缓冲池的大小。例如

mysql> SET GLOBAL innodb_buffer_pool_size=402653184;

注意：

缓冲池大小必须等于或等于innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances的倍数。更改这些变量设置需要重新启动服务器。

通过InnoDB API执行的活动事务和操作应在调整缓冲池大小之前完成。

启动调整大小操作时，直到所有活动事务都完成后，该操作才会启动。

调整大小操作进行后，需要访问缓冲池的新事务和操作必须等待调整大小操作完成。

该规则的例外情况是，当缓冲池进行碎片整理时，允许对缓冲池进行并发访问，而当缓冲池大小减小时，页面将被撤回。

允许并发访问的一个缺点是，当页面被撤回时，可能会导致可用页面暂时短缺。

注意：

如果在缓冲池大小调整操作开始后启动嵌套事务，则嵌套事务可能会失败。

1.3 监视联机缓冲池调整大小的进度

Innodb_buffer_pool_resize_status变量报告一个字符串值，指示缓冲池调整大小的进度；例如

mysql> SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';
+----------------------------------+----------------------------------+
| Variable_name                    | Value                            |
+----------------------------------+----------------------------------+
| Innodb_buffer_pool_resize_status | Resizing also other hash tables. |
+----------------------------------+----------------------------------+

Innodb_buffer_pool_resize_status变量报告一个字符串值，指示bu。从MyQL 8.0.31，您还可以使用Innodb_buffer_pool_resize_status_code和Innodb_uffer_pool_resize _status_progress状态变量来监测在线缓冲池大小调整操作，这些变量报告数值，更适合用于编程监测。

Innodb_buffer_pool_resize_status_code状态变量报告一个状态代码，指示在线缓冲池大小调整操作的阶段。状态代码包括：fer池调整进度；例如

0:没有正在进行的调整大小操作

1：开始调整大小

2：禁用AHI（自适应哈希索引）

3：撤销区块

4：收购Global Lock

5：调整池大小

6：调整哈希大小

7：调整大小失败

Innodb_buffer_pool_resize_status_progress状态变量报告一个百分比值，指示每个阶段的进度。处理完每个缓冲池实例后，将更新百分比值。当状态（由Innodb_buffer_pool_resize_status_code报告）从一种状态更改为另一种状态时，百分比值重置为0。

以下查询返回一个字符串值，指示缓冲池调整大小的进度，一个代码，指示操作的当前阶段以及该阶段的当前进度，以百分比值表示：

SELECT variable_name, variable_value 
 FROM performance_schema.global_status 
 WHERE LOWER(variable_name) LIKE "innodb_buffer_pool_resize%";

缓冲池调整大小的进度也可以在服务器错误日志中看到。此示例显示了在增加缓冲池大小时记录的注释：

[Note] InnoDB: Resizing buffer pool from 134217728 to 4294967296. (unit=134217728)
[Note] InnoDB: disabled adaptive hash index.
[Note] InnoDB: buffer pool 0 : 31 chunks (253952 blocks) was added.
[Note] InnoDB: buffer pool 0 : hash tables were resized.
[Note] InnoDB: Resized hash tables at lock_sys, adaptive hash index, dictionary.
[Note] InnoDB: completed to resize buffer pool from 134217728 to 4294967296.
[Note] InnoDB: re-enabled adaptive hash index.

此示例显示减小缓冲池大小时记录的注释：

[Note] InnoDB: Resizing buffer pool from 4294967296 to 134217728. (unit=134217728)
[Note] InnoDB: disabled adaptive hash index.
[Note] InnoDB: buffer pool 0 : start to withdraw the last 253952 blocks.
[Note] InnoDB: buffer pool 0 : withdrew 253952 blocks from free list. tried to relocate 
0 pages. (253952/253952)
[Note] InnoDB: buffer pool 0 : withdrawn target 253952 blocks.
[Note] InnoDB: buffer pool 0 : 31 chunks (253952 blocks) was freed.
[Note] InnoDB: buffer pool 0 : hash tables were resized.
[Note] InnoDB: Resized hash tables at lock_sys, adaptive hash index, dictionary.
[Note] InnoDB: completed to resize buffer pool from 4294967296 to 134217728.
[Note] InnoDB: re-enabled adaptive hash index.

从MySQL 8.0.31开始，以--log error verbose=3启动服务器，会在联机缓冲池调整大小操作期间将额外信息记录到错误日志中。附加信息包括Innodb_buffer_pool_resize_status_code报告的状态代码和Innodb_buffer_pool_resize_status_progress报告的进度百分比值。

[Note] [MY-012398] [InnoDB] Requested to resize buffer pool. (new size: 1073741824 bytes)
[Note] [MY-013954] [InnoDB] Status code 1: Resizing buffer pool from 134217728 to 1073741824
(unit=134217728).
[Note] [MY-013953] [InnoDB] Status code 1: 100% complete
[Note] [MY-013952] [InnoDB] Status code 1: Completed
[Note] [MY-013954] [InnoDB] Status code 2: Disabling adaptive hash index.
[Note] [MY-011885] [InnoDB] disabled adaptive hash index.
[Note] [MY-013953] [InnoDB] Status code 2: 100% complete
[Note] [MY-013952] [InnoDB] Status code 2: Completed
[Note] [MY-013954] [InnoDB] Status code 3: Withdrawing blocks to be shrunken.
[Note] [MY-013953] [InnoDB] Status code 3: 100% complete
[Note] [MY-013952] [InnoDB] Status code 3: Completed
[Note] [MY-013954] [InnoDB] Status code 4: Latching whole of buffer pool.
[Note] [MY-013953] [InnoDB] Status code 4: 14% complete
[Note] [MY-013953] [InnoDB] Status code 4: 28% complete
[Note] [MY-013953] [InnoDB] Status code 4: 42% complete
[Note] [MY-013953] [InnoDB] Status code 4: 57% complete
[Note] [MY-013953] [InnoDB] Status code 4: 71% complete
[Note] [MY-013953] [InnoDB] Status code 4: 85% complete
[Note] [MY-013953] [InnoDB] Status code 4: 100% complete
[Note] [MY-013952] [InnoDB] Status code 4: Completed
[Note] [MY-013954] [InnoDB] Status code 5: Starting pool resize
[Note] [MY-013954] [InnoDB] Status code 5: buffer pool 0 : resizing with chunks 1 to 8.
[Note] [MY-011891] [InnoDB] buffer pool 0 : 7 chunks (57339 blocks) were added.
[Note] [MY-013953] [InnoDB] Status code 5: 100% complete
[Note] [MY-013952] [InnoDB] Status code 5: Completed
[Note] [MY-013954] [InnoDB] Status code 6: Resizing hash tables.
[Note] [MY-011892] [InnoDB] buffer pool 0 : hash tables were resized.
[Note] [MY-013953] [InnoDB] Status code 6: 100% complete
[Note] [MY-013954] [InnoDB] Status code 6: Resizing also other hash tables.
[Note] [MY-011893] [InnoDB] Resized hash tables at lock_sys, adaptive hash index, dictionary.
[Note] [MY-011894] [InnoDB] Completed to resize buffer pool from 134217728 to 1073741824.
[Note] [MY-011895] [InnoDB] Re-enabled adaptive hash index.
[Note] [MY-013952] [InnoDB] Status code 6: Completed
[Note] [MY-013954] [InnoDB] Status code 0: Completed resizing buffer pool at 220826  6:25:46.
[Note] [MY-013953] [InnoDB] Status code 0: 100% complete

1.4 在线缓冲池内部调整大小

调整大小操作由后台线程执行。当增加缓冲池的大小时，调整大小操作：

按块添加页面（块大小由innodb_buffer_pool_chunk_size定义）

转换哈希表、列表和指针以使用内存中的新地址

将新页面添加到可用列表

在进行这些操作时，会阻止其他线程访问缓冲池。

当减小缓冲池的大小时，调整大小操作：

对缓冲池进行碎片整理并提取（释放）页面

按块删除页面（块大小由innodb_buffer_pool_chunk_size定义）

转换哈希表、列表和指针以使用内存中的新地址

在这些操作中，只有对缓冲池进行碎片整理和撤回页面才能允许其他线程同时访问缓冲池。

2 配置多个缓冲池实例

对于缓冲池在千兆字节范围内的系统，将缓冲池划分为单独的实例可以通过减少不同线程读取和写入缓存页面时的争用来提高并发性。

此功能通常适用于缓冲池大小在千兆字节范围内的系统。

使用innodb_buffer_pool_instances配置选项配置多个缓冲池实例，还可以调整innodb_buffer_pool_size值。

当InnoDB缓冲池很大时，可以通过从内存中检索来满足许多数据请求。

您可能会遇到多个线程试图同时访问缓冲池的瓶颈。

您可以启用多个缓冲池来最大限度地减少这种争用。

使用哈希函数，将存储在缓冲池中或从缓冲池中读取的每个页面随机分配给其中一个缓冲池。

每个缓冲池管理自己的空闲列表、刷新列表、LRU以及连接到缓冲池的所有其他数据结构。

在MySQL 8.0之前，每个缓冲池都由自己的缓冲池互斥体保护。

在MySQL 8.0及更高版本中，缓冲池互斥被几个列表和哈希保护互斥所取代，以减少争用。

要启用多个缓冲池实例，请将innodb_buffer_pool_instances配置选项设置为大于1（默认值）到64（最大值）的值。

只有将innodb_buffer_pool_size设置为1GB或更大时，此选项才会生效。

您指定的总大小在所有缓冲池中分配。

为了获得最佳效率，请指定innodb_buffer_pool_instances和innodb_buffer_pool_size的组合，以便每个缓冲池实例至少为1GB。

3 使缓冲池抗扫描

InnoDB没有使用严格的LRU算法，而是使用了一种技术来最大限度地减少被带入缓冲池且不再被访问的数据量。（一句话解释就是减少热数据的误判率）

目标是确保频繁访问（“热”）页面保留在缓冲池中，即使预读和全表扫描会带来新的块，这些块可能在以后访问，也可能不会访问。

新读取的块被插入LRU列表的中间。所有新读取的页面都插入到距离LRU列表尾部3/8的位置。

第一次在缓冲池中访问页面时，页面会移动到列表的前面（最近使用的末尾）。

因此，从未访问过的页面永远不会出现在LRU列表的前面部分，并且比使用标准的LRU方法更快地“老化”。

这种设计将LRU列表分为两段，其中插入点下游的页面被认为是“旧的”，是LRU驱逐的理想候选块

有关InnoDB缓冲池的内部工作原理和LRU算法的具体说明，请参阅下面博客中的“缓存池”。

【MySQL精通之路】InnoDB(5)-内存结构-CSDN博客

您可以控制LRU列表中的插入点，并选择InnoDB是否对通过表或索引扫描带入缓冲池的块应用相同的优化。

配置参数innodb_old_block_pct控制LRU列表中“旧”块的百分比。

innodb_old_block_pct的默认值为37，对应于原始的固定比率3/8。该值范围为5（缓冲池中的新页面很快老化）到95（只有5%的缓冲池保留给热页面，使算法接近于熟悉的LRU策略）。

防止缓冲池被预读搅动的优化可以避免由于表或索引扫描而引起的类似问题。

在这些扫描中，数据页通常会被快速连续访问几次，并且再也不会被查询。配置参数innodb_old_block_time指定第一次访问页面后的时间窗口（以毫秒为单位），在此期间可以访问页面而不必移动到LRU列表的前端（最近使用的端）。innodb_old_block_time的默认值为1000。增加该值会使越来越多的块从缓冲池中更快地老化。

innodb_old_block_pct和innodb_ld_block_time都可以在MySQL选项文件（my.cnf或my.ini）中指定，也可以在运行时使用SET GLOBAL语句进行更改。

在运行时更改值需要足够的权限来设置全局系统变量。参见“系统变量权限”。

【MySQL精通之路】系统变量-系统变量权限-CSDN博客

为了帮助您评估设置这些参数的效果，SHOW ENGINE INNODB STATUS命令报告缓冲池统计信息。

有关详细信息，请参阅使用InnoDB标准监视器监视缓冲池。

由于这些参数的影响可能因硬件配置、数据和工作负载的详细信息而异，因此在任何性能关键型或生产环境中更改这些设置之前，请始终进行基准测试以验证其有效性。

在混合工作负载中，大多数活动都是OLTP类型的，具有周期性的批处理报告查询，这会导致大量扫描，在批处理运行期间设置innodb_old_block_time的值可以帮助将正常工作负载的工作集保留在缓冲池中。

当扫描无法完全容纳在缓冲池中的大表时，将innodb_old_block_pct设置为小值可以防止只读取一次的数据占用缓冲池的很大一部分。

例如，设置innodb_old_block_pct=5将只读取一次的数据限制为缓冲池的5%。

当扫描适合内存的小表时，在缓冲池中移动页面的开销较小，因此可以将innodb_old_block_pct保留为默认值，甚至更高，例如innodb_ld_block_pct=50。

innodb_old_block_time参数的影响比innodb_ld_block_pct参数更难预测，相对较小，并且随着工作负载的变化而变化更大。

要获得最佳值，如果通过调整innodb_old_block_pct的性能改进还不够，请执行自己的基准测试。

4 配置InnoDB缓冲池预取（预读）

预读请求是一种I/O请求，用于异步预取缓冲池中的多个页面，以应对即将到来的对这些页面的需求。

这些请求将所有页面放在一个范围内。InnoDB使用两种预读算法来提高I/O性能：

线性预读是一种根据缓冲池中按顺序访问的页面来预测可能很快需要哪些页面的技术。

通过使用配置参数InnoDB_read_ahead_threshold调整触发异步读取请求所需的有序页面访问次数，可以控制InnoDB何时执行预读操作。

在添加此参数之前，InnoDB只会在读取当前数据块的最后一页时计算是否对整个下一个数据块发出异步预取请求。

配置参数innodb_read_ahead_threshold控制innodb在检测顺序页面访问模式时的敏感度。

如果从一个数据块顺序读取的页数大于或等于innodb_read_ahead_threshold，innodb将启动整个后续数据块的异步预读操作。

innodb_read_ahead_threshold可以设置为0-64之间的任何值。

默认值为56。该值越高，访问模式检查就越严格。

例如，如果将该值设置为48，则只有当顺序访问了当前扩展区中的48个页面时，InnoDB才会触发线性预读请求。

如果该值为8，即使按顺序访问数据块中只有8个页面，InnoDB也会触发异步预读。

您可以在MySQL配置文件中设置此参数的值，也可以使用SET GLOBAL语句动态更改它

这需要足够的权限来设置全局系统变量。参见“系统变量权限”。

随机预读是一种技术，它根据缓冲池中已经存在的页面来预测何时可能很快需要其他页面，而不管这些页面的读取顺序如何。

如果在缓冲池中发现来自同一数据块的13个连续页面，InnoDB将异步发出一个请求，以预取数据块的剩余页面。

要启用此功能，请将配置变量innodb_random_read_ahead设置为ON。

SHOW ENGINE INNODB STATUS命令显示统计信息，以帮助您评估预读算法的有效性。

统计信息包括以下全局状态变量的计数器信息：

Innodb_buffer_pool_read_ahead

Innodb_buffer_pool_read_ahead_evicted

Innodb_buffer_pool_read_ahead_rnd

当微调innodb_random_read_ahead设置时，此信息非常有用。

有关I/O性能的更多信息，请参阅“优化InnoDB磁盘I/O”和“优化磁盘I/O”。

5 配置缓冲池刷新

InnoDB在后台执行某些任务，包括从缓冲池中清除脏页。

脏页是指那些已被修改但尚未写入磁盘上的数据文件的页。

在MySQL 8.0中，缓冲池刷新是由页清理线程执行的。

5.1 页清理线程

页清理线程的数量由innodb_page_cleaners变量控制，默认值为4。

但是，如果页面清理器线程的数量超过缓冲池实例的数量，innodb_page_cleaners会自动设置为与innodb_buffer_pool_instances相同的值。

当脏页的百分比达到innodb_max_dirty_pages_pct_lwm变量定义的低水位标记值时，启动缓冲池刷新。默认的低水位标记是缓冲池页面的10%。

innodb_max_dirty_pages_pct_lwm值为0将禁用这种过早的刷新行为。

innodb_max_dirty_pages_pct_lwm值的目的是控制缓冲池中脏页的百分比，并防止脏页的数量达到innodb_max_dirty_pages_pct变量（默认值为90）定义的阈值。

如果缓冲池中脏页面的百分比达到InnoDB_max_dirty_pages_pct阈值，InnoDB会主动刷新缓冲池页面。

配置innodb_max_dirty_pages_pct_lwm时，该值应始终低于innodb_ax_dirty_pages_pct值。

其他变量允许对缓冲池刷新行为进行微调：

innodb_flush_neighbors变量定义从缓冲池中刷新页面是否也会刷新相同的区（EXTENT）的其他脏页面。

博主PS：这里的EXTENT就是下面博客中的MySQL的文件空间管理，页，区，段，表空间中的区EXTENT

【MySQL精通之路】InnoDB磁盘I/O和文件空间管理(11)-CSDN博客

默认设置0将禁用innodb_flush_neighbors。相同范围内的脏页不会被刷新。

对于寻道时间不是重要因素的非旋转存储（SSD）设备，建议使用此设置。

“博主PS：这里的非旋转存储指的是需要寻道时间的机械硬盘~”

设置为1会在相同的区（EXTENT）内刷新连续的脏页。

设置为2会在相同的区（EXTENT）内刷新脏页。

当表数据存储在传统的HDD存储设备上时，与在不同时间刷新单个页面相比，在一次操作中刷新相邻页面减少了I/O开销（主要用于磁盘寻道操作）。

对于存储在SSD上的表数据，寻道时间不是一个重要因素，您可以禁用此设置来分散写入操作。

innodb_lru_scan_depth变量为每个缓冲池实例指定页清理器线程在缓冲池LRU列表搜索脏页的深度。这是页清理线程每秒执行一次的后台操作。

小于默认值的设置通常适用于大多数工作负载。

明显高于必要值的值可能会影响性能。

只有在典型工作负载下有空闲I/O容量时，才考虑增加值。

相反，如果写密集型工作负载使I/O容量饱和，请降低该值，尤其是在缓冲池很大的情况下。

当调优innodb_lru_scan_depth时，从一个低值开始，并向上配置设置，最优配置是很少看到零个可用页面。

此外，在更改缓冲池实例的数量时

请考虑调整innodb_lru_scan_depth * innodb_buffer_pool_instances定义了页面清理线程每秒执行的工作量。

innodb_flush_neighbors和innodb_lru_scan_depth变量主要用于写密集型工作负载。

在DML活动频繁的情况下

如果刷新不够积极，则可能会落后；

如果刷新过于频繁，则磁盘写入可能会使I/O容量饱和。

理想的设置取决于您的工作负载、数据访问模式和存储配置（例如，数据是存储在HDD还是SSD设备上）。

5.2 自适应刷新

InnoDB使用自适应刷新算法，根据redolog日志生成的速度和当前刷新率动态调整刷新率。

其目的是通过确保刷新活动与当前工作负载保持同步来平滑总体性能。

自动调整刷新率有助于避免吞吐量突然下降，当缓冲池刷新导致的I/O活动突发影响到可用于普通读写活动的I/O容量时，可能会发生这种情况。

例如，Sharp checkpoints通常与生成大量redolog日志记录的写密集型工作负载相关，可能会导致吞吐量的突然变化。当InnoDB想要redolog日志文件的一部分时，就会出现一个突发的检查点。

博主PS：打过单机游戏的都知道检查点吧，或者对应游戏来说就是一个存档位置。你死了可以从检查点复活，那么这里也是一样，如果产生检查点的时候，MySQL会将所有脏页从内存刷到磁盘中。你可以理解这就是Sharp checkpoints。这个操作必然导致数据库吞吐量降低。

在执行此操作之前，必须刷新日志文件的该部分中具有redolog日志的所有脏页。

如果日志文件已满，则会出现突发的检查点，从而导致吞吐量暂时降低。即使未达到innodb_max_dirty_pages_pct阈值，也可能出现这种情况。

自适应刷新算法通过跟踪缓冲池中脏页的数量和生成redolog日志记录的速率来帮助避免这种情况。

根据这些信息，它决定每秒从缓冲池中清除多少脏页，这使它能够管理工作负载的突然变化。

innodb_adaptive_flushing_lwm变量定义了重做日志容量的低水位标记。

当超过该阈值时，即使innodb_adaptive_flushing变量被禁用，也会启用自适应刷新。

内部基准测试表明，该算法不仅可以随着时间的推移保持吞吐量，而且可以显著提高整体吞吐量。但是，自适应刷新可能会显著影响工作负载的I/O模式，并且可能不适用于所有情况。当redolog日志有被填满的危险时，它提供了最大的好处。

如果自适应刷新不适合您的工作负载特征，您可以禁用它。

自适应刷新由innodb_adaptive_flushing变量控制，默认情况下启用该变量。

innodb_flushing_avg_lops定义了innodb保留先前计算的刷新状态快照的迭代次数，控制自适应刷新对前台工作负载变化的响应速度。

innodb_flushing_avg_lops值越高，意味着innodb会使之前计算的快照保持更长的时间，因此自适应刷新的响应越慢。

当设置一个高值时，重要的是要确保redolog日志利用率不会达到75%（异步刷新开始时的硬编码限制），并且innodb_max_dirty_pages_pct阈值将脏页的数量保持在适合工作负载的水平。

具有一致工作负载、大日志文件大小（innodb_log_file_size）和日志空间利用率未达到75%的小峰值的系统应使用高innodb_flushig_avg_lops值以保持刷新尽可能顺利。

对于具有极端负载峰值或日志文件不提供大量空间的系统，较小的值允许刷新以密切跟踪工作负载变化，并有助于避免达到75%的日志空间利用率。

请注意，如果刷新滞后，缓冲池刷新的速率可能会超过InnoDB可用的I/O容量，如innodb_io_capacity设置所定义。

innodb_io_capacity_max值定义了在这种情况下I/O容量的上限，因此I/O活动的峰值不会消耗服务器的全部I/O容量。

innodb_io_capacity设置适用于所有缓冲池实例。

刷新脏页时，I/O容量在缓冲池实例之间平均分配。

5.3 限制空闲期间的缓冲区刷新

从MySQL 8.0.18开始，您可以使用innodb_idle_flush_pct变量来限制空闲期间缓冲池刷新的速率，空闲期间是不修改数据库页的时间段。

innodb_idle_flush_pct值是innodb_io_ccapacity设置的百分比，该设置定义了innodb每秒可用的I/O操作数。

默认的innodb_idle_flush_pct值为100，是innodb_io_caption设置的100%。

要限制空闲期间的刷新，请定义一个小于100的innodb_idle_flush_pct值。

在空闲期间限制页面刷新有助于延长固态存储设备的寿命。

在空闲期间限制页面刷新的副作用可能包括长时间空闲后的关闭时间更长，以及服务器出现故障时的恢复时间更长。

6 保存和恢复缓冲池状态

为了缩短重新启动服务器后的预热时间，InnoDB在服务器关闭时为每个缓冲池保存一定比例的最近使用的页面，并在服务器启动时恢复这些页面。

存储的最近使用的页面的百分比由innodb_buffer_pool_dump_pct配置选项定义。

重新启动的服务器后，通常会有一段吞吐量稳步增加的预热期，因为缓冲池中的磁盘页会被带回内存（查询、更新相同的数据等）。

在启动时恢复缓冲池的能力缩短了预热期，因为重新加载了重新启动前缓冲池中的磁盘页，而不是等待DML操作访问相应的行。

此外，I/O请求可以大批量执行，使整体I/O速度更快。页面加载发生在后台，不会延迟数据库启动。

除了在关闭时保存缓冲池状态并在启动时恢复外，您还可以在服务器运行时随时保存和恢复缓冲池状态。

例如，可以在稳定的工作负载下达到稳定的吞吐量后保存缓冲池的状态。

尽管缓冲池的大小可能有很多GB，但相比之下，InnoDB保存到磁盘的缓冲池数据非常小。只有表空间ID和查找相应页面所需的页面ID才会保存到磁盘。

此信息源自INFORMATION_SCHEMA库INNODB_BUFFER_PAGE_LRU表。默认情况下，表空间ID和页面ID数据保存在名为ib_buffer_pool的文件中，该文件保存到InnoDB数据目录中。可以使用innodb_buffer_pool_filename配置参数修改文件名和位置。

因为数据是像常规数据库操作一样缓存在缓冲池中和从缓冲池中移出的，所以如果磁盘页是最近更新的，或者DML操作涉及尚未加载的数据，则没有问题。加载机制会跳过已不存在的请求页面。

底层机制涉及一个后台线程，该线程被分派来执行转储和加载操作。

压缩表中的磁盘页以压缩的形式加载到缓冲池中。在DML操作期间访问页面内容时，页面会像往常一样被解压缩。因为解压缩页面是一个CPU密集型过程，所以并发在连接线程中执行操作比在执行缓冲池恢复操作的单个线程中执行更高效。

6.1 配置缓冲池页面的转储百分比

在从缓冲池转储页面之前，可以通过设置innodb_buffer_pool_dump_pct选项来配置要转储的最近使用的缓冲池页面的百分比。如果计划在服务器运行时转储缓冲池页面，则可以动态配置该选项：

SET GLOBAL innodb_buffer_pool_dump_pct=40;

如果计划在服务器关闭时转储缓冲池页面，请在配置文件中设置innodb_buffer_pool_dump_pct。

[mysqld]
innodb_buffer_pool_dump_pct=40

6.2 关闭时保存缓冲池状态，启动时恢复

要在服务器关闭时保存缓冲池的状态，请在关闭服务器之前发出以下语句：

SET GLOBAL innodb_buffer_pool_dump_at_shutdown=ON;

innodb_buffer_pool_dump_at_shutdown在默认情况下处于启用状态。

要在服务器启动时恢复缓冲池状态，请在启动服务器时指定--innodb缓冲池启动时加载选项：

mysqld --innodb-buffer-pool-load-at-startup=ON;

innodb_buffer_pool_load_at_startup在默认情况下处于启用状态。

6.3 联机保存和恢复缓冲池状态

要在MySQL服务器运行时保存缓冲池的状态，请发出以下语句：

SET GLOBAL innodb_buffer_pool_dump_now=ON;

要在MySQL运行时恢复缓冲池状态，请发出以下语句：

SET GLOBAL innodb_buffer_pool_load_now=ON;

6.4 显示缓冲池转储进度

要在将缓冲池状态保存到磁盘时显示进度，请发出以下语句：

SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status';

如果操作尚未启动，则返回“未启动”。如果操作完成，则打印完成时间（例如在110505 12:18:02完成）。如果操作正在进行，则提供状态信息（例如，转储缓冲池5/7，第237/2873页）。

6.5 中止缓冲池加载操作

要中止缓冲池加载操作，请发出以下语句：

SET GLOBAL innodb_buffer_pool_load_abort=ON;

6.6 使用Performance Schema监视缓冲池加载进度

您可以使用Performance Schema库监视缓冲池加载进度。

以下示例演示了如何启用stage/innodb/buffer-pool-load stage事件工具和相关的consumer表来监控缓冲池加载进度。

有关本例中使用的缓冲池转储和加载过程的信息，请参阅“保存和恢复缓冲池状态”。

有关性能模式阶段事件仪器和相关消费者的信息，请参阅“Performance Schema Stage Event Tables”。

1. 启用stage/innodb/pool load组件：

   mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' 
          WHERE NAME LIKE 'stage/innodb/buffer%';

2. 启用 stage event consumer 表， 包含 events_stages_current, events_stages_history, and events_stages_history_long.

   mysql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' 
          WHERE NAME LIKE '%stages%';

3. 通过启用innodb_buffer_pool_Dump_now来转储当前缓冲池状态。

   mysql> SET GLOBAL innodb_buffer_pool_dump_now=ON;

4. 检查缓冲池转储状态以确保操作已完成。

   mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status'\G
   *************************** 1. row ***************************
   Variable_name: Innodb_buffer_pool_dump_status
           Value: Buffer pool(s) dump completed at 150202 16:38:58

5. ​通过启用innodb_buffer_pool_Load_now加载缓冲池：

   mysql> SET GLOBAL innodb_buffer_pool_load_now=ON;

6. ​通过查询Performance Schema库events_stages_current表，检查缓冲池加载操作的当前状态。WORK_COMPLETED列显示加载的缓冲池页面数。WORK_ESTMATED列以页为单位提供剩余工作量的估计值。
   ​

   mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED
          FROM performance_schema.events_stages_current;
   +-------------------------------+----------------+----------------+
   | EVENT_NAME                    | WORK_COMPLETED | WORK_ESTIMATED |
   +-------------------------------+----------------+----------------+
   | stage/innodb/buffer pool load |           5353 |           7167 |
   +-------------------------------+----------------+----------------+

   ​

   如果缓冲池加载操作已完成，events_stages_current表将返回一个空集。在这种情况下，您可以检查events_stages_history表来查看已完成事件的数据。例如
   ​

   mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED 
          FROM performance_schema.events_stages_history;
   +-------------------------------+----------------+----------------+
   | EVENT_NAME                    | WORK_COMPLETED | WORK_ESTIMATED |
   +-------------------------------+----------------+----------------+
   | stage/innodb/buffer pool load |           7167 |           7167 |
   +-------------------------------+----------------+----------------+

7 从核心文件中排除缓冲池页面