MySQL 5.7中如何動態修改innodb

140次閱讀

共計 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，此時開啟