MySQL 8.0 Reference Manual（读书笔记70节–InnoDB Buffer Pool Configuration (1)）

1.InnoDB Buffer Pool Size 配置

When increasing or decreasing innodb_buffer_pool_size, the operation is performed in chunks. Chunk size is defined by the innodb_buffer_pool_chunk_size configuration option, which has a default of 128M.—–innodb_buffer_pool_size的扩容和缩容，都是以innodb_buffer_pool_chunk_size为单位进行的，其默认为128M 。

Buffer pool size must always be equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances. If you configure innodb_buffer_pool_size to a value that is not equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances, 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.—– innodb_buffer_pool_size必须是 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances乘积的整数倍

举个例子：

假如 innodb_buffer_pool_instances 为16；innodb_buffer_pool_chunk_size默认的为128M。


innodb_buffer_pool_instances * innodb_buffer_pool_chunk_size =2G；

则

 innodb_buffer_pool_size 应该设置为2G的倍数

如果你设置成了8G，通过 SELECT @@innodb_buffer_pool_size/1024/1024/1024; 命令查看，确实为8G；

如果你设置成了9G，通过SELECT @@innodb_buffer_pool_size/1024/1024/1024;命令查看，实际上变成了10G。【向上扩展】

2.InnoDB Buffer Pool Chunk Size 配置

innodb_buffer_pool_chunk_size can be increased or decreased in 1MB (1048576 byte) units but can only be modified at startup, in a command line string or in a MySQL configuration file.

—调整的最小单位为1M，并且需重启。

The following conditions apply when altering innodb_buffer_pool_chunk_size:

• If the new innodb_buffer_pool_chunk_size value * innodb_buffer_pool_instances is larger than the current buffer pool size when the buffer pool is initialized,

innodb_buffer_pool_chunk_size is truncated to innodb_buffer_pool_size / innodb_buffer_pool_instances.

• Buffer pool size must always be equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances.

If you alter innodb_buffer_pool_chunk_size, innodb_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.

The adjustment occurs when the buffer pool is initialized.

注意事项

（1）Care should be taken when changing innodb_buffer_pool_chunk_size, as changing this value can increase the size of the buffer pool, as shown in the examples above.

Before you change innodb_buffer_pool_chunk_size, calculate the effect on innodb_buffer_pool_size to ensure that the resulting buffer pool size is acceptable. –谨慎调整

（2）To avoid potential performance issues, the number of chunks (innodb_buffer_pool_size / innodb_buffer_pool_chunk_size) should not exceed 1000. —换句话说，当innodb_buffer_pool_size>128G时，

可以考虑修改innodb_buffer_pool_chunk_size的默认值了。

3. InnoDB Buffer Pool Size 在线调整

是支持在线调整的。

Active transactions and operations performed through InnoDB APIs should be completed before resizing the buffer pool【当前的事务需要执行完毕】. When initiating a resizing operation, the operation does not start until all active transactions are completed.【新的事务请求，会被阻塞住，等待调整的命令执行完】 Once the resizing operation is in progress, new transactions and operations that require access to the buffer pool must wait until the resizing operation finishes.【调整时，buffer 也不允许新的事务或请求，去访问】 The exception to the rule is that concurrent【同时发生的】 access to the buffer pool is permitted while the buffer pool is defragmented 【去碎片化】and pages are withdrawn when buffer pool size is decreased. A drawback【ˈdrɔːbæk  缺点】 of allowing concurrent access is that it could result in a temporary shortage of available pages while pages are being withdrawn【撤回、撤离、不再提供】.

—–三种事务： 发出调整命令前的事务；发出命令后事务；同时发出的事务。

需要注意的是：Nested transactions could fail if initiated after the buffer pool resizing operation begins.

 4.可视化Online Buffer Pool Resizing Progress

The Innodb_buffer_pool_resize_status variable reports a string value indicating buffer pool resizing progress;执行的命令为：

SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';

 From MyQL 8.0.31, you can also monitor an online buffer pool resizing operation using the Innodb_buffer_pool_resize_status_code and Innodb_buffer_pool_resize_status_progress status variables, which report numeric values, preferable for programmatic monitoring.–新版本的MySQL，有更多的监控指标

The Innodb_buffer_pool_resize_status_code status variable reports a status code indicating the stage of an online buffer pool resizing operation. Status codes include:

• 0: No Resize operation in progress

• 1: Starting Resize

• 2: Disabling AHI (Adaptive Hash Index)

• 3: Withdrawing Blocks

• 4: Acquiring Global Lock

• 5: Resizing Pool

• 6: Resizing Hash

• 7: Resizing Failed

The Innodb_buffer_pool_resize_status_progress status variable reports a percentage value indicating the progress of each stage. The percentage value is updated after each buffer pool instance is processed. As the status (reported by Innodb_buffer_pool_resize_status_code) changes from one status to another, the percentage value is reset to 0.

The following query returns a string value indicating the buffer pool resizing progress, a code indicating the current stage of the operation, and the current progress of that stage, expressed as a percentage value:

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

Buffer pool resizing progress is also visible in the server error log. —调整过程也可以在error log 查看。

From MySQL 8.0.31, starting the server with –log-error-verbosity=3 logs additional information to the error log during an online buffer pool resizing operation.Additional information includes the status codes reported by Innodb_buffer_pool_resize_status_code and the percentage progress value reported by Innodb_buffer_pool_resize_status_progress.

5.Online Buffer Pool Resizing Internals

The resizing operation is performed by a background thread.

增加内存分配

When increasing the size of the buffer pool, the resizing operation:

• Adds pages in chunks (chunk size is defined by innodb_buffer_pool_chunk_size)

• Converts hash tables, lists, and pointers to use new addresses in memory

• Adds new pages to the free list

While these operations are in progress, other threads are blocked from accessing the buffer pool.

减少内存分配

When decreasing the size of the buffer pool, the resizing operation:

• Defragments the buffer pool and withdraws (frees) pages

• Removes pages in chunks (chunk size is defined by innodb_buffer_pool_chunk_size)

• Converts hash tables, lists, and pointers to use new addresses in memory

Of these operations, only defragmenting the buffer pool and withdrawing pages allow other threads to access to the buffer pool concurrently.

—《17.8.3.1 Configuring InnoDB Buffer Pool Size》

https://dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool-resize.html