MySQL 8.0中InnoDB Buffer Pool Size进度更透明

从MySQL 5.7开始，进度支持在线动态调整 innodb buffer pool，更透并为此新增了一个状态变量 Innodb_buffer_pool_resize_status，进度可以通过观察它了解调整buffer pool过程中的更透一些状态，例如 Resizing also other hash tables. 或 Completed resizing buffer pool at 230131 15:57:03.。进度

从8.0.31开始，更透针对innodb buffer pool在线动态resize特性，进度新增了下面两个状态变量，更透可以更方便透明观察resize的进度进度：

状态变量

解释

Innodb_buffer_pool_resize_status_code

buffer pool resize状态码，共有以下几种不同值：

- 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

报告当前resize的进度，云南idc服务商用百分比进度条方式展示，更透数值范围从0-100

我们可以通过 Innodb_buffer_pool_resize_status_code 报告的进度状态码，了解当前的更透状态，以及是进度否出现报错等情况。

状态变量 Innodb_buffer_pool_resize_status_progress 可以实时查看当前resize的百分比进度，当有多个buffer pool instance时，每完成一个instance resize，该值就会更新一次。每当 Innodb_buffer_pool_resize_status_code 状态码变化一次，则 progress 的b2b信息网进度百分比会被重置为0，重新开始。当整个buffer pool都resize完成后，最终显示100。

接下来，我们找个正在跑sysbench压测中的实例，对其在线调整buffer pool看看这几个状态变量分别是怎样的。压测期间，在另一个窗口跑下面的命令观察：

复制$ 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|1.2.3.4.5.6.7.8.9.10.11.12.13.

此外，还可以从日志文件中查看详细过程：

复制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失败，高防服务器将继续重试 ... 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% complete1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.57.58.59.60.

本文地址：http://www.bzve.cn/html/441e2999529.html