【MySQL】インポート時間短縮のためのDBパラメータチューニング / 開発者向けブログ・イベント

バイナリ互換性がないMySQLバージョン間（5.6→8.0）のDB移行が必要となり、DBを停止して、mysqldumpによるデータ移行を計画しました。その際にDBのダウンタイムをなるべく短くするため、インポートにかかる時間をパラメータチューニングによって短くできないか検証しました。

注意点

本記事の内容は、筆者の個人的な見解や認識に基づいて作成しているため、正確性、信頼性、完全性、最新性を保証するものではありません。

本記事は、2023/04/25 時点の情報をもとに作成しております。
最新の情報については、他の情報源と照らし合わせてご確認ください。

今回紹介するパラメータはインポート時に一時的に設定するパラメータと仮定しており、運用時は適さないパラメータもあります。
インポートが完了したら運用に適したパラメータへの変更をお願いします。

前提条件

インポートするデータ

スキーマ毎にmysqldumpコマンドでダンプした、計14GBのダンプファイル
ダンプファイルはデータのみ（テーブル定義等は前もってリストア済み）
テーブルのデータ型にはtext型・blob型も含まれている
すべてのテーブルはInnoDBエンジンを使用
ダンプファイルに「UNIQUE_CHECKS」「FOREIGN_KEY_CHECKS」を無効化しインポートするための記載がある

VM情報（インポート先の情報）

OS	Oracle Linux Server release 8.6
vCPU	4コア
メモリ	16GB
ディスク	SSD 150GB
MySQLバージョン	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分
2	Innodb_redo_log_enabled	ON	OFF	24分
3	innodb_flush_method	fsync	O_DIRECT _NO_FSYNC	22分
4	log_bin	ON	OFF	19分
5	innodb_flush_log _at_trx_commit	1	2	19分
6	innodb_doublewrite	ON	OFF	19分

結果からの考察

圧倒的に時間短縮に影響が大きかったのは、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のインポート試験では、より大きなデータを使って、明確なデータ型を設定して、データ型による短縮時間の差異を検証すべきです。しかし、今回は移行時間を短縮することに焦点を当てて、ダンプファイルのインポート時間短縮の検証を行いました。あくまで一例として、参考になれば幸いです。