寺田 太志朗 の記事一覧

バイナリ互換性がないMySQLバージョン間（5.6→8.0）のDB移行が必要となり、DBを停止して、mysqldumpによるデータ移行を計画しました。その際にDBのダウンタイムをなるべく短くするため、インポートにかかる時間をパラメータチューニングによって短くできないか検証しました。 注意点 本記事の内容は、筆者の個人的な見解や認識に基づいて作成しているため、正確性、信頼性、完全性、最新性を保証するものではありません。 本記事は、2023/04/25 時点の情報をもとに作成しております。最新の情報については、他の情報源と照らし合わせてご確認ください。 今回紹介するパラメータはインポート時に一時的に設定するパラメータと仮定しており、運用時は適さないパラメータもあります。インポートが完了したら運用に適したパラメータへの変更をお願いします。 前提条件 インポートするデータ スキーマ毎にmysqldumpコマンドでダンプした、計14GBのダンプファイルダンプファイルはデータのみ（テーブル定義等は前もってリストア済み）テーブルのデータ型にはtext型・blob型も含まれているすべてのテーブルはInnoDBエンジンを使用ダンプファイルに「UNIQUE_CHECKS」「FOREIGN_KEY_CHECKS」を無効化しインポートするための記載がある VM情報（インポート先の情報） OSOracle Linux Server release 8.6vCPU4コアメモリ16GBディスクSSD 150GBMySQLバージョンmysql  Ver 8.0.28 for Linux on x86_64 (MySQL Community Server - GPL)DB構成シングル構成 インポート検証 インポート時間に大きな影響を与えそうなパラメータを変更して、検証を行いました。 時間を短縮することが見込めるため、並列でインポートを行いました。2並列から6並列までのインポートを検証したところ、4並列以上にするとスキーマ毎のインポート時間が増加するため、並列数は4並列としました。（vCPU、「innodb_write_io_threads」を増やしても同様の結果となったため、ディスクIOの限界によるものだと思われます） これより、各パラメータ紹介です。結論をお急ぎの方はこちら 変更しておくパラメータ（初めから固定しておくパラメータ） innodb_buffer_pool_size 動的、デフォルト：128MB メモリ領域であるバッファープールの値。インポート短縮に関わらず、一般的にチューニングをする必要がある。メインメモリの50～75%でよくチューニングされているため、今回は75%の12GBを設定している。 /etc/my.cnf ファイルにて、変更可能。 [mysqld] innodb_buffer_pool_size=12G 公式マニュアル[innodb_buffer_pool_size] innodb_log_buffer_size 動的、デフォルト：16MB ディスク上のログファイルに書き込みを行う際のバッファーサイズの値。バイナリログ・REDOログともにディスク上に書き込んているため、これらを有効にしている場合は影響があると思われる。 /etc/my.cnf ファイルにて、変更可能。 [mysqld] innodb_log_buffer_size=128M 公式マニュアル[innodb_log_buffer_size] max_allowed_packet 動的、デフォルト：64MB 1パケットの最大サイズの値。データ型として、blob型を扱っているため、念のため引き上げておく。 /etc/my.cnf ファイルにて、変更可能。 [mysqld] max_allowed_packet=1G 公式マニュアル[max_allowed_packet] 検証に使用するパラメータ（検証にて変更するパラメータ） innodb_doublewrite 静的（DB再起動が必要）、デフォルト：ON 二重書込みバッファを有効化・無効化する変数。二重書込みバッファはクラッシュリカバリの際に有益であるが、今回はパフォーマンスを重視するため、無効化する。 /etc/my.cnf ファイルにて、「skip_innodb_doublewrite」を設定することで無効化できる。 [mysqld] skip_innodb_doublewrite 公式マニュアル[innodb_doublewrite] innodb_flush_log_at_trx_commit 動的、デフォルト：1 コミット操作に対する厳密なACID特性とパフォーマンスのバランスを制御する変数。今回は、パフォーマンスを重視するため、各トランザクションのコミット後にログが書き込まれ、1 秒に 1 回ディスクにフラッシュされる「2」に設定する。 /etc/my.cnf ファイルにて、変更可能。 [mysqld] innodb_flush_log_at_trx_commit=2 公式マニュアル[innodb_flush_log_at_trx_commit] innodb_flush_method 静的（DB再起動が必要）、デフォルト：fsync（windowsの場合はunbuffered） データファイル・ログファイルへデータをフラッシュする際の方法を指定する変数。今回は、インポート時間の短縮を目的としているため、「O_DIRECT_NO_FSYNC」に設定。「O_DIRECT」も試してみたが、結果に差異は生じなかった。 /etc/my.cnf ファイルにて、変更可能。 [mysqld] innodb_flush_method="O_DIRECT_NO_FSYNC" 公式マニュアル[innodb_flush_method] log_bin 静的（DB再起動が必要）、デフォルト：ON バイナリログファイルへの書き込みを有効化・無効化する変数。レプリケーションを張っているような環境だと有効化が必須、今回はレプリケーションの想定はしていないため、無効化する。 /etc/my.cnf ファイルにて、「disable_log_bin」を設定することで無効化できる。 [mysqld] disable_log_bin 公式マニュアル[17.1.6.4 バイナリロギングのオプションと変数] Innodb_redo_log_enabled(ステータス変数) 動的、デフォルト：ON REDOログファイルへの書き込みを有効化・無効化する変数。REDOファイルは基本クラッシュリカバリの際に使用されるため、今回のように新しくダンプファイルからインポートを行う際は無効化できる。（データの用意が出来て、DBを運用する際は有効化する） 下記コマンドで変更可能。 ALTER INSTANCE [ ENABLE / DISABLE ] INNODB REDO_LOG; mysql> SHOW STATUS LIKE 'Innodb_redo_log_enabled'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Innodb_redo_log_enabled | ON | +-------------------------+-------+ 1 row in set (0.01 sec) mysql> mysql> ALTER INSTANCE DISABLE INNODB REDO_LOG; Query OK, 0 rows affected (0.01 sec) mysql> mysql> SHOW STATUS LIKE 'Innodb_redo_log_enabled'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Innodb_redo_log_enabled | OFF | +-------------------------+-------+ 1 row in set (0.00 sec) mysql> mysql> ALTER INSTANCE ENABLE INNODB REDO_LOG; Query OK, 0 rows affected (0.00 sec) mysql> mysql> SHOW STATUS LIKE 'Innodb_redo_log_enabled'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Innodb_redo_log_enabled | ON | +-------------------------+-------+ 1 row in set (0.01 sec) 公式マニュアル[15.6.5 redo ログ] その他のパラメータ innodb_write_io_threads 静的（DB再起動が必要）、デフォルト：4 データの書き込み時に使用されるI/Oスレッドの数。高速なストレージを使用している場合は、増やすことで高速化が見込める。今回は変更してもインポート時間に影響は見られなかったため、除外。（デフォルトのまま） 公式マニュアル[innodb_write_io_threads] bulk_insert_buffer_size 動的、デフォルト：8MB バルクインサート時に使用するバッファーサイズ。MyISAMへのインサートの高速化が期待できる。今回のテーブルはすべてInnoDBのため、この変数は検証対象から除外。 公式マニュアル[bulk_insert_buffer_size] 検証結果 パラメータ変更前パラメータ変更後5時間3分19分 5時間以上かかっていたインポートを20分以内に完了するまでに短縮できました。 ▼パラメータの変更前 innodb_doublewrite ON innodb_flush_log_at_trx_commit 1 innodb_flush_method fsync log_bin ON Innodb_redo_log_enabled ON # 検証にて変更しない変数（初めから固定で設定しておく変数） innodb_buffer_pool_size 12884901888 # 12GB innodb_log_buffer_size 134217728 # 128MB max_allowed_packet 1073741824 # 1GB ▼パラメータの変更後（最終値） innodb_doublewrite OFF # REDOログ無効化してるため変更しなくてもよい innodb_flush_log_at_trx_commit 2 # REDOログ無効化してるため変更しなくてもよい innodb_flush_method O_DIRECT_NO_FSYNC log_bin OFF Innodb_redo_log_enabled OFF # 検証にて変更しない変数（初めから固定で設定しておく変数） innodb_buffer_pool_size 12884901888 # 12GB innodb_log_buffer_size 134217728 # 128MB max_allowed_packet 1073741824 # 1GB パラメータ変更の順序と時間短縮の推移 順序パラメータ変更前変更後所要時間1パラメータ変更なしーー5時間3分2Innodb_redo_log_enabledONOFF24分3innodb_flush_methodfsyncO_DIRECT_NO_FSYNC22分4log_binONOFF19分5innodb_flush_log_at_trx_commit1219分6innodb_doublewriteONOFF19分 結果からの考察 圧倒的に時間短縮に影響が大きかったのは、REDOログの無効化でした。これにより、インポート時間が元の1割ほどまで短縮されました。（公式マニュアルでも新しくデータインポートをする際には無効化が推奨されており、無効化手順まで丁寧に書いてあるのはそういうことなんだろうと思います。もうむしろREDOログを無効化するだけでいいと言ってしまえそうです） バイナリログの無効化も影響はありましたが、REDOログと比べるとそこまでの影響はありませんでした。 「innodb_flush_method」に関しても、インポート時間への影響はあったものの、きわめて微小な影響でした。（わざわざ変更しなくてもよいかなと思うほどです）ただし、REDOログを有効化した場合のインポート時間短縮への影響は大きかったためディスクへの書き込み処理の量に依存しているだけで、インポート時間短縮への影響度は高いと思われます。 「innodb_doublewrite」に関しては、REDOログを無効化している状態では、暗黙的に二重書き込みは回避されるため、インポート時間への影響は見られませんでした。 「innodb_flush_log_at_trx_commit」に関しても、ログへの書き込み時の挙動の変更のため、REDOログを無効化している状態では、インポート時間への影響は見られませんでした。 まとめ REDOログを無効化するだけで、大幅に時間短縮できました。 さらに時間短縮を行いたいのであれば、「バイナリログ無効化」「innodb_flush_methodのチューニング」を試してみるとよいと思います。 「innodb_doublewrite」「innodb_flush_log_at_trx_commit」のチューニングはREDOログを無効化している場合では、効果はみられませんでした。 注意点でもふれてはいますが、戻す必要のあるパラメータは忘れずに戻すようにしましょう。 あとがき REDOログとバイナリログでここまで大きく影響に差が出ると思っていませんでした。MySQLのインポート試験では、より大きなデータを使って、明確なデータ型を設定して、データ型による短縮時間の差異を検証すべきです。しかし、今回は移行時間を短縮することに焦点を当てて、ダンプファイルのインポート時間短縮の検証を行いました。あくまで一例として、参考になれば幸いです。