MySQL5.7新特性之在线调整innodb_buffer_pool_size
所有文档

          云数据库 RDS

          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。展示如下图:

            image.png

          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

              举例,如下图为初始状态:

              image.png

              在my.cnf设置innodb_buffer_pool_chunk_size=1073741824,重启实例:

              image.png

              以上,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中;

            测试结果:

            image.png

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

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

            测试结果如下:

            image.png

            可以看到,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:

          image.png

          可以看到,在线修改时只是将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和insatnce影响,所以,一定要提前计算好设置的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为整数)啦。
          上一篇
          REFERENCES command denied to user错误详解
          下一篇
          MySQL5.7新特性之GeneratedColumn