innodb_buffer_pool_chunck_size
buffer pool块大小,当buffer pool扩容或缩容时,以chunck为单位来进行空间resizing。只能在实例重启时修改生效。
修改chunck大小,增加和减少的空间需要是MB的倍数。
如果innodb_buffer_pool_chunck_size*innodb_buffer_pool_instances不能大于innodb_buffer_pool_size,也就是只能改小。
innodb_buffer_pool_instances
buffer pool可以分为多个子pool,称为实例,每个实例都专属的LRU链和相关的数据结构,已达到避免只有一个pool时的并发冲突。
innodb_buffer_pool_size
buffer pool总大小,设定最好是innodb_buffer_pool_instances*innodb_buffer_pool_chunck_size的倍数,否则MySQL会调整为适当的大小,因为只能按照innodb_buffer_pool_chunck_size来调节。
可以在线动态调整,但是不知道会有什么BUG。
在线调整时,可以通过以下命令查看调整进度:
SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';
同样也可以查看error log里的调整过程信息,如:
在线增大buffer pool的error log
[Note] InnoDB: Resizing buffer pool from 134217728 to 4294967296. (unit=134217728)
[Note] InnoDB: disabled adaptive hash index.
[Note] InnoDB: buffer pool 0 : 31 chunks (253952 blocks) was added.
[Note] InnoDB: buffer pool 0 : hash tables were resized.
[Note] InnoDB: Resized hash tables at lock_sys, adaptive hash index, dictionary.
[Note] InnoDB: completed to resize buffer pool from 134217728 to 4294967296.
[Note] InnoDB: re-enabled adaptive hash index.
在线减小buffer pool的error log