MySQL 5.7中如何動態(tài)修改innodb_buffer_pool大小,很多新手對此不是很清楚,為了幫助大家解決這個難題,下面小編將為大家詳細(xì)講解,有這方面需求的人可以來學(xué)習(xí)下,希望你能有所收獲。
成都創(chuàng)新互聯(lián)公司-專業(yè)網(wǎng)站定制、快速模板網(wǎng)站建設(shè)、高性價比東興網(wǎng)站開發(fā)、企業(yè)建站全套包干低至880元,成熟完善的模板庫,直接使用。一站式東興網(wǎng)站制作公司更省心,省錢,快速模板網(wǎng)站建設(shè)找我們,業(yè)務(wù)覆蓋東興地區(qū)。費(fèi)用合理售后完善,10多年實體公司更值得信賴。
MySQL5.7版本開始支持buffer pool動態(tài)調(diào)整大小,每個buffer_pool_instance都由同樣個數(shù)的chunk組成(chunks數(shù)組), 每個chunk內(nèi)存大小為innodb_buffer_pool_chunk_size(實際會偏大5%,用于存放chuck中的block信息)。
buffer pool以innodb_buffer_pool_chunk_size為單位進(jìn)行動態(tài)增大和縮小。調(diào)整前后innodb_buffer_pool_size應(yīng)一直保持是innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances的倍數(shù)。
實驗如下:
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(這個過程會有一點點耗時);
3、調(diào)整AHI鏈表,使用新的內(nèi)存地址。
附:resize的詳細(xì)流程如下:
如果開啟了AHI,需禁用AHI
如果是收縮內(nèi)存
計算需收縮的chunk數(shù), 從chunks開始尾部刪除指定個數(shù)的chunk.
鎖buf_pool
從free_list中摘除待刪chunk的page放入待刪鏈表buf_pool->withdraw
如果待刪chunk的page為臟頁,則刷臟
重新加載LRU中要刪除的頁,從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倍,會重置page hash,改變桶大小
釋放buffer_pool,page_hash鎖
如果改變/縮小超過2倍,會重啟和buffer pool大小相關(guān)的內(nèi)存結(jié)構(gòu),如鎖系統(tǒng)(lock_sys_resize),AHI(btr_search_sys_resize), 數(shù)據(jù)字段(dict_resize)等
如果禁用了AHI,此時開啟
看完上述內(nèi)容是否對您有幫助呢?如果還想對相關(guān)知識有進(jìn)一步的了解或閱讀更多相關(guān)文章,請關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道,感謝您對創(chuàng)新互聯(lián)的支持。
網(wǎng)站題目:MySQL5.7中如何動態(tài)修改innodb_buffer_pool大小
分享地址:http://jinyejixie.com/article42/iisphc.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供關(guān)鍵詞優(yōu)化、網(wǎng)站改版、外貿(mào)建站、App開發(fā)、網(wǎng)站建設(shè)、網(wǎng)站內(nèi)鏈
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請盡快告知,我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場,如需處理請聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時需注明來源: 創(chuàng)新互聯(lián)