The way of MySQL Management-Notes-MySQL5.7- online adjust innodb_buffer_pool_size
Online adjustment of innodb_buffer_pool_size does not need to restart the mysql process
Before MySQL5.7, adjusting innodb_buffer_pool_size required a restart of the mysql process to take effect.
It is recommended that the business be executed at a low peak time.
1 current size 128m
Root@localhost:mysql3306.sock [(none)] > show variables like'% innodb_buffer_pool_size%' +-+ | Variable_name | Value | +-+-+ | innodb_buffer_pool_size | 134217728 | +- -+-+ row in set (0.03 sec) root@localhost:mysql3306.sock [(none)] > select 134217728Unix 1024 +-+ | 134217728 row in set 1024 1024 | +-+ | 128.00000000 | +-+ row in set (0.00 sec)
2 dynamically adjusted to 256m
Root@localhost:mysql3306.sock [(none)] > set global innodb_buffer_pool_size = 256, 1024, 1024, query OK, 0 rows affected (0.18 sec) root@localhost:mysql3306.sock [(none)] > show variables like'% innodb_buffer_pool_size%' +-+ | Variable_name | Value | +-+-+ | innodb_buffer_pool_size | 268435456 | +- -+-+ row in set (0.02 sec) root@localhost:mysql3306.sock [(none)] > select 268435456 sec 1024 +-+ | 2684354561024 sec 1024 | +-+ | 256.00000000 | +-+ row in set (0.00 sec) root@localhost:mysql3306.sock [(none)] > select version () +-+ | version () | +-+ | 5.7.18-log | +-+ row in set (0.03 sec)
When adjusted, the data page is moved internally to a new location, in blocks. If you want to increase the movement speed, you need to adjust
Parameter size of innodb_buffer_pool_chunk_size. Default is 128m.
Innodb_buffer_pool_size/innodb_buffer_pool_instances = the parameter size of innodb_buffer_pool_chunk_size. Default is 128m