MySQL5.7 online Innodb_Buffer_Pool_size adjustment without restarting the mysql process

In earlier versions, it is much easier to adjust the Innodb_Buffer_Pool_size to take effect only after the mysql process is restarted. Application scenarios of this function: 1. Increase the memory on the machine. The DBA is careless and forgets to increase Innodb_Buffer_Pool_size. 2. Work handover. The new DBA finds that the former D

In earlier versions, it is much easier to adjust the Innodb_Buffer_Pool_size to take effect only after the mysql process is restarted. Application scenarios of this function: 1. Increase the memory on the machine. The DBA is careless and forgets to increase Innodb_Buffer_Pool_size. 2. Work handover. The new DBA finds that the former D

In earlier versions, it is much easier to adjust the Innodb_Buffer_Pool_size to take effect only after the mysql process is restarted.

Application scenarios of this function:
1. The machine adds memory. The DBA carelessly forgets to increase Innodb_Buffer_Pool_size.
Ii. Work handover: The New DBA finds that the Innodb_Buffer_Pool_size set by the former DBA is unreasonable.

Note that during the Buffer_Pool adjustment, user requests will be blocked until the adjustment is completed. Therefore, do not adjust them during the day and adjust them at AM.

During adjustment, the data page is moved internally to a new location, in blocks. To increase the moving speed, adjust the innodb_buffer_pool_chunk_size parameter. The default value is 128 MB.

Example (increase BP 384 M to M ):

mysql> SELECT @@innodb_buffer_pool_size;+---------------------------+| @@innodb_buffer_pool_size |+---------------------------+|                 134217728 |+---------------------------+1 row in set (0.00 sec)mysql> SELECT @@innodb_buffer_pool_chunk_size;+---------------------------------+| @@innodb_buffer_pool_chunk_size |+---------------------------------+|                       134217728 |+---------------------------------+1 row in set (0.00 sec)mysql> SET GLOBAL innodb_buffer_pool_size=402653184;Query OK, 0 rows affected (0.01 sec)mysql> SELECT @@innodb_buffer_pool_size;+---------------------------+| @@innodb_buffer_pool_size |+---------------------------+|                 402653184 |+---------------------------+1 row in set (0.00 sec)

Innodb_buffer_pool_chunk_size. The formula is innodb_buffer_pool_size/innodb_buffer_pool_instances.

For example, if innodb_buffer_pool_size is set to 2 GB, innodb_buffer_pool_instances is set to 4, and innodb_buffer_pool_chunk_size is set to 1 GB, then the interval 1 GB is automatically adjusted to 512 MB:
./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 |+---------------------------+1 row in set (0.00 sec)mysql> SELECT @@innodb_buffer_pool_instances;+--------------------------------+| @@innodb_buffer_pool_instances |+--------------------------------+|                              4 |+--------------------------------+1 row in set (0.00 sec)# Chunk size was set to 1GB (1073741824 bytes) on startup but was# truncated to innodb_buffer_pool_size / innodb_buffer_pool_instancesmysql> SELECT @@innodb_buffer_pool_chunk_size;+---------------------------------+| @@innodb_buffer_pool_chunk_size |+---------------------------------+|                       536870912 |+---------------------------------+1 row in set (0.00 sec)

Monitor Buffer Pool adjustment process

mysql> SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';+----------------------------------+----------------------------------+| Variable_name                    | Value                            |+----------------------------------+----------------------------------+| Innodb_buffer_pool_resize_status | Resizing also other hash tables. |+----------------------------------+----------------------------------+1 row in set (0.00 sec)

View error logs:
(Increase)

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

(Decrease)

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