Get the App
SLTechnology News&Howtos  ›  Database  › 

The way of MySQL Management-Notes-MySQL5.7- online adjust innodb_buffer_pool_size

Shulou Source: shulou.com Published: 2022-06-01 18:27:32 11月17日 Update

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

Tags: Adjust size parameter process move online do not use business location dynamics units suggestions data time speed notes management Apple Docker Huawei Linux macOS MariaDB Microsoft MySQL NVidia OPPO Reno Microsoft OPPO Reno macOS Docker MariaDB