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

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

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