MySQL 5.7在线调整innodb buffer pool size操作

转载

小叔叔书 2021-08-09 16:37:26 博主文章分类：Mysql

文章标签 mysql 字节数数据库 MySQL 文章分类 MySQL 数据库

本次调整的数据库版本如下：

mysql> select version();
+------------+
| version() |
+------------+
| 5.7.18-log |
+------------+
1 row in set (0.01 sec)

step1：
mysql> show variables like '%innodb_buffer_pool_size%';
+-------------------------+-------------+
| Variable_name | Value |
+-------------------------+-------------+
| innodb_buffer_pool_size | 85899345920 |
+-------------------------+-------------+
1 row in set (0.00 sec)
当前buffer pool大小为80G

mysql> show variables like 'innodb_buffer_pool_instances';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| innodb_buffer_pool_instances | 16 |
+------------------------------+-------+
1 row in set (0.00 sec)
当前有16个buffer pool实例

我这里打算调整增加到90G，计算下字节数
mysql> select 90*1024*1024*1024;
+-------------------+
| 90*1024*1024*1024 |
+-------------------+
| 96636764160 |
+-------------------+
1 row in set (0.00 sec)

step2：开始调整
mysql> set global innodb_buffer_pool_size=96636764160 ;
Query OK, 0 rows affected (0.00 sec)

step3：检查结果
mysql> show variables like '%innodb_buffer_pool_size%';
+-------------------------+-------------+
| Variable_name | Value |
+-------------------------+-------------+
| innodb_buffer_pool_size | 96636764160 |
+-------------------------+-------------+
1 row in set (0.00 sec)

mysql> show variables like 'innodb_buffer_pool_instances';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| innodb_buffer_pool_instances | 16 |
+------------------------------+-------+
1 row in set (0.01 sec)

调整完成。

当然记得配置调整内核参数：shmmax为相应的值哦。

在线调整innodb buffer pool size的后台日志如下：
2017-07-06T15:48:22.864337+08:00 8 [Note] InnoDB: Requested to resize buffer pool. (new size: 96636764160 bytes)
2017-07-06T15:48:22.868202+08:00 0 [Note] InnoDB: Resizing buffer pool from 85899345920 to 96636764160 (unit=134217728).
2017-07-06T15:48:22.868222+08:00 0 [Note] InnoDB: Disabling adaptive hash index.
2017-07-06T15:48:23.081501+08:00 0 [Note] InnoDB: disabled adaptive hash index.
2017-07-06T15:48:23.081548+08:00 0 [Note] InnoDB: Withdrawing blocks to be shrunken.
2017-07-06T15:48:23.081562+08:00 0 [Note] InnoDB: Latching whole of buffer pool.
2017-07-06T15:48:23.081633+08:00 0 [Note] InnoDB: buffer pool 0 : resizing with chunks 40 to 45.
2017-07-06T15:48:23.132947+08:00 0 [Note] InnoDB: buffer pool 0 : 5 chunks (40960 blocks) were added.
2017-07-06T15:48:23.132985+08:00 0 [Note] InnoDB: buffer pool 1 : resizing with chunks 40 to 45.
2017-07-06T15:48:23.181751+08:00 0 [Note] InnoDB: buffer pool 1 : 5 chunks (40960 blocks) were added.
2017-07-06T15:48:23.185124+08:00 0 [Note] InnoDB: buffer pool 2 : resizing with chunks 40 to 45.
2017-07-06T15:48:23.238412+08:00 0 [Note] InnoDB: buffer pool 2 : 5 chunks (40960 blocks) were added.
2017-07-06T15:48:23.238454+08:00 0 [Note] InnoDB: buffer pool 3 : resizing with chunks 40 to 45.
2017-07-06T15:48:23.289552+08:00 0 [Note] InnoDB: buffer pool 3 : 5 chunks (40960 blocks) were added.
2017-07-06T15:48:23.289588+08:00 0 [Note] InnoDB: buffer pool 4 : resizing with chunks 40 to 45.
2017-07-06T15:48:23.338803+08:00 0 [Note] InnoDB: buffer pool 4 : 5 chunks (40960 blocks) were added.
2017-07-06T15:48:23.338838+08:00 0 [Note] InnoDB: buffer pool 5 : resizing with chunks 40 to 45.
2017-07-06T15:48:23.388154+08:00 0 [Note] InnoDB: buffer pool 5 : 5 chunks (40960 blocks) were added.
2017-07-06T15:48:23.388196+08:00 0 [Note] InnoDB: buffer pool 6 : resizing with chunks 40 to 45.
2017-07-06T15:48:23.437965+08:00 0 [Note] InnoDB: buffer pool 6 : 5 chunks (40960 blocks) were added.
2017-07-06T15:48:23.438005+08:00 0 [Note] InnoDB: buffer pool 7 : resizing with chunks 40 to 45.
2017-07-06T15:48:23.494750+08:00 0 [Note] InnoDB: buffer pool 7 : 5 chunks (40960 blocks) were added.
2017-07-06T15:48:23.494786+08:00 0 [Note] InnoDB: buffer pool 8 : resizing with chunks 40 to 45.
2017-07-06T15:48:23.544400+08:00 0 [Note] InnoDB: buffer pool 8 : 5 chunks (40960 blocks) were added.
2017-07-06T15:48:23.544436+08:00 0 [Note] InnoDB: buffer pool 9 : resizing with chunks 40 to 45.
2017-07-06T15:48:23.600689+08:00 0 [Note] InnoDB: buffer pool 9 : 5 chunks (40960 blocks) were added.
2017-07-06T15:48:23.600739+08:00 0 [Note] InnoDB: buffer pool 10 : resizing with chunks 40 to 45.
2017-07-06T15:48:23.650422+08:00 0 [Note] InnoDB: buffer pool 10 : 5 chunks (40960 blocks) were added.
2017-07-06T15:48:23.650458+08:00 0 [Note] InnoDB: buffer pool 11 : resizing with chunks 40 to 45.
2017-07-06T15:48:23.700974+08:00 0 [Note] InnoDB: buffer pool 11 : 5 chunks (40960 blocks) were added.
2017-07-06T15:48:23.701010+08:00 0 [Note] InnoDB: buffer pool 12 : resizing with chunks 40 to 45.
2017-07-06T15:48:23.759245+08:00 0 [Note] InnoDB: buffer pool 12 : 5 chunks (40960 blocks) were added.
2017-07-06T15:48:23.759287+08:00 0 [Note] InnoDB: buffer pool 13 : resizing with chunks 40 to 45.
2017-07-06T15:48:23.812728+08:00 0 [Note] InnoDB: buffer pool 13 : 5 chunks (40960 blocks) were added.
2017-07-06T15:48:23.812766+08:00 0 [Note] InnoDB: buffer pool 14 : resizing with chunks 40 to 45.
2017-07-06T15:48:23.863099+08:00 0 [Note] InnoDB: buffer pool 14 : 5 chunks (40960 blocks) were added.
2017-07-06T15:48:23.863134+08:00 0 [Note] InnoDB: buffer pool 15 : resizing with chunks 40 to 45.
2017-07-06T15:48:23.916792+08:00 0 [Note] InnoDB: buffer pool 15 : 5 chunks (40960 blocks) were added.
2017-07-06T15:48:23.917110+08:00 0 [Note] InnoDB: Completed to resize buffer pool from 85899345920 to 96636764160.
2017-07-06T15:48:23.917133+08:00 0 [Note] InnoDB: Re-enabled adaptive hash index.
2017-07-06T15:48:23.917148+08:00 0 [Note] InnoDB: Completed resizing buffer pool at 170706 15:48:23.

继续调整如下：
在线减小innodb buffer pool size大小为80G
mysql> set global innodb_buffer_pool_size=85899345920;
Query OK, 0 rows affected (0.00 sec)

如果不是80G呢？
mysql> set global innodb_buffer_pool_size=85899345950;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect innodb_buffer_pool_size value: '85899345950' |
+---------+------+------------------------------------------------------------------+
1 row in set (0.00 sec)

出现警告了，再看实际大小

mysql> show variables like '%innodb_buffer_pool_size%';
+-------------------------+-------------+
| Variable_name | Value |
+-------------------------+-------------+
| innodb_buffer_pool_size | 88046829568 |
+-------------------------+-------------+
1 row in set (0.00 sec)

这是因为，调整后的buffer pool大小必须满足如下条件：
innodb_buffer_pool_chunk_size* innodb_buffer_pool_instances的倍数，即128M的倍数。

---The end