InnoDB バッファープールをオンラインで変更する

この記事は MySQL Casual Advent Calendar 2015 の7日目です。

MySQL 5.7の新機能、InnoDBバファープールのオンライン変更を試してみました。
5.6までは、バッファープールのサイズ変更には再起動が必要でした。
「思ったより、データが増えて、バッファが不足してきたので増やしたい」「割り当てすぎたので、減らしたい」といった場合に再起動なしで対応できるようになったので、嬉しいですね。

InnoDB バッファープールとは

InnoDB バッファープールの主な役割は、データをキャッシュしておくことです。
バッファープールが不十分だと、ディスクへの読み取りが発生し、パフォーマンスが低下します。

バッファープールのサイズはinnodb_buffer_pool_sizeで指定します。

バッファープールのオンライン変更

変更方法

方法は通常の設定変更と変わりません。

SET GLOBAL innodb_buffer_pool_size = 変更したいサイズ;

変更できるサイズの制限

バッファープールは内部的に複数の領域（インスタンス）に分割されています。
インスタンスの数は innodb_buffer_pool_instastancesで指定されています。

バッファープールは各インスタンスごとに、innodb_buffer_pool_chunk_sizeで指定したサイズ分、増減します。
バッファープールのサイズはinstances x chunk_size 単位でしか調整できません。

# instances は 4, chunk size は 128M
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 4              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 1073741824     |
+-------------------------------------+----------------+
10 rows in set (0.00 sec)

mysql> SELECT 1073741824 + 134217728 + 134217728;
+------------------------------------+
| 1073741824 + 134217728 + 134217728 |
+------------------------------------+
|                         1342177280 |
+------------------------------------+
1 row in set (0.00 sec)

# chunk ２つ分追加してみる
mysql> SET GLOBAL innodb_buffer_pool_size=1342177280;
Query OK, 0 rows affected, 1 warning (0.01 sec)

# Warning が出力されて、chunk ４つ分（512MB)に増加サイズが切り上げられる
mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------------------------+
| Level   | Code | Message                                                         |
+---------+------+-----------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect innodb_buffer_pool_size value: '1342177280' |
+---------+------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>  SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 1610612736 |
+-------------------------+------------+
1 row in set (0.00 sec)

注意点

バッファープールのリサイズが完了するまで、処理が一瞬ブロックされます。
試してみたところ、増やすデータ量が多いほど、ブロックされる時間が長いという結果になりました。レスポンスタイムにシビアな環境では細かい単位で調整したほうがよさそうです。