はじめに
InnoDB関連のシステム変数の自動構成について学習したことを簡単にまとめます。
検証に使用した環境
DB：MySQL8.0
InnoDBの自動構成（innodb_dedicated_server）
サーバーのスペックをもとにシステム変数を推奨値に自動設定してくれる機能です。
※MySQL8.0.3から追加されました。
自動設定されるシステム変数は以下の4つです。
・innodb_buffer_pool_size
・innodb_log_files_in_group
・innodb_log_file_size
・innodb_flush_method
【注意】
こちらの機能は、使用しているサーバがMySQLのみを運用していることが前提とされています。
そのため、サーバにMySQLの他にアプリサーバ等を稼働させている場合は推奨されません。
他に稼働しているサービスがない、またはDockerやVMなどを使って、サーバがMySQLのみに注力できる環境の場合に効果を発揮します。
利用する
innodb_dedicated_serverシステム変数をONにして再起動することで利用できます。
my.cnfにinnodb_dedicated_serverの設定を追記します。
sh-4.2# sudo vi /etc/my.cnf
[mysqld]
・・・
innodb_dedicated_server=ON
my.cnfを修正し、MySQLを再起動するとON担ってることが確認できます。
mysql> show variables like 'innodb_dedicated_server';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| innodb_dedicated_server | ON |
+-------------------------+-------+
1 row in set (0.02 sec)
InnoDBバッファプールサイズ（innodb_buffer_pool_size）
グローバルバッファの中で一番重要なバッファで、テーブルのデータそのものと、インデックスのデータを持っています。
※デフォルトは128MB
自動構成を利用している場合
以下の通りにサイズが自動設定されます。
サーバのメモリが1GB未満の場合
→ 128MiB (デフォルト値)
サーバのメモリが1GB〜4GBの場合
→ サーバのメモリの50%
サーバのメモリが4GBを超える場合
→ サーバのメモリの75%
近年のシステムの本番環境などでメモリが1GBのマシンをDBサーバに使うケースは少ないと思います。
なので、デフォルト値である128MiBは圧倒的に足りないと感じられます。
InnoDBのログファイルの数（innodb_log_files_in_group）
ロググループ内のログファイルの数を指定します。
デフォルト (推奨) 値は2です。
対象となるログファイルの出力先は、innodb_log_group_home_dirで指定できます。
自動構成を利用した場合
InnoDBバッファプールサイズの自動設定値より、以下のように構成されます。
InnoDBバッファプールサイズが2Gバイト未満の場合
→ 2
InnoDBバッファプールサイズが8GB未満の場合
→ 丸められたバッファプールサイズ ROUND(バッファプールサイズ(GB))
InnoDBバッファプールサイズが8GB〜128GBの場合
→ 丸められたバッファプールサイズの75% ROUND(バッファプールサイズ(GB) * 0.75)
InnoDBバッファプールサイズが128GBを超える
→ 64
InnoDBのログファイルの出力先（innodb_log_group_home_dir）
InnoDBのRedoログファイルへのディレクトリパスです。
この数は、innodb_log_files_in_groupで指定されます。
innodb_log_files_in_groupがデフォルト(2)の場合、データディレクトリ内にib_logfile0、ib_logfile1という2つのファイルが作成されます。
ログファイルのサイズ（innodb_log_file_size）
InnoDBの各ログファイルのサイズ(バイト単位)です。
デフォルト値は48MBです。
増やすことでページのフラッシュを遅らせることができ、更新処理性能が期待できます。
設定値は、1MBから「1/グループ内のログファイルの数」のサイズまでが適切な設定値の範囲となります。
値を大きくするほど、バッファープール内で必要となるチェックポイントフラッシュアクティビティーの数が少なくなります。
これは、ページのフラッシュを遅らせることになり、ディスクI/Oを節約され、更新処理の性能向上が期待できます。
ただし、ログファイルを大きくすると、クラッシュリカバリの速度が遅くなります。
※MySQL5.5以上では、リカバリのパフォーマンスが改善され、ログファイルのサイズへの考慮事項も少なくなっているとのこと。
詳しくはこちら
自動構成を利用した場合
InnoDBバッファプールサイズの自動設定値より、以下のように構成されます。
InnoDBバッファプールサイズが8GB未満の場合
→ 512MiB
サーバのメモリが8GB〜128GBの場合
→ 1024MiB
InnoDBバッファプールサイズが128GBを超える
→ 2048MiB
innodb_flush_method
InnoDBのデータファイル、ログファイルをフラッシュする方法を指定します。
これにより、I/Oスループットが影響を受ける可能性があります。
Unix、Linuxでのみ変更が可能で、Windowsはasync_unbufferedで固定となります。
自動構成を利用した場合
O_DIRECT_NO_FSYNCが設定されます。
もしO_DIRECT_NO_FSYNCを設定できない場合、デフォルトのfsyncが設定されます。
各設定値の詳細はこちら
参考文献