- GreatSQL社區(qū)原創(chuàng)內(nèi)容未經(jīng)授權(quán)不得隨意使用，轉(zhuǎn)載請聯(lián)系小編并注明來源。
- GreatSQL是MySQL的國產(chǎn)分支版本，使用上與MySQL一致。
- 作者：Yejinrong/葉金榮
- 文章來源：GreatSQL社區(qū)原創(chuàng)
MySQL 8.0 up up up~
從MySQL 5.7開始，支持在線動態(tài)調(diào)整 innodb buffer pool，并為此新增了一個狀態(tài)變量 Innodb_buffer_pool_resize_status，可以通過觀察它了解調(diào)整buffer pool過程中的一些狀態(tài)，例如 Resizing also other hash tables. 或 Completed resizing buffer pool at 230131 15:57:03.。
從8.0.31開始，針對innodb buffer pool在線動態(tài)resize特性，新增了下面兩個狀態(tài)變量，可以更方便透明觀察resize的進(jìn)度：
| 狀態(tài)變量 | 解釋 |
|---|---|
| Innodb_buffer_pool_resize_status_code | buffer pool resize狀態(tài)碼，共有以下幾種不同值： - 0: No Resize operation in progress - 1: Starting Resize - 2: Disabling AHI (Adaptive Hash Index) - 3: Withdrawing Blocks，清除/降低buffer pool pages（主要針對buffer pool縮小的情況） - 4: Acquiring Global Lock - 5: Resizing Pool - 6: Resizing Hash - 7: Resizing Failed |
| Innodb_buffer_pool_resize_status_progress | 報告當(dāng)前resize的進(jìn)度，用百分比進(jìn)度條方式展示，數(shù)值范圍從0-100 |
我們可以通過 Innodb_buffer_pool_resize_status_code 報告的狀態(tài)碼，了解當(dāng)前的狀態(tài)，以及是否出現(xiàn)報錯等情況。
狀態(tài)變量 Innodb_buffer_pool_resize_status_progress 可以實時查看當(dāng)前resize的百分比進(jìn)度，當(dāng)有多個buffer pool instance時，每完成一個instance resize，該值就會更新一次。每當(dāng) Innodb_buffer_pool_resize_status_code 狀態(tài)碼變化一次，則 progress 的進(jìn)度百分比會被重置為0，重新開始。當(dāng)整個buffer pool都resize完成后，最終顯示100。
接下來，我們找個正在跑sysbench壓測中的實例，對其在線調(diào)整buffer pool看看這幾個狀態(tài)變量分別是怎樣的。壓測期間，在另一個窗口跑下面的命令觀察：
$ while [ 1 ] ; do mysqladmin ext | grep -i Innodb_buffer_pool_resi | sed 's/ //g'; sleep 0.5; echo ; echo; done
...
|Innodb_buffer_pool_resize_status|bufferpool7:withdrawingblocks.(112590/122869)|
|Innodb_buffer_pool_resize_status_code|3|
|Innodb_buffer_pool_resize_status_progress|0|
...
|Innodb_buffer_pool_resize_status|bufferpool7:resizingwithchunks16to1.|
|Innodb_buffer_pool_resize_status_code|5|
|Innodb_buffer_pool_resize_status_progress|87|
...
|Innodb_buffer_pool_resize_status|Completedresizingbufferpoolat2302029:58:50.|
|Innodb_buffer_pool_resize_status_code|0|
|Innodb_buffer_pool_resize_status_progress|100|
此外，還可以從日志文件中查看詳細(xì)過程：
09:56:20.275388+08:00 30 .. Resizing buffer pool from 17179869184 to 8589934592 (unit=134217728). (new size: 8589934592 bytes)
09:56:20.275397+08:00 0 .. Status code 1: Resizing buffer pool from 17179869184 to 8589934592 (unit=134217728).
09:56:20.275417+08:00 0 .. Status code 1: 12% complete
09:56:20.275422+08:00 0 .. Status code 1: 25% complete
...
09:56:20.275439+08:00 0 .. Status code 1: 100% complete
09:56:20.275443+08:00 0 .. Status code 1: Completed
09:56:20.275446+08:00 0 .. Status code 2: Disabling adaptive hash index.
09:56:20.286907+08:00 0 .. disabled adaptive hash index.
09:56:20.286939+08:00 0 .. Status code 2: 12% complete
09:56:20.286943+08:00 0 .. Status code 2: 25% complete
...
09:56:20.286963+08:00 0 .. Status code 2: 100% complete
09:56:20.286966+08:00 0 .. Status code 2: Completed
09:56:20.286970+08:00 0 .. Status code 3: Withdrawing blocks to be shrunken.
09:56:20.286974+08:00 0 .. buffer pool 0 : start to withdraw the last 65530 blocks.
09:56:20.288848+08:00 0 .. Status code 3: buffer pool 0 : withdrawing blocks. (52526/65530)
09:56:20.288865+08:00 0 .. buffer pool 0 : withdrew 52512 blocks from free list. Tried to relocate 14 pages (52526/65530).
...
09:56:20.288934+08:00 0 .. Status code 3: buffer pool 0 : withdrawing blocks. (52526/65530)
09:56:20.288937+08:00 0 .. buffer pool 0 : withdrew 0 blocks from free list. Tried to relocate 0 pages (52526/65530).
09:56:20.288941+08:00 0 .. buffer pool 0 : will retry to withdraw later.
09:56:20.288951+08:00 0 .. buffer pool 1 : start to withdraw the last 65530 blocks.
09:56:20.290815+08:00 0 .. Status code 3: buffer pool 1 : withdrawing blocks. (53263/65530)
09:56:20.290824+08:00 0 .. buffer pool 1 : withdrew 53249 blocks from free list. Tried to relocate 14 pages (53263/65530). -- 嘗試釋放buffer pages失敗，將繼續(xù)重試
...
09:56:27.305382+08:00 0 .. buffer pool 7 : withdrew 0 blocks from free list. Tried to relocate 0 pages (54852/65530).
09:56:27.305385+08:00 0 .. buffer pool 7 : will retry to withdraw later.
09:56:27.305388+08:00 0 .. Will retry to withdraw 8 seconds later.
09:56:29.047052+08:00 0 .. Page cleaner took 12405ms to flush 83911 and evict 0 pages
09:56:35.305806+08:00 0 .. buffer pool 0 : start to withdraw the last 65530 blocks.
09:56:35.333782+08:00 0 .. Status code 3: buffer pool 0 : withdrawing blocks. (65530/65530)
09:56:35.333813+08:00 0 .. buffer pool 0 : withdrew 0 blocks from free list. Tried to relocate 13004 pages (65530/65530).
09:56:35.334245+08:00 0 .. buffer pool 0 : withdrawn target 65530 blocks.
09:56:35.334253+08:00 0 .. Status code 3: 12% complete
...
09:56:35.515813+08:00 0 .. Status code 3: 100% complete
09:56:35.515817+08:00 0 .. Status code 3: Completed
09:56:35.515821+08:00 0 .. Status code 4: Latching whole of buffer pool.
09:56:35.515824+08:00 0 .. Status code 4: 14% complete
09:56:35.515827+08:00 0 .. Status code 4: 28% complete
09:56:35.515838+08:00 0 .. Status code 4: 42% complete
09:56:35.515842+08:00 0 .. Status code 4: 57% complete
09:56:35.515845+08:00 0 .. Status code 4: 71% complete
09:56:35.515848+08:00 0 .. Status code 4: 85% complete
09:56:35.515851+08:00 0 .. Status code 4: 100% complete
09:56:35.515862+08:00 0 .. Status code 4: Completed
09:56:35.515865+08:00 0 .. Status code 5: Starting pool resize
09:56:35.515868+08:00 0 .. Status code 5: buffer pool 0 : resizing with chunks 16 to 8.
09:56:35.533458+08:00 0 .. buffer pool 0 : 8 chunks (65530 blocks) were freed.
09:56:35.533477+08:00 0 .. Status code 5: 12% complete
...
09:56:35.639495+08:00 0 .. Status code 5: buffer pool 7 : resizing with chunks 16 to 8.
09:56:35.655714+08:00 0 .. buffer pool 7 : 8 chunks (65530 blocks) were freed.
09:56:35.655732+08:00 0 .. Status code 5: 100% complete
09:56:35.655746+08:00 0 .. Completed to resize buffer pool from 17179869184 to 8589934592.
09:56:35.655750+08:00 0 .. Re-enabled adaptive hash index.
09:56:35.655755+08:00 0 .. Status code 5: Completed
09:56:35.655758+08:00 0 .. Status code 0: Completed resizing buffer pool at 230202 9:56:35.
09:56:35.655761+08:00 0 .. Status code 0: 100% complete
結(jié)合上一篇文章 MySQL 8.0不再擔(dān)心被垃圾SQL搞爆內(nèi)存，可以看到MySQL 8.0在各個細(xì)節(jié)方面做的是越來越好了。
延伸閱讀
- Configuring InnoDB Buffer Pool Size, https://dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool-resize.html
- Changes in MySQL 8.0.31, https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-31.html
- statvar_Innodb_buffer_pool_resize_status_code, https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html
Enjoy GreatSQL ??
關(guān)于 GreatSQL
GreatSQL是由萬里數(shù)據(jù)庫維護的MySQL分支，專注于提升MGR可靠性及性能，支持InnoDB并行查詢特性，是適用于金融級應(yīng)用的MySQL分支版本。
相關(guān)鏈接： GreatSQL社區(qū) Gitee GitHub Bilibili
GreatSQL社區(qū)：
社區(qū)博客有獎?wù)鞲逶斍椋篽ttps://greatsql.cn/thread-100-1-1.html
技術(shù)交流群：
微信：掃碼添加
GreatSQL社區(qū)助手微信好友，發(fā)送驗證信息加群。文章來源：http://www.zghlxwxcb.cn/news/detail-433512.html
文章來源地址http://www.zghlxwxcb.cn/news/detail-433512.html
到了這里，關(guān)于MySQL 8.0中InnoDB buffer pool size進(jìn)度更透明的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
