In the previous version, adjust the innodb_buffer_pool_size size must restart the MySQL process to take effect, now in the MySQL5.7, can be directly dynamic settings, convenient a lot.
This feature applies to the scenario:
First, the machine increased memory, the DBA careless forgot to adjust the big innodb_buffer_pool_size
Second, the work handover, the new DBA found that the former DBA set innodb_buffer_pool_size unreasonable
Note that during the adjustment of Buffer_pool, the user's request will be blocked until the adjustment is complete, so do not adjust in the daytime, in the early morning 3-4-point low peak period adjustment.
When adjusted, the data page is moved internally to a new location, in blocks. If you want to increase the speed of the move, you need to adjust the size of the Innodb_buffer_pool_chunk_size parameter, the default is 128M.
Example (Increase the BP 128M to 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)
The size of the
Innodb_buffer_pool_chunk_size, calculated as Innodb_buffer_pool_size/innodb_buffer_pool_instances
For example, now initialize innodb_buffer_pool_size to 2g,innodb_buffer_pool_instances instance for 4,innodb_buffer_pool_chunk_size set to 1G, Then the Innodb_buffer_pool_chunk_size 1G is automatically adjusted to 512M, for example:
./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 the 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)
To view the error log:
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 the buffer pool from 134217728 to 4294967296. [Note] innodb:re-enabled Adaptive Hash Index.
Reduce
[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.
This article is from the "Spring Yang Technical column" blog, please be sure to keep this source http://hcymysql.blog.51cto.com/5223301/1566864
MySQL5.7 Online adjustment innodb_buffer_pool_size No need to restart MySQL process