MySQL 5.7 参数innodb_buffer_pool_size 在线修改 | 配置文件 | 命令行

innodb_buffer_pool_size默认 128M

在线修改

SET GLOBAL innodb_buffer_pool_size=68719476736;

复制

查看修改进度

SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';

复制

缓冲池大小调整进度会记录在错误日志error.log中

2023-12-07T08:51:42.868337Z 0 [Note] InnoDB: Resizing buffer pool from 134217728 to 68719476736 (unit=134217728).
2023-12-07T08:51:42.868458Z 0 [Note] InnoDB: Disabling adaptive hash index.
2023-12-07T08:51:42.869158Z 0 [Note] InnoDB: disabled adaptive hash index.
2023-12-07T08:51:42.869171Z 0 [Note] InnoDB: Withdrawing blocks to be shrunken.
2023-12-07T08:51:42.869179Z 0 [Note] InnoDB: Latching whole of buffer pool.
2023-12-07T08:51:42.869192Z 0 [Note] InnoDB: buffer pool 0 : resizing with chunks 1 to 512.
2023-12-07T08:51:48.538571Z 0 [Note] InnoDB: buffer pool 0 : 511 chunks (4186112 blocks) were added.
2023-12-07T08:51:48.538655Z 0 [Note] InnoDB: Resizing hash tables.
2023-12-07T08:51:48.635982Z 0 [Note] InnoDB: buffer pool 0 : hash tables were resized.
2023-12-07T08:51:48.636163Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5049ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
2023-12-07T08:51:48.636170Z 0 [Note] InnoDB: Resizing also other hash tables.
2023-12-07T08:51:49.941980Z 0 [Note] InnoDB: Resized hash tables at lock_sys, adaptive hash index, dictionary.
2023-12-07T08:51:49.942044Z 0 [Note] InnoDB: Completed to resize buffer pool from 134217728 to 68719476736.
2023-12-07T08:51:49.942057Z 0 [Note] InnoDB: Re-enabled adaptive hash index.
2023-12-07T08:51:49.942076Z 0 [Note] InnoDB: Completed resizing buffer pool at 231207 16:51:49.

复制

在线缓冲池调整内容机制

调整操作由后台线程执行。当增加缓冲池大小时，调整操作：

• 逐块添加页面（块大小由 innodb_buffer_pool_chunk_size 定义）
• 将哈希表、列表和指针转换为使用内存中的新地址
• 将新页面添加到空闲列表

在这些操作进行时，其他线程无法访问缓冲池。
当减小缓冲池大小时，调整操作：

• 对缓冲池进行碎片整理并撤回（释放）页面
• 逐块删除页面（块大小由 innodb_buffer_pool_chunk_size 定义）
• 将哈希表、列表和指针转换为使用内存中的新地址
  在这些操作中，只有对缓冲池进行碎片整理和撤回页面允许其他线程并发访问缓冲池。

Online Buffer Pool Resizing Internals
The resizing operation is performed by a background thread. When increasing the size of the buffer pool, the resizing operation:

• Adds pages in chunks (chunk size is defined by innodb_buffer_pool_chunk_size)
• Converts hash tables, lists, and pointers to use new addresses in memory
• Adds new pages to the free list
  While these operations are in progress, other threads are blocked from accessing the buffer pool.
  When decreasing the size of the buffer pool, the resizing operation:
• Defragments the buffer pool and withdraws (frees) pages
• Removes pages in chunks (chunk size is defined by innodb_buffer_pool_chunk_size)
• Converts hash tables, lists, and pointers to use new addresses in memory
  Of these operations, only defragmenting the buffer pool and withdrawing pages allow other threads to
  access to the buffer pool concurrently.

配置文件修改：

[mysqld]
innodb_buffer_pool_size=134217728

复制

命令行：

复制