MySQL · 特性分析 · innodb_buffer_pool_size在线修改

InnoDB Buffer Pool缓存了表数据和二级索引在内存中，提高数据库效率，因此设置innodb_buffer_pool_size到合理数值对实例性能影响很大。当size设置偏小，会导致数据库大量直接磁盘的访问，而设置过大会导致实例占用内存太多，容易发生OOM。在MySQL 5.7之前innodb_buffer_pool_size的修改需要重启实例，在5.7后支持了动态修改innodb_buffer_pool_size。本文会根据源码介绍该特性。

innodb_buffer_pool_size默认值是128M，最小5M(当小于该值时会设置成5M)，最大为LLONG_MAX。当innodb_buffer_pool_instances设置大于1的时候，buffer pool size最小为1GB。同时buffer pool size需要是innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances的倍数。innodb_buffer_pool_chunk_size默认为128M，最小为1M，实例启动后为只读参数。

如果开启了AHI（adaptive hash index，自适应哈希索引）就关闭AHI，这里因为AHI是通过buffer pool中的B+树页构造而来。

如果新设定的buffer pool size小于原来的size，就需要计算需要删除的chunk数目withdraw_target。

遍历buffer pool instances，锁住buffer pool，收集free list中的chunk page到withdraw，直到withdraw_target或者遍历完，然后释放buffer pool锁。

停止加载buffer pool。

如果free list中没有收集到足够的chunk，则重复遍历收集，每次重复间隔时间会指数增加1s、2s、4s、8s…，以等待事务释放资源。

锁住buffer pool，开始增减chunk。

如果改变比较大，超过2倍，会重置page hash，改变桶大小。

释放buffer_pool,page_hash锁。

改变比较大时候，重新设置buffer pool大小相关的内存结构。

开启AHI。

在支持动态修改innodb_buffer_pool_size之前，该值的修改需要修改配置项然后重启实例生效。而重启实例会导致用户连接强制断开，导致一段时间的实例不可用，如果有大事务在回滚就需要等待很长时间。

动态修改innodb_buffer_pool_size只有在收集回收块；查找持有block阻止buffer pool收集回收chunk的事务；resizing buffer pool操作时会阻塞用户写入。而这几部分操作都是内存操作，会较快完成。

如果对innodb_buffer_pool_size修改量很大，同时遇到page cleaner工作时间久，就可能导致一段时间的阻塞。例如下面一个较为极端的例子，innodb_buffer_pool_instances为1，innodb_buffer_pool_size由18GB改为5M，innodb_buffer_pool_chunk_size为1M，page cleaner loop花费近48s，导致收集回收块会花费很长时间，可以看到在测试机器上用时近48s。而这期间的写入操作也会被阻塞。

正常不需要等待时的内存操作会很快。

另一个方面，如果当前有事务占用大量buffer pool数据导致无法收集到足够的chunk，resize过程也会变久。下面极端测试中当执行xa rollback回滚大事务的时候，innodb_buffer_pool_chunk_size由16M改为5M，即等待了较久时间才完成回收chunk的收集。不过这段时间并不会完全阻塞用户的操作。

从上面可以看到innodb_buffer_pool_size的online修改相比重启对用户实例的影响降低了很多，但也最好选择业务低峰期和没有大事务操作时候进行，同时要修改MySQL配置文件，防止重启后恢复到原来的值。