MySQL管理之道

在线调整innodb_buffer_pool_size不用重启mysql进程
MySQL5.7以前，调整innodb_buffer_pool_size需要重启mysql进程才可以生效。
建议业务低峰时间执行

1 当前大小128M

1root@localhost:mysql3306.sock [(none)]>show variables like '%innodb_buffer_pool_size%';
2+-------------------------+-----------+
3| Variable_name           | Value     |
4+-------------------------+-----------+
5| innodb_buffer_pool_size | 134217728 |
6+-------------------------+-----------+
71 row in set (0.03 sec)
8
9root@localhost:mysql3306.sock [(none)]>select 134217728/1024/1024;  
10+---------------------+
11| 134217728/1024/1024 |
12+---------------------+
13|        128.00000000 |
14+---------------------+
151 row in set (0.00 sec)

2 动态调整为256M

1root@localhost:mysql3306.sock [(none)]>set global innodb_buffer_pool_size = 256*1024*1024;
2Query OK, 0 rows affected (0.18 sec)
3
4root@localhost:mysql3306.sock [(none)]>show variables like '%innodb_buffer_pool_size%';
5+-------------------------+-----------+
6| Variable_name           | Value     |
7+-------------------------+-----------+
8| innodb_buffer_pool_size | 268435456 |
9+-------------------------+-----------+
101 row in set (0.02 sec)
11
12root@localhost:mysql3306.sock [(none)]>select 268435456/1024/1024;
13+---------------------+
14| 268435456/1024/1024 |
15+---------------------+
16|        256.00000000 |
17+---------------------+
181 row in set (0.00 sec)
19
20root@localhost:mysql3306.sock [(none)]>select version();
21+------------+
22| version()  |
23+------------+
24| 5.7.18-log |
25+------------+
261 row in set (0.03 sec)

调整时，内部会把数据页移动到一个新的位置，单位是块。如果想提升移动速度，则需要调整
innodb_buffer_pool_chunk_size的参数大小，默认是128M

innodb_buffer_pool_size/innodb_buffer_pool_instances = innodb_buffer_pool_chunk_size的参数大小，默认是128M