放大mysql50进程_MySQL5.7 在线调整Innodb_Buffer_Pool_size不用重启mysql进程

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

weixin_34273479

2014-10-22 18:39:27

69

收藏

文章标签：

数据库

版权

在之前的版本，调整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.

(减少)[Note] InnoDB: Resizing buffer pool from 4294967296 to 134217728. (unit=134217728)

[Note] InnoDB: disabled adaptive hash index.

[Note] InnoDB: buffer pool 0 : start to withdraw the last 253952 blocks.

[Note] InnoDB: buffer pool 0 : withdrew 253952 blocks from free list. tried to relocate 0 pages. (253952/253952)

[Note] InnoDB: buffer pool 0 : withdrawn target 253952 blocks.

[Note] InnoDB: buffer pool 0 : 31 chunks (253952 blocks) was freed.

[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 4294967296 to 134217728.

[Note] InnoDB: re-enabled adaptive hash index.