关于数据库:MySQL-80中InnoDB-buffer-pool-size进度更透明

44次阅读

共计 5773 个字符，预计需要花费 15 分钟才能阅读完成。

GreatSQL 社区原创内容未经受权不得随便应用，转载请分割小编并注明起源。
GreatSQL 是 MySQL 的国产分支版本，应用上与 MySQL 统一。
作者：Yejinrong/ 叶金荣
文章起源：GreatSQL 社区原创

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 看看这几个状态变量别离是怎么的。压测期间，在另一个窗口跑上面的命令察看：

$ 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|

此外，还能够从日志文件中查看具体过程：

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-re…
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-variabl…

Enjoy GreatSQL :)

## 对于 GreatSQL

GreatSQL 是由万里数据库保护的 MySQL 分支，专一于晋升 MGR 可靠性及性能，反对 InnoDB 并行查问个性，是实用于金融级利用的 MySQL 分支版本。

相干链接：GreatSQL 社区 Gitee GitHub Bilibili