云数据库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