第92回　innodb_dedicated_serverによるbuffer_poolの割り当て

MySQLで大切なパラメータの1つとして、innodb_buffer_pool_sizeというバッファプールのサイズを決めるパラメータがあります。デフォルトは128MBで、この値を変更することで大きくパフォーマンスが良くなる可能性があります。MySQL8.0ではinnodb_dedicated_serverをONに設定することで、起動時に環境のメモリに応じて値を設定する仕組みが導入されました。

今回は、innodb_dedicated_serverをONにするとinnodb_buffer_pool_sizeの容量がどれくらい設定されるのかを確認し、オンラインでinnodb_buffer_pool_sizeの変更を実施してみたいと思います。なお、実行環境はMySQL 8.0.15、OSはCentOS 7を利用しています。

innodb_dedicated_server

innodb_dedicated_serverは、MySQL8.0.3で追加されたシステム変数です。この設定をONにすることで、以下の4つのシステム変数を自動で設定します。

innodb_buffer_pool_size
innodb_log_file_size
innodb_log_files_in_group
innodb_flush_method

innodb_dedicated_serverをONにすると、サーバーのリソースに応じて上記のパラメータが変動します。ONにするにはmy.cnfにinnodb_dedicated_server=1を設定するか、起動のオプションに--innodb_dedicated_server=1をつけて実行します。

試しに、メモリ1GBのサーバーに対してinnodb_dedicated_serverをONにして、実行してみます。

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

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

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

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

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

各種値が設定されています。続いてinnodb_buffer_pool_size等の各種設定をmy.cnfに設定した状態で起動してみます。

2019-03-04T22:47:52.616834+09:00 0 [System] [MY-010116] [Server] /usr/local/mysql8015/bin/mysqld (mysqld 8.0.15) starting as process 24779
2019-03-04T22:47:52.628948+09:00 0 [Warning] [MY-012367] [InnoDB] Option innodb_dedicated_server is ignored for innodb_flush_methodbecause innodb_flush_method=O_DIRECT is specified explicitly.
2019-03-04T22:47:52.628990+09:00 0 [Warning] [MY-012358] [InnoDB] Option innodb_dedicated_server is ignored for innodb_buffer_pool_size because innodb_buffer_pool_size=536870912 is specified explicitly.
2019-03-04T22:47:52.629096+09:00 0 [Warning] [MY-012360] [InnoDB]  Option innodb_dedicated_server is ignored for innodb_log_file_size because innodb_log_file_size=268435456 is specified explicitly.

起動時に上記のようなワーニングが出力されました。innodb_dedicated_serverをONにしても、my.cnfで設定した値が優先して設定されます。

innodb_buffer_pool_size=512Mで設定した場合

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

innodb_dedicated_serverをONにした場合、メモリが1GB未満の場合は128MB,1～4GBの場合はメモリの50％、4GB以上の場合はメモリの75％がinnodb_buffer_pool_sizeに割り当てられます。

メモリ	innodb_buffer_pool_size
1GB未満	128MB
1GB～4GB	メモリの50％
4GB以上	メモリの75％

試しにメモリが128GBのサーバーで実施してみます。

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

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

innodb_buffer_pool_sizeに約100GB割り当てられました。このように、innodb_dedicated_serverは自動でinnodb_buffer_pool_sizeの割り当てを実施します。

innodb_dedicated_serverはあくまでもMySQL専用として動かすことを前提としているため、多くのリソースをMySQLに占有されます。なので他のアプリケーションなども動いているような環境で設定することはおすすめしません。

その他の各種設定される値については、公式ドキュメントの15.8.12 Enabling Automatic Configuration for a Dedicated MySQL Serverをご覧ください。

innodb_buffer_pool_sizeの変更

MySQL5.6とそれ以前までは、innodb_buffer_pool_sizeを変更する場合にMySQLの再起動が必要でしたが、MySQL5.7からオンラインでバッファプールサイズを変更することが可能になりました。

もし、最初に設定したinnodb_buffer_pool_sizeが小さいと感じたり、逆にデータ容量に対してinnodb_buffer_pool_sizeを割り当て過ぎたと感じた場合はオンラインで変更することも可能です。

オンラインでinnodb_buffer_pool_sizeを変更する

innodb_buffer_pool_sizeに設定する値はinnodb_buffer_pool_instances×innodb_buffer_pool_chunk_size（デフォルト128MB）単位で設定します。innodb_buffer_pool_instancesはサーバーのメモリが１GB以上の場合はデフォルトで8、1GB未満の場合は1になります。

オンラインでinnodb_buffer_pool_sizeを変更するときはSET GLOBAL innodb_buffer_pool_sizeを利用します。実施中はInnoDB_buffer_pool_resize_statusで進行状況を確認することができます。

mysql>SET GLOBAL innodb_buffer_pool_size = 256 * 1024 * 1024;
Query OK, 0 rows affected (0.00 sec)

mysql>SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';
+----------------------------------+----------------------------------------------------+
| Variable_name                    | Value                                              |
+----------------------------------+----------------------------------------------------+
| Innodb_buffer_pool_resize_status | Completed resizing buffer pool at 190306  2:01:24. |
+----------------------------------+----------------------------------------------------+
1 row in set (0.02 sec)

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

SET GLOBALはmy.cnfでの設定と違ってMやGなどの単位接尾辞は使えませんが、代わりに 256 * 1024 * 1024などの乗算が使えます。

また、innnodb_buffer_pool_sizeとinnodb_buffer_pool_instances、innodb_buffer_pool_chunk_sizeの設定値によって、各種値が適切に調整されることがあります。詳細は15.8.3.1 Configuring InnoDB Buffer Pool SizeのConfiguring InnoDB Buffer Pool Chunk Sizeをご確認ください。

innodb_buffer_pool_sizeをオンラインで変更するときの注意

innodb_buffer_pool_sizeをオンラインで変更しようとする時に、長めのトランザクション処理や高いスループットのトランザクションを実行していると、innodb_buffer_pool_sizeの変更が待機になるおそれがあります。実行する際はバッチ処理や負荷がかかるような時間帯は避けて実施することをおすすめします。

オンラインでbuffer_poolのサイズを調整したときの内部的な挙動については、公式ドキュメント15.8.3.1 Configuring InnoDB Buffer Pool SizeのOnline Buffer Pool Resizing Internalsに記述されています。こちらもあわせてご確認ください。

まとめ

今回はinnodb_dedicated_serverでのinnodb_buffer_pool_sizeの設定とオンラインでの変更について紹介しました。innodb_buffer_pool_sizeの設定に迷ったときはぜひ検討してみてください。