MySQL 8.0 up up up~
从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的进度，用百分比进度条方式展示，数值范围从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 的进度百分比会被重置为0，重新开始。当整个buffer pool都resize完成后，最终显示100。
接下来，我们找个正在跑sysbench压测中的实例，对其在线调整buffer pool看看这几个状态变量分别是怎样的。压测期间，在另一个窗口跑下面的命令观察：
代码语言：javascript复制$ 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|
此外，还可以从日志文件中查看详细过程：
代码语言：javascript复制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% complete
结合上一篇文章 MySQL 8.0不再担心被垃圾SQL搞爆内存，可以看到MySQL 8.0在各个细节方面做的是越来越好了。
延伸阅读
- 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
