当前位置:Gxlcms > mysql > MySQL5.7在线调整Innodb_Buffer_Pool_size不用重启mysql进程

MySQL5.7在线调整Innodb_Buffer_Pool_size不用重启mysql进程

时间:2021-07-01 10:21:17 帮助过:53人阅读

在之前的版本,调整Innodb_Buffer_Pool_size大小必须重启mysql进程才可以生效,如今在MySQL5.7里,可以直接动态设置,方便了很多。这个功能应用的场景:一、机器

在之前的版本,调整Innodb_Buffer_Pool_size大小必须重启mysql进程才可以生效,如今在MySQL5.7里,可以直接动态设置,方便了很多。

这个功能应用的场景:
一、机器增加内存,DBA粗心大意忘记调大Innodb_Buffer_Pool_size了
二、工作交接,新来的DBA发现前任DBA设置的Innodb_Buffer_Pool_size不合理

需要注意的地方,在调整Buffer_Pool期间,用户的请求将会阻塞,直到调整完毕,所以请勿在白天调整,在凌晨3-4点低峰期调整。

调整时,内部把数据页移动到一个新的位置,单位是块。如果想增加移动的速度,需要调整innodb_buffer_pool_chunk_size参数的大小,默认是128M。

例(把BP 128M增大为384M):

mysql> SELECT @@innodb_buffer_pool_size; +---------------------------+ | @@innodb_buffer_pool_size | +---------------------------+ | 134217728 | +---------------------------+ 1 row in set (0.00 sec) mysql> SELECT @@innodb_buffer_pool_chunk_size; +---------------------------------+ | @@innodb_buffer_pool_chunk_size | +---------------------------------+ | 134217728 | +---------------------------------+ 1 row in set (0.00 sec) mysql> SET GLOBAL innodb_buffer_pool_size=402653184; Query OK, 0 rows affected (0.01 sec) mysql> SELECT @@innodb_buffer_pool_size; +---------------------------+ | @@innodb_buffer_pool_size | +---------------------------+ | 402653184 | +---------------------------+ 1 row in set (0.00 sec)

innodb_buffer_pool_chunk_size的大小,,计算公式是innodb_buffer_pool_size / innodb_buffer_pool_instances

比如现在初始化innodb_buffer_pool_size为2G,innodb_buffer_pool_instances实例为4,innodb_buffer_pool_chunk_size设置为1G,那么会自动把innodb_buffer_pool_chunk_size 1G调整为512M,例:
./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 | +---------------------------+ 1 row in set (0.00 sec) mysql> SELECT @@innodb_buffer_pool_instances; +--------------------------------+ | @@innodb_buffer_pool_instances | +--------------------------------+ | 4 | +--------------------------------+ 1 row in set (0.00 sec) # 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 | +---------------------------------+ 1 row in set (0.00 sec)

监控Buffer Pool调整进程

mysql> SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status'; +----------------------------------+----------------------------------+ | Variable_name | Value | +----------------------------------+----------------------------------+ | Innodb_buffer_pool_resize_status | Resizing also other hash tables. | +----------------------------------+----------------------------------+ 1 row in set (0.00 sec)

查看错误日志:
(增大)

[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.

人气教程排行