- 特性说明：
- 在MySQL运行的时候，可以在线调整innodb buffer size大小。
- 使用set命令进行设置 mysql> SET GLOBAL innodb_buffer_pool_size=402653184;
- 在buffer size resize之前活动事务和各类操作需要执行完毕。如果事务没有完成，那么resize不会启动直至事务完成。
- 在innodb buffer size resizing操作开始时嵌套事务可能会失败。
- 监控执行进程：
mysql> SHOW STATUS WHERE Variable_name=’InnoDB_buffer_pool_resize_status’;
或者查看error log.
测试和验证：
mysql> set global innodb_buffer_pool_size=12737418240;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+———+——+——————————————————————+
| Level | Code | Message |
+———+——+——————————————————————+
| Warning | 1292 | Truncated incorrect innodb_buffer_pool_size value: ‘12737418240’ |
+———+——+——————————————————————+
1 row in set (0.00 sec)
mysql> SHOW STATUS WHERE Variable_name=’InnoDB_buffer_pool_resize_status’;
+———————————-+—————————————————-+
| Variable_name | Value |
+———————————-+—————————————————-+
| Innodb_buffer_pool_resize_status | Completed resizing buffer pool at 170721 15:29:38. |
+———————————-+—————————————————-+
1 row in set (0.00 sec)
mysql> set global innodb_buffer_pool_size=15G;
ERROR 1232 (42000): Incorrect argument type to variable ‘innodb_buffer_pool_size’
mysql> show variables like ‘innodb_buffer_pool_size’;
+————————-+————-+
| Variable_name | Value |
+————————-+————-+
| innodb_buffer_pool_size | 12884901888 |
+————————-+————-+
1 row in set (0.01 sec)
通过观察日志可以看到resizing过程
#tail -n 100 -f /storage/fioa/mysql3307/data/a2-db01b-prodtest3-80-172.sh.err
2017-07-21T07:29:38.408691Z 12 [Note] InnoDB: Requested to resize buffer pool. (new size: 12884901888 bytes)
2017-07-21T07:29:38.408728Z 0 [Note] InnoDB: Resizing buffer pool from 10737418240 to 12884901888 (unit=134217728).
2017-07-21T07:29:38.408764Z 0 [Note] InnoDB: Disabling adaptive hash index.
2017-07-21T07:29:38.438137Z 0 [Note] InnoDB: disabled adaptive hash index.
2017-07-21T07:29:38.438165Z 0 [Note] InnoDB: Withdrawing blocks to be shrunken.
2017-07-21T07:29:38.438187Z 0 [Note] InnoDB: Latching whole of buffer pool.
2017-07-21T07:29:38.438230Z 0 [Note] InnoDB: buffer pool 0 : resizing with chunks 10 to 12.
2017-07-21T07:29:38.457096Z 0 [Note] InnoDB: buffer pool 0 : 2 chunks (16384 blocks) were added.
2017-07-21T07:29:38.457113Z 0 [Note] InnoDB: buffer pool 1 : resizing with chunks 10 to 12.
2017-07-21T07:29:38.474192Z 0 [Note] InnoDB: buffer pool 1 : 2 chunks (16384 blocks) were added.
2017-07-21T07:29:38.474206Z 0 [Note] InnoDB: buffer pool 2 : resizing with chunks 10 to 12.
2017-07-21T07:29:38.490037Z 0 [Note] InnoDB: buffer pool 2 : 2 chunks (16384 blocks) were added.
2017-07-21T07:29:38.490050Z 0 [Note] InnoDB: buffer pool 3 : resizing with chunks 10 to 12.
2017-07-21T07:29:38.504844Z 0 [Note] InnoDB: buffer pool 3 : 2 chunks (16384 blocks) were added.
2017-07-21T07:29:38.504855Z 0 [Note] InnoDB: buffer pool 4 : resizing with chunks 10 to 12.
2017-07-21T07:29:38.519033Z 0 [Note] InnoDB: buffer pool 4 : 2 chunks (16384 blocks) were added.
2017-07-21T07:29:38.519045Z 0 [Note] InnoDB: buffer pool 5 : resizing with chunks 10 to 12.
2017-07-21T07:29:38.532589Z 0 [Note] InnoDB: buffer pool 5 : 2 chunks (16384 blocks) were added.
2017-07-21T07:29:38.532601Z 0 [Note] InnoDB: buffer pool 6 : resizing with chunks 10 to 12.
2017-07-21T07:29:38.544723Z 0 [Note] InnoDB: buffer pool 6 : 2 chunks (16384 blocks) were added.
2017-07-21T07:29:38.544732Z 0 [Note] InnoDB: buffer pool 7 : resizing with chunks 10 to 12.
2017-07-21T07:29:38.555531Z 0 [Note] InnoDB: buffer pool 7 : 2 chunks (16384 blocks) were added.
2017-07-21T07:29:38.555566Z 0 [Note] InnoDB: Completed to resize buffer pool from 10737418240 to 12884901888.
2017-07-21T07:29:38.555577Z 0 [Note] InnoDB: Re-enabled adaptive hash index.
2017-07-21T07:29:38.555589Z 0 [Note] InnoDB: Completed resizing buffer pool at 170721 15:29:38.
参考资料：
https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html
