在之前的版本，调整Innodb_Buffer_Pool_size大小必须重启mysql进程才可以生效，如今在MySQL5.7里，可以直接动态设置，方便了很多。这个功能应用的场景：一、机器
在之前的版本，调整innodb_buffer_pool_size大小必须重启mysql进程才可以生效，如今在mysql5.7里，可以直接动态设置，方便了很多。
这个功能应用的场景：
一、机器增加内存，dba粗心大意忘记调大innodb_buffer_pool_size了
二、工作交接，新来的dba发现前任dba设置的innodb_buffer_pool_size不合理
需要注意的地方，在调整buffer_pool期间，用户的请求将会阻塞，直到调整完毕，所以请勿在白天调整，在凌晨3-4点低峰期调整。
调整时，内部把数据页移动到一个新的位置，单位是块。如果想增加移动的速度，需要调整innodb_buffer_pool_chunk_size参数的大小，默认是128m。
例（把bp 128m增大为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)
innodb_buffer_pool_chunk_size的大小，，计算公式是innodb_buffer_pool_size / innodb_buffer_pool_instances
比如现在初始化innodb_buffer_pool_size为2G，innodb_buffer_pool_instances实例为4，innodb_buffer_pool_chunk_size设置为1G，那么会自动把innodb_buffer_pool_chunk_size 1G调整为512M，例：
./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_instances
mysql> SELECT @@innodb_buffer_pool_chunk_size;
+---------------------------------+
| @@innodb_buffer_pool_chunk_size |
+---------------------------------+
| 536870912 |
+---------------------------------+
1 row in set (0.00 sec)
监控Buffer Pool调整进程
乐活途购物分享社区
beta v1.1版本为第一个版本，简单的整合了基础功能，各位站长拿到程序后，不要纠结后台的功能简单，后续将不断更新扩展。在beta v1.1版本使用过程中遇到什么问题，请登录 www.loftto.com 进行反馈！ 安装说明######重要提醒：程序不支持二级目录安装，请使用一级目录或二级目录绑定！#第一步，确定你的服务器支持PHP+mysql。#第二步，确定你的服务器开启了gd库。#第三步，
下载
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)
查看错误日志：
（增大）
[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 buffer pool from 134217728 to 4294967296.
[Note] InnoDB: re-enabled adaptive hash index.
