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にして、実行してみます。
各種値が設定されています。続いてinnodb_buffer_pool_size等の各種設定をmy.cnfに設定した状態で起動してみます。
起動時に上記のようなワーニングが出力されました。innodb_dedicated_serverをONにしても、my.cnfで設定した値が優先して設定されます。
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のサーバーで実施してみます。
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はサーバーのメモリが1GB以上の場合はデフォルトで8、1GB未満の場合は1になります。
オンラインでinnodb_buffer_pool_sizeを変更するときはSET GLOBAL innodb_buffer_pool_size
を利用します。実施中はInnoDB_buffer_pool_resize_status
で進行状況を確認することができます。
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の設定に迷ったときはぜひ検討してみてください。