在之前的版本，調整Innodb_Buffer_Pool_size大小必須重啟mysql程式才可以生效，如今在MySQL5.7裡，可以直接動態設定，方便了很多。
這個功能應用的場景：
一、機器增加記憶體，DBA粗心大意忘記調大Innodb_Buffer_Pool_size了
二、工作交接，新來的DBA發現前任DBA設定的Innodb_Buffer_Pool_size不合理
需要注意的地方，在調整Buffer_Pool期間，使用者的請求將會阻塞，直到調整完畢，所以請勿在白天調整，在凌晨3-4點低峰期調整。
調整時，內部把資料頁移動到一個新的位置，單位是塊。如果想增加移動的速度，需要調整innodb_buffer_pool_chunk_size引數的大小，預設是128M。
例（把BP 128M增大為384M）：
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的大小，計算公式是innodb_buffer_pool_size / innodb_buffer_pool_instances
比如現在初始化innodb_buffer_pool_size為2G，innodb_buffer_pool_instances例項為4，innodb_buffer_pool_chunk_size設定為1G，那麼會自動把innodb_buffer_pool_chunk_size 1G調整為512M，例：
./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_instances mysql> SELECT @@innodb_buffer_pool_chunk_size; +---------------------------------+ | @@innodb_buffer_pool_chunk_size | +---------------------------------+ | 536870912 | +---------------------------------+ 1 row in set (0.00 sec)
監控Buffer Pool調整程式
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)
檢視錯誤日誌：
（增大）
[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.