MySQL Innodb buffer pool相关参数

客官°小女子只卖身不卖艺 2023-08-17 17:06 112阅读 0赞

**innodb\_buffer\_pool\_chunck\_size**  
buffer pool块大小，当buffer pool扩容或缩容时，以chunck为单位来进行空间resizing。只能在实例重启时修改生效。  
修改chunck大小，增加和减少的空间需要是MB的倍数。  
如果innodb\_buffer\_pool\_chunck\_size\*innodb\_buffer\_pool\_instances不能大于innodb\_buffer\_pool\_size，也就是只能改小。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70]

**innodb\_buffer\_pool\_instances**  
buffer pool可以分为多个子pool，称为实例，每个实例都专属的LRU链和相关的数据结构，已达到避免只有一个pool时的并发冲突。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 1]

**innodb\_buffer\_pool\_size**  
buffer pool总大小，设定最好是innodb\_buffer\_pool\_instances\*innodb\_buffer\_pool\_chunck\_size的倍数，否则MySQL会调整为适当的大小，因为只能按照innodb\_buffer\_pool\_chunck\_size来调节。  
可以在线动态调整，但是不知道会有什么BUG。  
在线调整时，可以通过以下命令查看调整进度：  
`SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';`  
同样也可以查看error log里的调整过程信息，如：  
在线增大buffer pool的error log

[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.

在线减小buffer pool的error log

[Note] InnoDB: Resizing buffer pool from 4294967296 to 134217728. (unit=134217728)
    [Note] InnoDB: disabled adaptive hash index.
    [Note] InnoDB: buffer pool 0 : start to withdraw the last 253952 blocks.
    [Note] InnoDB: buffer pool 0 : withdrew 253952 blocks from free list. tried to relocate 0 pages.
    (253952/253952)
    [Note] InnoDB: buffer pool 0 : withdrawn target 253952 blocks.
    [Note] InnoDB: buffer pool 0 : 31 chunks (253952 blocks) was freed.
    [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 4294967296 to 134217728.
    [Note] InnoDB: re-enabled adaptive hash index.

可以看到resizing过程需要禁用自适应哈希索引以及resize hash table，那么在线调整的内核原理如下：  
1.increase  
以chunck为单位增加buffer容量；  
将哈希表，链表结构和指针等重定向到新的内存地址；  
将新页加到free list。  
这些操作由后台线程进行，操作过程中其他线程无法访问buffer pool。  
2.decrease  
抽取buffer pool的碎片并回收；  
以chunck为单位移除页；  
将哈希表，链表结构和指针等重定向到新的内存地址。  
这些操作由后台线程进行，在抽取buffer pool的碎片并回收时允许其他线程访问buffer pool，其他过程中buffer pool不允许访问。  
所以在线调整buffer pool还是对在线事务有影响。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 2]

**innodb\_old\_blocks\_pct**  
MySQL的LRU算法经过优化，将LRU链分为young区和old区，这个参数控制分配多少比例给old区，例如默认值37，即分配大概八分之三的LRU链给old区使用。  
可取值5到95。可在线调整。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 3]

**innodb\_old\_blocks\_time**  
这个参数控制LRU链中old区的页经过多少毫秒后才移动到young区，如果读取到old区的页而该页在old区停留没有超过设定的时间，则不会移动到young区。默认值是1000，即1秒。可在线调整。  
通常是用于防止全表扫描等一次性读取对LRU的冲刷，以及避免热数据被大量的短期内读的冷数据取代。  
设为0时，则old区的数据一旦被读取就会移动到young区。  
LRU的优先淘汰LRU链尾的页，即old区的末端。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 4]

**innodb\_read\_ahead\_threshold**  
线性预读的触发阈值，可取值0到64。默认值是56，意思是当在一个extent里顺序读取了56个页，那么innodb就会认为很有可能继续顺序读取该extent剩余的页，那么innodb就会把整个extent都异步地预先读取到buffer pool里，以备后续需要的时候直接读取内存。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 5]

**innodb\_random\_read\_ahead**  
是否开启随机预读，意识是如果innodb发现buffer pool已经有了同属于一个extent的13个连续的页，就会把这个extent剩余的页都预先读取到buffer pool里。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 6]

**innodb\_adaptive\_flushing**  
是否开启自适应刷盘调整，默认开启。即自动调整刷盘的速度。  
什么是刷盘的速度？比如一段时间内刷100页和有多少脏页就刷多少的区别，就是速度的概念。  
innodb会用一种启发式的算法，根据buffer pool里的脏页数量，以及redo文件写入速度，来控制刷盘速度。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 7]

**innodb\_adaptive\_flushing\_lwm**  
日志文件使用量的百分率低水位，当达到这个界限，则会触发自适应刷盘，即使没有开启innodb\_adaptive\_flushing。  
默认值10，最大值70。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 8]

**innodb\_flush\_neighbors**  
在刷盘时是否把要写入磁盘的页所在的区的其他页也一起刷盘。  
当使用HDD硬盘设备时，开启innodb\_flush\_neighbors可以减少IO压力，其实是减少了寻道时间，因为同一个extent通常在磁盘上是相邻的页。  
而对于SSD，寻道时间并不是一个影响性能的关键因素，反而不需要开启innodb\_flush\_neighbors。  
可取值0,1，2，默认值为1。  
0，将邻接块一同刷盘的特性关闭；  
1，将同一个区的所有块一同刷盘；  
2，只将同一个区的赃块一同刷盘。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 9]

**innodb\_flushing\_avg\_loops**  
innodb保存之前flush状态的迭代次数，有点拗口，意思就是对于自适应flush应对负载变化的积极程度，以便能够更顺滑地调整flush速度。  
默认值是30，最小值1，最大值1000。可动态调整。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 10]

**innodb\_lru\_scan\_depth**  
每一秒钟都会有一个后台线程page cleaner thread来扫描LRU上的脏页并刷盘，该参数控制扫描链表的深度，即页数。  
默认值1024，最小值100。可动态调整。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 11]

**innodb\_max\_dirty\_pages\_pct**  
buffer pool的脏页比例上限，MySQL会通过flush来保持脏页比例不超过该设定。默认至75，代表75%。可取值0到99。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 12]

**innodb\_max\_dirty\_pages\_pct\_lwm**  
脏页比例上限的低水位，超过低水位就会提前进行flush，避免超过脏页上限。默认值0，表示禁用该特性。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 13]

**innodb\_buffer\_pool\_filename**  
innodb\_buffer\_pool\_dump\_at\_shutdown或innodb\_buffer\_pool\_dump\_now用于记录tablespace IDs和page IDs的文件名.  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 14]

**innodb\_buffer\_pool\_dump\_at\_shutdown**  
在实例正常关闭时，将buffer pool导出到磁盘文件，以便下次启动时可以加载回来，那么buffer pool的数据就不需要经过业务运行来warm up。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 15]

**innodb\_buffer\_pool\_load\_at\_startup**  
在实例启动时加载导出的buffer pool数据  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 16]

**innodb\_buffer\_pool\_dump\_now**  
马上导出buffer pool的数据到磁盘文件  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 17]

**innodb\_buffer\_pool\_load\_now**  
马上加载以前导出的buffer poo数据  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 18]

**innodb\_buffer\_pool\_dump\_pct**  
控制buffer pool导出时的采样比例，可取值1到100.  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 19]

**innodb\_buffer\_pool\_load\_abort**  
中断buffer pool数据的加载  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 20]

innodb\_io\_capacity  
告诉innodb当前系统磁盘的性能，可设为所用磁盘的iops。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 21]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70]: /images/20230808/2fa1e61683234ecd91ecfa47d8d4fd2c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 1]: /images/20230808/bb4e9fb8969e4cb0905ecaabecb45f60.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 2]: /images/20230808/d5e4a103b79746d5a2cba662d165b553.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 3]: /images/20230808/6cf46d8dc73a484cbdcf26a175619ea4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 4]: /images/20230808/fa03e582017c456ea01f3205dc09ff7a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 5]: /images/20230808/506ac5b62db84660ba892811d5346714.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 6]: /images/20230808/ccc4d95f5b2c498d9d188918918faed6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 7]: /images/20230808/b55646b70df6442e8721e57564fc17af.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 8]: /images/20230808/77426a7e2f1440499f868c3726cf1ad7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 9]: /images/20230808/5834012a48f0433a86ce4b28ec6c6900.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 10]: /images/20230808/a870fc1645fd441494959f342f6fae60.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 11]: /images/20230808/469e4ee3ee8d4363afe85779455998d7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 12]: /images/20230808/82f1036043b6428a92531c81a4842871.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 13]: /images/20230808/dd1d5a8045774c8db8f72511f7d4c5f5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 14]: /images/20230808/806d52f8e5b04e49ab143c599de88360.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 15]: /images/20230808/d406fa0a04d0453c84fe9856cfb6de1a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 16]: /images/20230808/b9960857b42f4b32aed9b8ab31a80b6d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 17]: /images/20230808/d0dd38c256394e92b9252e78369510bc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 18]: /images/20230808/ac3512af7a124811a9a96bc1b64c029f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 19]: /images/20230808/c7ea1f628a494ca6a2f35475538a2024.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 20]: /images/20230808/3626aafe2292439693490f28c90104e2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70 21]: /images/20230808/36651ac99658415ea7f60317aa4bb525.png