如果 初始化缓冲池时 innodb_buffer_pool_chunk_size* innodb_buffer_pool_instances大于当前缓冲池大小, innodb_buffer_pool_chunk_size 则截断为 innodb_buffer_pool_size/ innodb_buffer_pool_instances。
缓冲池大小必须始终等于innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数。如果更改 innodb_buffer_pool_chunk_size, innodb_buffer_pool_size 则会自动舍入为等于innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数。初始化缓冲池时会进行调整。
举例说明:
innodb_buffer_pool_size
设置为3G,innodb_buffer_pool_instances
设置为8。innodb_buffer_pool_chunk_size默认值为
128M。
3G是有效的innodb_buffer_pool_size值,因为3G是innodb_buffer_pool_instances = 8 * innodb_buffer_pool_chunk_size = 128M
的倍数
innodb_buffer_pool_size
设置为3G,innodb_buffer_pool_instances
设置为16. innodb_buffer_pool_chunk_size
为128M。
3G不是有效的innodb_buffer_pool_size值,因为3G不是innodb_buffer_pool_instances = 16 * innodb_buffer_pool_chunk_size = 128M
的倍数,可以看出innodb_buffer_pool_size
的值自动调整到4GB。
截断举例:
如果缓冲池初始化的大小为2GB
(2147483648字节), 4
缓冲池实例和块大小1GB
(1073741824字节),则块大小将截断为等于innodb_buffer_pool_size
/ 的值innodb_buffer_pool_instances
shell> mysqld --innodb-buffer-pool-size=2147483648 --innodb-buffer-pool-instances=4 --innodb-buffer-pool-chunk-size=1073741824; mysql> SELECT @@innodb_buffer_pool_size; +---------------------------+ | @@innodb_buffer_pool_size | +---------------------------+ | 2147483648 | +---------------------------+ mysql> SELECT @@innodb_buffer_pool_instances; +--------------------------------+ | @@innodb_buffer_pool_instances | +--------------------------------+ | 4 | +--------------------------------+ # Chunk size was set to 1GB (1073741824 bytes) on startup but was # truncated to innodb_buffer_pool_size / innodb_buffer_pool_instances mysql> SELECT @@innodb_buffer_pool_chunk_size; +---------------------------------+ | @@innodb_buffer_pool_chunk_size | +---------------------------------+ | 536870912 | +---------------------------------+
注意:innodb_buffer_pool_chunk_size可以以1MB(1048576字节)为单位增加或减少,但只能在启动时,命令行字符串或MySQL配置文件中进行修改。为避免潜在的性能问题,块数(innodb_buffer_pool_size
/ innodb_buffer_pool_chunk_size
)不应超过1000。更改时应小心 innodb_buffer_pool_chunk_size
,因为更改此值可以自动增加缓冲池的大小。在更改之前 innodb_buffer_pool_chunk_size
,请计算它将产生的影响, innodb_buffer_pool_size
以确保生成的缓冲池大小可以接受
在线缓冲池调整内部
调整大小操作由后台线程执行。增加缓冲池的大小时,调整大小操作:
-
添加页面
chunks
(块大小定义innodb_buffer_pool_chunk_size
) -
隐藏在内存中使用新地址的哈希表,列表和指针
-
将新页面添加到空闲列表
当这些操作正在进行时,其他线程将被阻止访问缓冲池。
减小缓冲池的大小时,调整大小操作:
-
对缓冲池进行碎片整理并撤消(释放)页面
-
删除页面
chunks
(块大小定义innodb_buffer_pool_chunk_size
) -
转换哈希表,列表和指针以在内存中使用新地址
参考:官方文档:
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_chunk_size
https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html