15.8.3.1 配置 InnoDB 缓冲池大小

MySQL 8.0 参考手册 / 第 15 章 InnoDB 存储引擎 / 15.8 InnoDB配置 / 15.8.3 InnoDB缓冲池配置 / 15.8.3.1 配置 InnoDB 缓冲池大小

15.8.3.1 配置 InnoDB 缓冲池大小

您可以InnoDB离线或在服务器运行时配置缓冲池大小。本节中描述的行为适用于这两种方法。有关在线配置缓冲池大小的其他信息，请参阅在线配置 InnoDB 缓冲池大小。

当 increasing 或 decreasing innodb_buffer_pool_size时，操作以块的形式执行。块大小由 innodb_buffer_pool_chunk_size 配置选项定义，默认值为 128M. 有关详细信息，请参阅配置 InnoDB 缓冲池块大小。

缓冲池大小必须始终等于 innodb_buffer_pool_chunk_size* 或它的倍数innodb_buffer_pool_instances。如果您配置 innodb_buffer_pool_size的值不等于 innodb_buffer_pool_chunk_size* 或倍数innodb_buffer_pool_instances，缓冲池大小会自动调整为等于或倍数的值 innodb_buffer_pool_chunk_size* innodb_buffer_pool_instances。

在以下示例中， innodb_buffer_pool_size设置为8G，并 innodb_buffer_pool_instances设置为16。 innodb_buffer_pool_chunk_size is 128M，这是默认值。

8G是一个有效值，innodb_buffer_pool_size因为它是 * 8G 的倍数，即. innodb_buffer_pool_instances=16innodb_buffer_pool_chunk_size=128M2G

$> 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，并且 innodb_buffer_pool_instances设置为16。 innodb_buffer_pool_chunk_size is 128M，这是默认值。在这种情况下，9G不是 innodb_buffer_pool_instances=16 * 的倍数innodb_buffer_pool_chunk_size=128M，因此innodb_buffer_pool_size调整为10G，它是 innodb_buffer_pool_chunk_size* 的倍数innodb_buffer_pool_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 |
+------------------------------------------+

配置 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_buffer_pool_chunk_size 则被截断为 innodb_buffer_pool_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_buffer_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。

在线配置 InnoDB 缓冲池大小

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

mysql> SET GLOBAL innodb_buffer_pool_size=402653184;

笔记

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

通过以下方式执行的活动交易和操作 InnoDBAPI 应在调整缓冲池大小之前完成。启动调整大小操作时，直到所有活动事务完成后操作才会开始。一旦调整大小操作正在进行中，需要访问缓冲池的新事务和操作必须等到调整大小操作完成。该规则的例外是，在对缓冲池进行碎片整理并在缓冲池大小减小时撤回页面时，允许并发访问缓冲池。允许并发访问的一个缺点是它可能导致在页面被撤回时暂时缺少可用页面。

笔记

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

监控在线缓冲池大小调整进度

该 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_code 从 MyQL 8.0.31 开始，您还可以使用和 status 变量监视在线缓冲池大小调整操作 Innodb_buffer_pool_resize_status_progress ，这些变量报告数值，更适合编程监视。

Innodb_buffer_pool_resize_status_code 状态变量报告指示在线缓冲池大小调整操作阶段的状态代码。状态码包括：

0：没有正在进行的调整大小操作
1：开始调整大小
2：禁用AHI（自适应哈希索引）
3：提取区块
4：获取全局锁
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-verbosity=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

在线缓冲池调整内部结构

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

添加页面chunks（块大小由定义 innodb_buffer_pool_chunk_size）
转换哈希表、列表和指针以使用内存中的新地址
将新页面添加到空闲列表

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

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

对缓冲池进行碎片整理并撤回（释放）页面
删除页面chunks（块大小由定义 innodb_buffer_pool_chunk_size）
转换哈希表、列表和指针以使用内存中的新地址

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