MySQL :: MySQL 8.4 Reference Manual :: 17.8.3.1 Configuring InnoDB Buffer Pool Size

version 8.4

8.0 current
5.7

8.0 Japanese

MySQL 8.4 Reference Manual / ... / Configuring InnoDB Buffer Pool Size

17.8.3.1 InnoDB 缓冲池大小配置

您可以在离线或服务器运行时配置InnoDB缓冲池大小。以下行为适用于两种方法。关于在线配置缓冲池大小的更多信息，请见在线配置 InnoDB 缓冲池大小。

当增加或减少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默认值为128M。

8G 是一个有效的innodb_buffer_pool_size值，因为8G是innodb_ buffer_pool_instances=16和innodb_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，innodb_buffer_pool_instances 设置为 16。innodb_buffer_pool_chunk_size 是 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 缓冲池 Chunk 大小

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

命令行：

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

配置文件：

[mysqld]
innodb_buffer_pool_chunk_size=134217728

以下条件适用于更改innodb_缓存池块大小：

如果新的innodb_缓存池块大小值乘以innodb_缓存池实例大于初始缓存池大小时，innodb_缓存池块大小将被截断到innodb_缓存池大小 / innodb_缓存池实例。

例如，如果缓存池初始化大小为2GB(2147483648字节)、缓存池实例数为4，块大小为1GB(1073741824字节），则块大小将被截断到innodb_缓存池大小 / innodb_缓存池实例，如下所示：

$> 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的影响，以确保最终的缓冲池大小是可接受的。

Note

为了避免潜在的性能问题，chunk 数量 (innodb_buffer_池大小 / innodb_缓冲池chunk_大小) 不应该超过 1000。

在线配置 InnoDB 缓冲池大小

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

mysql> SET GLOBAL innodb_buffer_pool_size=402653184;

Note

缓冲池大小必须等于或是innodb_缓冲池chunk_大小 * innodb_缓冲池实例的倍数。修改这些变量设置需要重新启动服务器。

活动事务和通过InnoDB API执行的操作应在调整缓冲池大小前完成。当启动调整操作时，操作不会立即开始，而是等待所有活动事务完成。一旦调整操作正在进行中，新的事务和需要访问缓冲池的操作必须等待调整操作完成。唯一的例外是，当缓冲池被 defragmented 和页面被撤回时，可以允许并发访问缓冲池。在允许并发访问时可能会出现暂时性的可用页面短缺情况。

Note

嵌套事务可能在调整操作开始后失败。

在线缓冲池调整进度监控

变量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和Innodb_buffer_pool_resize_status_progress来监控在线缓冲池调整操作，这两个变量报告数字值，适合程序化监控。

Innodb 缓冲池 resize 状态变量Innodb_buffer_pool_resize_status_code报告了在线缓冲池 resize 操作的状态代码。状态代码包括：

0：没有正在进行的 Resize 操作
1：Resize 操作开始
2：禁用 AHI（Adaptive Hash Index）
3：撤回块
4：获取全局锁
5：resize 缓冲池
6：resize 哈希表
7：Resize 失败

Innodb 缓冲池 resize 状态进度变量Innodb_buffer_pool_resize_status_progress报告了每个阶段的百分比值。百分比值在处理每个缓冲池实例后更新。在状态（由Innodb_buffer_pool_resize_status_code报告）从一个状态到另一个状态时，百分比值将被重置为 0。

以下查询返回字符串值，表示缓冲池 resize 进度、当前操作的代码和该阶段的当前进度， expressed as a percentage value：

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

缓冲池 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.

使用--log-error-verbosity=3启动服务器时，会在在线缓冲池重-sizing操作期间将额外信息记录到错误日志中。额外信息包括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

在线缓冲池重-sizing内部实现

重-sizing操作由背景线程执行。当增加缓冲池的大小时，重-sizing操作：

以chunks（chunk size由innodb_buffer_pool_chunk_size定义）添加页面
将哈希表、列表和指针转换为使用新地址的内存
将新的页面添加到自由列表中

在这些操作进行时，其他线程无法访问缓冲池。

当减少缓冲池的大小时，重-sizing操作：

碎片化缓冲池并释放页面
以chunks（chunk size由innodb_buffer_pool_chunk_size定义）删除页面
将哈希表、列表和指针转换为使用新地址的内存

这些操作中，只有缓冲池碎片化和撤回页面允许其他线程同时访问缓冲池。

PREV HOME UP NEXT