MySQL 5.7中如何動態修改innodb_buffer_pool大小

MySQL 5.7中如何動態修改innodb_buffer_pool大小，很多新手對此不是很清楚，為了幫助大家解決這個難題，下面小編將為大家詳細講解，有這方面需求的人可以來學習下，希望你能有所收獲。

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)


Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.


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> show variables like 'innodb_buffer_pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 134217728      |
+-------------------------------------+----------------+
10 rows in set, 1 warning (0.61 sec)


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

看完上述內容是否對您有幫助呢？如果還想對相關知識有進一步的了解或閱讀更多相關文章，請關注億速云行業資訊頻道，感謝您對億速云的支持。