MySQL 5.7中如何动态修改innodb

110次阅读

共计 5389 个字符，预计需要花费 14 分钟才能阅读完成。

MySQL 5.7 中如何动态修改 innodb_buffer_pool 大小，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面丸趣 TV 小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。

MySQL5.7 版本开始支持 buffer pool 动态调整大小，每个 buffer_pool_instance 都由同样个数的 chunk 组成(chunks 数组), 每个 chunk 内存大小为 innodb_buffer_pool_chunk_size(实际会偏大 5%，用于存放 chuck 中的 block 信息)。
buffer pool 以 innodb_buffer_pool_chunk_size 为单位进行动态增大和缩小。调整前后 innodb_buffer_pool_size 应一直保持是 innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances 的倍数。

实验如下:

C:\Users\duansf mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.17-log MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type help; or \h for help. Type \c to clear the current input statement.

mysql SELECT @@innodb_buffer_pool_size;
+—————————+
| @@innodb_buffer_pool_size |
+—————————+
| 134217728 |
+—————————+
1 row in set (0.00 sec)

将 innodb_buffer_pool_size 从 134217728 扩大到 268435456

mysql SET GLOBAL innodb_buffer_pool_size=268435456;
Query OK, 0 rows affected (0.02 sec)

mysql SELECT @@innodb_buffer_pool_size;
+—————————+
| @@innodb_buffer_pool_size |
+—————————+
| 268435456 |
+—————————+
1 row in set (0.00 sec)

— 查看日志记录(.err 结尾的文件)
2017-03-09T05:41:50.036769Z 6 [Note] InnoDB: Requested to resize buffer pool. (new size: 268435456 bytes)
2017-03-09T05:41:50.067742Z 0 [Note] InnoDB: Resizing buffer pool from 134217728 to 268435456 (unit=134217728).
2017-03-09T05:41:50.068754Z 0 [Note] InnoDB: Disabling adaptive hash index.
2017-03-09T05:41:50.229853Z 0 [Note] InnoDB: disabled adaptive hash index.
2017-03-09T05:41:50.230853Z 0 [Note] InnoDB: Withdrawing blocks to be shrunken.
2017-03-09T05:41:50.230853Z 0 [Note] InnoDB: Latching whole of buffer pool.
2017-03-09T05:41:50.231853Z 0 [Note] InnoDB: buffer pool 0 : resizing with chunks 1 to 2.
2017-03-09T05:41:50.257873Z 0 [Note] InnoDB: buffer pool 0 : 1 chunks (8192 blocks) were added.
2017-03-09T05:41:50.274899Z 0 [Note] InnoDB: Completed to resize buffer pool from 134217728 to 268435456.
2017-03-09T05:41:50.275895Z 0 [Note] InnoDB: Re-enabled adaptive hash index.
2017-03-09T05:41:50.276895Z 0 [Note] InnoDB: Completed resizing buffer pool at 170309 13:41:50.

加大 buffer pool 的过程大致如下：
1、以 innodb_buffer_pool_chunk_size 为单位，分配新的内存 pages；
2、扩展 buffer pool 的 AHI(adaptive hash index)链表，将新分配的 pages 包含进来；
3、将新分配的 pages 添加到 free list 中；

将 innodb_buffer_pool_size 从 268435456 缩减回 134217728

mysql SET GLOBAL innodb_buffer_pool_size=134217728;
Query OK, 0 rows affected (0.00 sec)

mysql SELECT @@innodb_buffer_pool_size;
+—————————+
| @@innodb_buffer_pool_size |
+—————————+
| 134217728 |
+—————————+
1 row in set (0.00 sec)

— 查看日志记录(.err 结尾的文件)
2017-03-09T05:57:42.759623Z 6 [Note] InnoDB: Requested to resize buffer pool. (new size: 134217728 bytes)
2017-03-09T05:57:42.759623Z 0 [Note] InnoDB: Resizing buffer pool from 268435456 to 134217728 (unit=134217728).
2017-03-09T05:57:42.761625Z 0 [Note] InnoDB: Disabling adaptive hash index.
2017-03-09T05:57:42.762626Z 0 [Note] InnoDB: disabled adaptive hash index.
2017-03-09T05:57:42.762626Z 0 [Note] InnoDB: Withdrawing blocks to be shrunken.
2017-03-09T05:57:42.763627Z 0 [Note] InnoDB: buffer pool 0 : start to withdraw the last 8192 blocks.
2017-03-09T05:57:42.765642Z 0 [Note] InnoDB: buffer pool 0 : withdrawing blocks. (8192/8192)
2017-03-09T05:57:42.765642Z 0 [Note] InnoDB: buffer pool 0 : withdrew 8192 blocks from free list. Tried to relocate 0 pages (8192/8192).
2017-03-09T05:57:42.767636Z 0 [Note] InnoDB: buffer pool 0 : withdrawn target 8192 blocks.
2017-03-09T05:57:42.767636Z 0 [Note] InnoDB: Latching whole of buffer pool.
2017-03-09T05:57:42.768631Z 0 [Note] InnoDB: buffer pool 0 : resizing with chunks 2 to 1.
2017-03-09T05:57:42.772633Z 0 [Note] InnoDB: buffer pool 0 : 1 chunks (8192 blocks) were freed.
2017-03-09T05:57:42.772633Z 0 [Note] InnoDB: Completed to resize buffer pool from 268435456 to 134217728.
2017-03-09T05:57:42.773633Z 0 [Note] InnoDB: Re-enabled adaptive hash index.
2017-03-09T05:57:42.774648Z 0 [Note] InnoDB: Completed resizing buffer pool at 170309 13:57:42.

缩减 buffer pool 的过程大致如下：
1、重整 buffer pool，准备回收 pages；
2、以 innodb_buffer_pool_chunk_size 为单位，释放删除这些 pages（这个过程会有一点点耗时）；
3、调整 AHI 链表，使用新的内存地址。

附:resize 的详细流程如下:
如果开启了 AHI，需禁用 AHI
如果是收缩内存
计算需收缩的 chunk 数，从 chunks 开始尾部删除指定个数的 chunk.
锁 buf_pool
从 free_list 中摘除待删 chunk 的 page 放入待删链表 buf_pool- withdraw
如果待删 chunk 的 page 为脏页，则刷脏
重新加载 LRU 中要删除的页，从 LRU 中摘除，重新从 free 列表获取 page 老的 page 放入待删链表 buf_pool- withdraw
释放 buffer pool 锁
如果需收缩的 chunk pages 没有收集全，重复 2 -6
开始 resize
锁住所有 instance 的 buffer_pool，page_hash
收缩 pool：以 chunk 为单位释放要收缩的内存
清空 withdraw 列表 buf_pool- withdraw
增大 pool: 分配新的 chunk
重新分配 buf_pool- chunks
如果改变 / 缩小超过 2 倍，会重置 page hash，改变桶大小
释放 buffer_pool,page_hash 锁
如果改变 / 缩小超过 2 倍, 会重启和 buffer pool 大小相关的内存结构，如锁系统 (lock_sys_resize)，AHI(btr_search_sys_resize), 数据字段(dict_resize) 等
如果禁用了 AHI，此时开启