MySQL 5.7中如何動(dòng)態(tài)修改innodb_buffer_pool大小

MySQL 5.7中如何動(dòng)態(tài)修改innodb_buffer_pool大小，很多新手對(duì)此不是很清楚，為了幫助大家解決這個(gè)難題，下面小編將為大家詳細(xì)講解，有這方面需求的人可以來學(xué)習(xí)下，希望你能有所收獲。

MySQL5.7版本開始支持buffer pool動(dòng)態(tài)調(diào)整大小，每個(gè)buffer_pool_instance都由同樣個(gè)數(shù)的chunk組成(chunks數(shù)組), 每個(gè)chunk內(nèi)存大小為innodb_buffer_pool_chunk_size(實(shí)際會(huì)偏大5%，用于存放chuck中的block信息)。
buffer pool以innodb_buffer_pool_chunk_size為單位進(jìn)行動(dòng)態(tài)增大和縮小。調(diào)整前后innodb_buffer_pool_size應(yīng)一直保持是innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances的倍數(shù)。

實(shí)驗(yàn)如下:

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 擴(kuò)大到 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結(jié)尾的文件)
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為單位，分配新的內(nèi)存pages；
2、擴(kuò)展buffer pool的AHI(adaptive hash index)鏈表，將新分配的pages包含進(jìn)來；
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結(jié)尾的文件)
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，準(zhǔn)備回收pages；
2、以innodb_buffer_pool_chunk_size為單位，釋放刪除這些pages（這個(gè)過程會(huì)有一點(diǎn)點(diǎn)耗時(shí)）；
3、調(diào)整AHI鏈表，使用新的內(nèi)存地址。


附:resize的詳細(xì)流程如下:
如果開啟了AHI，需禁用AHI
如果是收縮內(nèi)存
計(jì)算需收縮的chunk數(shù)， 從chunks開始尾部刪除指定個(gè)數(shù)的chunk.
鎖buf_pool
從free_list中摘除待刪chunk的page放入待刪鏈表buf_pool->withdraw
如果待刪chunk的page為臟頁(yè)，則刷臟
重新加載LRU中要?jiǎng)h除的頁(yè)，從LRU中摘除，重新從free列表獲取page老的page放入待刪鏈表buf_pool->withdraw
釋放buffer pool鎖
如果需收縮的chunk pages沒有收集全，重復(fù)2-6
開始resize
鎖住所有instance的buffer_pool，page_hash
收縮pool：以chunk為單位釋放要收縮的內(nèi)存
清空withdraw列表buf_pool->withdraw
增大pool:分配新的chunk
重新分配buf_pool->chunks
如果改變/縮小超過2倍，會(huì)重置page hash，改變桶大小
釋放buffer_pool,page_hash鎖
如果改變/縮小超過2倍,會(huì)重啟和buffer pool大小相關(guān)的內(nèi)存結(jié)構(gòu)，如鎖系統(tǒng)(lock_sys_resize)，AHI(btr_search_sys_resize), 數(shù)據(jù)字段(dict_resize)等
如果禁用了AHI，此時(shí)開啟

看完上述內(nèi)容是否對(duì)您有幫助呢？如果還想對(duì)相關(guān)知識(shí)有進(jìn)一步的了解或閱讀更多相關(guān)文章，請(qǐng)關(guān)注億速云行業(yè)資訊頻道，感謝您對(duì)億速云的支持。