14.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。

shell> 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的倍数。

shell> 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 配置文件中进行修改。

Command line:

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

Configuration file:

[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的值，如图所示下面：

shell> 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):

shell> 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):

shell> 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

为了避免潜在的性能问题，块(innodb_buffer_pool_size/innodb_buffer_pool_chunk_size)的数量不应超过 1000.

在线配置 InnoDB 缓冲池大小

可以使用SET语句动态设置innodb_buffer_pool_size配置选项，使您无需重新启动服务器即可调整缓冲池的大小。例如：

mysql> SET GLOBAL innodb_buffer_pool_size=402653184;

在调整缓冲池大小之前，应完成通过InnoDB API 执行的活动事务和操作。启动调整大小操作时，该操作直到所有活动事务完成后才开始。调整大小操作正在进行后，需要访问缓冲池的新事务和操作必须 await 直到调整大小操作完成。该规则的 exceptions 是，在对缓冲池进行碎片整理时，允许并发访问缓冲池；在减小缓冲池大小时，将撤回页面。允许并发访问的缺点是，在撤回页面时，这可能会导致可用页面的暂时短缺。

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. |
+----------------------------------+----------------------------------+

缓冲池调整大小进度也记录在服务器错误日志中。此示例显示增加缓冲池大小时记录的 Comments：

[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.

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

[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.

在线缓冲池调整内部大小

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

添加chunks的页面(块大小由innodb_buffer_pool_chunk_size定义)
隐藏哈希 table，列 table 和指针以使用内存中的新地址
将新页面添加到空闲列 table

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

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

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

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

Name	MySql 中文文档
Version
Language
Badge
Last Updated	2021-07-03T12:24:35