MySQL5.7 Online adjustment innodb_buffer_pool_size No need to restart MySQL process

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