翼度科技»论坛 编程开发 mysql 查看内容

MySQL 8.0中InnoDB buffer pool size进度更透明

8

主题

8

帖子

24

积分

新手上路

Rank: 1

积分
24

  • 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_codebuffer 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 FailedInnodb_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看看这几个状态变量分别是怎样的。压测期间,在另一个窗口跑下面的命令观察:
  1. $ while [ 1 ] ; do mysqladmin ext | grep -i Innodb_buffer_pool_resi | sed 's/ //g'; sleep 0.5; echo ; echo; done
  2. ...
  3. |Innodb_buffer_pool_resize_status|bufferpool7:withdrawingblocks.(112590/122869)|
  4. |Innodb_buffer_pool_resize_status_code|3|
  5. |Innodb_buffer_pool_resize_status_progress|0|
  6. ...
  7. |Innodb_buffer_pool_resize_status|bufferpool7:resizingwithchunks16to1.|
  8. |Innodb_buffer_pool_resize_status_code|5|
  9. |Innodb_buffer_pool_resize_status_progress|87|
  10. ...
  11. |Innodb_buffer_pool_resize_status|Completedresizingbufferpoolat2302029:58:50.|
  12. |Innodb_buffer_pool_resize_status_code|0|
  13. |Innodb_buffer_pool_resize_status_progress|100|
复制代码
此外,还可以从日志文件中查看详细过程:
  1. 09:56:20.275388+08:00 30 .. Resizing buffer pool from 17179869184 to 8589934592 (unit=134217728). (new size: 8589934592 bytes)
  2. 09:56:20.275397+08:00 0 .. Status code 1: Resizing buffer pool from 17179869184 to 8589934592 (unit=134217728).
  3. 09:56:20.275417+08:00 0 .. Status code 1: 12% complete
  4. 09:56:20.275422+08:00 0 .. Status code 1: 25% complete
  5. ...
  6. 09:56:20.275439+08:00 0 .. Status code 1: 100% complete
  7. 09:56:20.275443+08:00 0 .. Status code 1: Completed
  8. 09:56:20.275446+08:00 0 .. Status code 2: Disabling adaptive hash index.
  9. 09:56:20.286907+08:00 0 .. disabled adaptive hash index.
  10. 09:56:20.286939+08:00 0 .. Status code 2: 12% complete
  11. 09:56:20.286943+08:00 0 .. Status code 2: 25% complete
  12. ...
  13. 09:56:20.286963+08:00 0 .. Status code 2: 100% complete
  14. 09:56:20.286966+08:00 0 .. Status code 2: Completed
  15. 09:56:20.286970+08:00 0 .. Status code 3: Withdrawing blocks to be shrunken.
  16. 09:56:20.286974+08:00 0 .. buffer pool 0 : start to withdraw the last 65530 blocks.
  17. 09:56:20.288848+08:00 0 .. Status code 3: buffer pool 0 : withdrawing blocks. (52526/65530)
  18. 09:56:20.288865+08:00 0 .. buffer pool 0 : withdrew 52512 blocks from free list. Tried to relocate 14 pages (52526/65530).
  19. ...
  20. 09:56:20.288934+08:00 0 .. Status code 3: buffer pool 0 : withdrawing blocks. (52526/65530)
  21. 09:56:20.288937+08:00 0 .. buffer pool 0 : withdrew 0 blocks from free list. Tried to relocate 0 pages (52526/65530).
  22. 09:56:20.288941+08:00 0 .. buffer pool 0 : will retry to withdraw later.
  23. 09:56:20.288951+08:00 0 .. buffer pool 1 : start to withdraw the last 65530 blocks.
  24. 09:56:20.290815+08:00 0 .. Status code 3: buffer pool 1 : withdrawing blocks. (53263/65530)
  25. 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失败,将继续重试
  26. ...
  27. 09:56:27.305382+08:00 0 .. buffer pool 7 : withdrew 0 blocks from free list. Tried to relocate 0 pages (54852/65530).
  28. 09:56:27.305385+08:00 0 .. buffer pool 7 : will retry to withdraw later.
  29. 09:56:27.305388+08:00 0 .. Will retry to withdraw 8 seconds later.
  30. 09:56:29.047052+08:00 0 .. Page cleaner took 12405ms to flush 83911 and evict 0 pages
  31. 09:56:35.305806+08:00 0 .. buffer pool 0 : start to withdraw the last 65530 blocks.
  32. 09:56:35.333782+08:00 0 .. Status code 3: buffer pool 0 : withdrawing blocks. (65530/65530)
  33. 09:56:35.333813+08:00 0 .. buffer pool 0 : withdrew 0 blocks from free list. Tried to relocate 13004 pages (65530/65530).
  34. 09:56:35.334245+08:00 0 .. buffer pool 0 : withdrawn target 65530 blocks.
  35. 09:56:35.334253+08:00 0 .. Status code 3: 12% complete
  36. ...
  37. 09:56:35.515813+08:00 0 .. Status code 3: 100% complete
  38. 09:56:35.515817+08:00 0 .. Status code 3: Completed
  39. 09:56:35.515821+08:00 0 .. Status code 4: Latching whole of buffer pool.
  40. 09:56:35.515824+08:00 0 .. Status code 4: 14% complete
  41. 09:56:35.515827+08:00 0 .. Status code 4: 28% complete
  42. 09:56:35.515838+08:00 0 .. Status code 4: 42% complete
  43. 09:56:35.515842+08:00 0 .. Status code 4: 57% complete
  44. 09:56:35.515845+08:00 0 .. Status code 4: 71% complete
  45. 09:56:35.515848+08:00 0 .. Status code 4: 85% complete
  46. 09:56:35.515851+08:00 0 .. Status code 4: 100% complete
  47. 09:56:35.515862+08:00 0 .. Status code 4: Completed
  48. 09:56:35.515865+08:00 0 .. Status code 5: Starting pool resize
  49. 09:56:35.515868+08:00 0 .. Status code 5: buffer pool 0 : resizing with chunks 16 to 8.
  50. 09:56:35.533458+08:00 0 .. buffer pool 0 : 8 chunks (65530 blocks) were freed.
  51. 09:56:35.533477+08:00 0 .. Status code 5: 12% complete
  52. ...
  53. 09:56:35.639495+08:00 0 .. Status code 5: buffer pool 7 : resizing with chunks 16 to 8.
  54. 09:56:35.655714+08:00 0 .. buffer pool 7 : 8 chunks (65530 blocks) were freed.
  55. 09:56:35.655732+08:00 0 .. Status code 5: 100% complete
  56. 09:56:35.655746+08:00 0 .. Completed to resize buffer pool from 17179869184 to 8589934592.
  57. 09:56:35.655750+08:00 0 .. Re-enabled adaptive hash index.
  58. 09:56:35.655755+08:00 0 .. Status code 5: Completed
  59. 09:56:35.655758+08:00 0 .. Status code 0: Completed resizing buffer pool at 230202  9:56:35.
  60. 09:56:35.655761+08:00 0 .. Status code 0: 100% complete
复制代码
结合上一篇文章 MySQL 8.0不再担心被垃圾SQL搞爆内存,可以看到MySQL 8.0在各个细节方面做的是越来越好了。
延伸阅读

Enjoy GreatSQL
来源:https://www.cnblogs.com/greatsql/p/17372116.html
免责声明:由于采集信息均来自互联网,如果侵犯了您的权益,请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容,谢谢合作!

举报 回复 使用道具