MySQL5.7新特性之在线调整innodb_buffer_pool_size

背景

从MySQL 5.7.5版本开始，可以在线动态调整innodb_buffer_pool_size的大小，这个新特性同时也引入了参数innodb_buffer_pool_chunk_size。因为buffer pool的大小受innodb_buffer_pool_chunk_size和innodb_buffer_pool_instances两个参数影响，所以，实际innodb_buffer_pool_size的大小可能与DBA设置的并不一样，有时区别甚至还挺大。

本篇文章，主要从两个方面来解释这一新特性：

1. 怎么在线动态调整，在线调整对服务会有什么影响，适用场景有哪些。
2. innodb_buffer_pool_chunk_size和innodb_buffer_pool_instances是怎么影响buffer pool的。

问题现象

在对MySQL 5.7.21版本的数据库做性能压测时，选择的是2G内存的虚机，并按内存的60%（2G* 60%=1228MB）设置innodb_buffer_pool_size。压测开始没多久，数据库就OOM了。

排查发现，my.cnf设置的buffer pool大小和从内存查出的完全不同：

• my.cnf的值：innodb_buffer_pool_size = 1228MB。
• select @@ innodb_buffer_pool_size;的值：2147483648 (2048MB)。

我只是按照内存的60%（1228MB）设置，而内存中实际的buffer pool竟然占用了整个虚拟所有的内存。my.cnf静态文件竟然不能控制buffer pool大小了？这种行为足以让DBA感到怀疑人生。

原因分析

名词解释

• innodb_buffer_pool_size：该参数控制innodb缓冲池大小，用来存储innodb表和索引的数据。以下简称buffer pool。
• innodb_buffer_pool_instances：该参数控制innodb缓冲池被划分的区域数。如果innodb_buffer_pool_size<1G，则instance为1，否则默认为8。该参数最小值为1，最大值为64。以下简称instance。
• innodb_buffer_pool_chunk_size：该参数控制innodb缓冲池调整大小调整操作的块大小。该参数默认是128MB，最小值为1MB（可按1MB调整其大小），最大值为innodb_buffer_pool_size / innodb_buffer_pool_instances。以下简称chunk。

详细分析

1. 先来看看innodb_buffer_pool_size、innodb_buffer_pool_instances、innodb_buffer_pool_chunk_size这三个参数的关系 buffer pool可以存放多个instance，每个instance由多个chunk组成。instance的数量范围和chunk的总数量范围分别为1-64、1-1000。 比如，一个内存为4G的服务器，chunk是128MB。设置buffer pool为2G，instance设置为4个，那么每个instance为512MB即4个chunk。展示如下图：

2. 再来看看innodb_buffer_pool_instances和innodb_buffer_pool_chunk_size是怎么影响innodb_buffer_pool_size的：

   • 在初始化缓冲池时，如果innodb_buffer_pool_size小于innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的大小，那么innodb_buffer_pool_chunk_size将会被截断为innodb_buffer_pool_size / innodb_buffer_pool_instances。

     举例，如下图为初始状态：

     

     在my.cnf设置innodb_buffer_pool_chunk_size=1073741824，重启实例：

     

     以上，innodb_buffer_pool_chunk_size由默认的134217728调整为了 innodb_buffer_pool_size/innodb_buffer_pool_instances= 2147483648/8=268435456。

   • 缓冲池大小必须始终等于innodb_buffer_pool_chunk_size innodb_buffer_pool_instances的整数倍。修改任何一个参数， MySQL都自动将innodb_buffer_pool_size调整为innodb_buffer_pool_chunk_size innodb_buffer_pool_instances的整数倍。

     因为innodb_buffer_pool_chunk_size 或 innodb_buffer_pool_instances会影响innodb_buffer_pool_size的大小，所以修改时一定要特别小心。

解决方案

设置buffer pool时，参考计算公式：ceil(设置的buffer pool大小/chunk大小/instance个数)chunk大小instance个数，这个值计算出的结果要符合你想设置的预期。

案例复现

在线动态修改buffer pool

1. 在线调大buffer pool。加大buffer pool的过程大致如下：

   （1）以innodb_buffer_pool_chunk_size为单位，分配新的内存pages。
   （2） 扩展buffer pool的AHI(adaptive hash index)链表，将新分配的pages包含进来。 （3）将新分配的pages添加到free list中。

   测试结果：

   

2. 在线调小buffer pool。缩小buffer pool的过程大致如下：

   （1）重整buffer pool，准备回收pages。
   （2）以innodb_buffer_pool_chunk_size为单位，释放删除这些pages（可能会有一些耗时）。
   （3）调整AHI链表，使用新的内存地址。

   测试结果如下：

   

   可以看到，buffer pool通过在线修改，已经从480MB调整到了256MB。

MySQL 5.7.5后对buffer pool的影响因素

通过官网介绍，我们已经知道MySQL 5.7.5的buffer pool大小必须是innodb_buffer_pool_chunk_size* innodb_buffer_pool_instance的整数倍。那么这两个参数具体怎么影响buffer pool的设置的呢？

举例：系统内存4G，chunk大小为128MB，instance个数为8。

可以看到，在线修改时只是将buffer pool设置的比原来1073741824（1G）多 1byte， 但innodb_buffer_pool_size却自动向上调整到了2147483648（2G）。为什么会调整到2G？

计算方法：ceil(设置的buffer pool大小/chunk大小/instance个数) ∗ chunk大小 ∗ instance个数= ceil(1073741825/134217728/8) ∗ 134217728 ∗ 8= 2147483648byte=2G。

结论建议

动态调整方便快速，实测影响并不明显

实际测试，增大buffer pool对线上没有影响，缩小对线上影响也并不明显。 缩小buffer pool测试场景一：

（1）session 1：大事务正在运行，预计用内存6G。
（2）session 2：设置buffer pool大小调整到1G。
（3）观察到设置buffer pool大小的SQL瞬间完成，实际会等待session 1事务结束，设置才生效 。
（4）生效过程中，系统并没有任何锁信息，对其他库表的增删改查也没有任何影响。

缩小buffer pool 测试场景二：

（1）系统中有100个并发正在执行增删改查操作。
（2） session 2：设置buffer pool由5G到1G。
（3）观察到设置buffer pool大小的SQL瞬间完成，但是并没有立即生效，大概过了5秒后生效。
（4） buffer pool生效过程中，系统并没有任何锁信息，对其他库表的增删改查也没有任何影响。

5.7.5后设置buffer pool一定要小心

从MySQL 5.7.5开始，innodb buffer pool的大小受chunk和instance影响，所以，一定要提前计算好设置的buffer pool，否则可能会因为MySQL自动调整的buffer pool设置过大，导致实例很容易OOM。

参考计算公式：ceil(设置的buffer pool大小/chunk大小/instance个数) ∗ chunk大小 ∗ instance个数，这个值计算出的结果要符合你想设置的预期。

例如，MySQL所在虚机内存为8G，chunk为128MB，instance为8个。

• innodb_buffer_pool只能设置为chunk大小 ∗ instance个数=1G的整数倍，也就是只能设置nG（n为整数）。
• 如果就是想设置为n.5G怎么办？可以这样处理：将chunk大小 ∗ instance个数调整为512MB的整数倍即可。如将chunk调整为64MB，instance为8，那么buffer pool你就可以设置n.5G（n为整数）啦。