MySQL Shell でデータエクスポートと同時並行でインポートを実行する

2022年6月10日

この記事は最終更新から2年以上経過しています。内容が古くなっている可能性があります。

はじめに

MySQL 8.0 へのバージョンアップ対応などで、データ移行を実施する際、MySQL Shell インスタンスダンプユーティリティとダンプロードユーティリティを用いるのが非常に便利です。

今回の記事では、ダンプロードユーティリティの waitDumpTimeout オプションを活用した 「データエクスポートとインポートの同時実行」 方式をご紹介します。

あまり知られているオプションではありませんが、データ移行時間が短縮できる機能となります。

ダンプロードユーティリティの `waitDumpTimeout` オプションについて

MySQL Shell の公式リファレンスマニュアルからの抜粋を掲載します。

MySQL :: MySQL Shell 8.0 :: 8.6 ダンプロードユーティリティ

waitDumpTimeout オプションを使用すると、まだ作成中のダンプを適用できます。テーブルは使用可能になるとロードされ、新しいデータがダンプの場所に到着しなくなった後、ユーティリティは指定された秒数待機します。タイムアウトが経過すると、ユーティリティはダンプが完了したとみなし、インポートを停止します。

waitDumpTimeout: int
このオプションを設定すると、ダンプの場所にアップロードされたすべてのデータチャンクが処理された後、ユーティリティがそれ以降のデータを待機するタイムアウト (秒) を指定することで、同時ロードがアクティブ化されます。これにより、作成中のダンプをユーティリティでインポートできます。データは使用可能になると処理され、ダンプの場所にデータが表示されずにタイムアウトを超えるとインポートは停止します。デフォルト設定の 0 は、アップロードされたすべてのデータチャンクが処理され、それ以上のデータを待機しない場合に、ユーティリティがダンプを完了としてマークすることを意味します。

一般的には、移行対象のデータをエクスポートしたのち、インポートを実行することになりますが、
この機能を使うと

エクスポート実行中(dumpInstance()など)に、別のプロセスで waitDumpTimeout オプションを有効に(タイムアウト値を設定)した loadDump() を実行する
ダンプ出力先ディレクトリのデータエクスポート状況を監視しつつ、インポート可能なデータが見つかるとすぐにインポートを実行する

という動きになります。

インポート可能なデータが見つかるまでは、waitDumpTimeout で指定した秒数待機(タイムアウトするとインポートプロセスは終了)します。

この仕組みにより、データエクスポートを開始してから同時にインポートも並行して行えるため、単純に移行作業時間の短縮が見込めます。

移行元データベースの制約や移行作業時間・スケジュールの確保のむずかしさなど、なかなか満足にデータ移行に時間を割けることは少ない場合もあると思います。

当然ながら、少しでもデータのエクスポート・インポート時間を短縮したい、データ移行の効率的な方式を確立したい、という要望も多いと考えます。

弊社ブログ記事では、これまでに MySQL Shell のインスタンス/スキーマダンプユーティリティやダンプロードユーティリティに関する情報を多数公開しています。

MySQL Shell のユーティリティはエクスポート・インポートともに並列実行を基本としているので、並列度の調整（およびCPU数の増加）で処理を高速化する

MySQL Shell でバックアップとリストアをパラレルで実行する
 MySQL Shell の Parallel Table Import Utility を使ってみる
エクスポート時、リモートサーバとのネットワーク流量を制御する(許容範囲で転送量を増加させる)

MySQL Shell の dumpInstance() 帯域制限オプション活用法
必要な移行対象データのみを処理する

MySQL Shell 8.0.28新機能オブジェクトのフィルタリング機能の紹介

そのほか、インポートを手軽に高速化する方法としては、以下のようなインポート時のみの一時的な設定変更なども挙げられます。

INSERT 時のディスクI/Oを減らすためのパラメータ変更

sync_binlog = 0 innodb_flush_log_at_trx_commit = 0 or 2 skip_innodb_doublewrite

1
2
3

sync_binlog = 0
innodb_flush_log_at_trx_commit = 0 or 2
skip_innodb_doublewrite
- ※通常のINSERT高速化のひとつに、autocommit = OFF とする方法もあります。ただし、autocommit = OFF にして MySQL Shell の util.loadDump を実行すると対象のデータが全てロードしきれない(今回であれば500万行のところ100～400万行しかロードされない)という事象が発生することが今回の検証で確認されました。
  
  恐らく Bug であるため、現時点では util.loadDump 実行時は autocommit = OFF は設定しないようにしてください。
バイナリログ出力無効化(インポート後に不要な場合)
- 8.0 の場合は disable-log-bin or skip-log-bin
- MySQL Shell loadDump() の skipBinlog オプションを有効にすることで、インポート中のバイナリロギングを行わないことも可能です。
InnoDB REDO ログ出力を無効にする (8.0.21以降で可能)

ALTER INSTANCE DISABLE INNODB REDO_LOG;

1

ALTER INSTANCE DISABLE INNODB REDO_LOG;

※本コマンドを実行することで REDOログ書込みとダブルライトバッファが無効になります。(この場合 skip_innodb_doublewrite は不要)

これらの考慮に加え、今回紹介する waitDumpTimeout オプションを用いたインポートによって、大幅なデータ移行時間の短縮が期待できます。

実機検証

OCI のコンピュートインスタンスにインストールしていた MySQL 5.7/8.0 を使って、動作確認を行ってみました。

注意・前提

今回は簡易的な動作確認のため、互換性は度外視したうえで 5.7 から 8.0 へ直接データを移行しました。(ignoreVersion: true)
ただし、MySQL Shell では、メジャーバージョン間のインポートはサポートされておらず、通常エラーとなります。(ignoreVersion: false)

5.7 から 8.0 へのアップグレードに際しては、かならず互換性・非互換性のチェックや動作確認、定義の見直し有無などを入念に行ったうえで、たとえば「5.7 から 5.7 にデータ移行し、5.7 から 8.0 へアップグレードする」などのアップグレードパスを確立するようにしてください。

MySQL 8.0 へのアップグレードについては以下のリファレンスマニュアルをご確認ください。

MySQL :: MySQL 8.0 リファレンスマニュアル :: 2.11 MySQL のアップグレード

また、MySQL Shell のアップグレードチェッカユーティリティもぜひご活用ください。

MySQL :: MySQL Shell 8.0 :: 8.1 アップグレードチェッカユーティリティ

環境情報

移行元DB

MySQL 5.7.23 Community Edtion
- シェイプ：VM.Standard.E4.Flex
- OCPU：2
- メモリー(GB):8
datadir: ブロックボリューム(iscsi)
- サイズ：250GB
- VPU:10
sysbench で作成した 100GB 程度のデータをインポート済みです。
移行先DBから MySQL Shell で接続するユーザを作成しておきます。

mysql> CREATE USER mysqlsh_user@<移行先DB> identified by '*********'; mysql> GRANT ALL ON *.* TO mysqlsh_user@<移行先DB>;

1
2

mysql> CREATE USER mysqlsh_user@<移行先DB> identified by '*********';
mysql> GRANT ALL ON *.* TO mysqlsh_user@<移行先DB>;

移行先DB

MySQL 8.0.28 Community Edtion
- シェイプ：VM.Standard.E4.Flex
- OCPU：4
- メモリー(GB):64
datadir: ブロックボリューム(iscsi)
- サイズ：400GB
- VPU:10
my.cnf には以下のパラメータを追加しました。

innodb_buffer_pool_size=10G disable-log-bin

1
2

innodb_buffer_pool_size=10G
disable-log-bin

データエクスポートの実行

移行先DB(8.0)にて、エクスポートデータの出力ディレクトリを作成しておきます。

例)

# mkdir -p /var/lib/mysql/src_dump

1	# mkdir -p /var/lib/mysql/src_dump

MySQL Shell で移行元DBへログインし、dumpInstance() を実行します。

# mysqlsh mysql://mysqlsh_user@10.0.0.45

 JS > util.dumpInstance('/var/lib/mysql/src_dump',
    {
          defaultCharacterSet: 'utf8'
        , consistent: true
        , users: true
        , chunking: true
        , threads: 8
        , showProgress: true
    }
)

# mysqlsh mysql://mysqlsh_user@10.0.0.45

JS > util.dumpInstance('/var/lib/mysql/src_dump',

{

defaultCharacterSet: 'utf8'

, consistent: true

, users: true

, chunking: true

, threads: 8

, showProgress: true

}

)

データインポートの実行

エクスポートとは別のプロセスで、移行先DB(8.0)にログインします。

# mysqlsh mysql://root@localhost

1	# mysqlsh mysql://root@localhost

インポート高速化のためのいくつかのパラメータ変更を行います。

 JS > \sql
 SQL > SET GLOBAL local_infile = ON;
 SQL > SET GLOBAL innodb_flush_log_at_trx_commit = 2;
 SQL > ALTER INSTANCE DISABLE INNODB REDO_LOG;

JS > \sql

SQL > SET GLOBAL local_infile = ON;

SQL > SET GLOBAL innodb_flush_log_at_trx_commit = 2;

SQL > ALTER INSTANCE DISABLE INNODB REDO_LOG;

loadDump()を実行します。
waitDumpTimeout にはタイムアウトが発生しないように 36000秒(10時間)を設定しました。
また、DDL・ユーザ情報・データを一度にインポートし(loadDdl,loadUsers,loadData)、インポート後に ANALYZE TABLE を実行するオプションも付与しました。

 SQL > \js
 JS > util.loadDump('/var/lib/mysql/src_dump',
    {
          loadDdl: true
        , loadUsers: true
        , loadData: true
        , ignoreVersion: true
        , analyzeTables: 'on'
        , threads: 8
        , waitDumpTimeout: 36000
        , showProgress: true
    }
)

SQL > \js

JS > util.loadDump('/var/lib/mysql/src_dump',

{

loadDdl: true

, loadUsers: true

, loadData: true

, ignoreVersion: true

, analyzeTables: 'on'

, threads: 8

, waitDumpTimeout: 36000

, showProgress: true

}

)

実行中は、waitDumpTimeout オプション有効時に特有の以下のメッセージが出力されるのが確認できます。

Loading DDL, Data and Users from '/var/lib/mysql/src_dump' using 8 threads.
Opening dump...
NOTE: Dump is still ongoing, data will be loaded as it becomes available.
    ...
    ...
Dump still in progress, 856.27 MB ready (compr.) - 7 thds loading / ?% (1.67 GB / ?), 76.32 MB/s, 3 / 100 tables done

Loading DDL, Data and Users from '/var/lib/mysql/src_dump' using 8 threads.

Opening dump...

NOTE: Dump is still ongoing, data will be loaded as it becomes available.

...

Dump still in progress, 856.27 MB ready (compr.) - 7 thds loading / ?% (1.67 GB / ?), 76.32 MB/s, 3 / 100 tables done

処理完了結果

データエクスポート

所要時間は 15分 でした。
流量制御(MaxRate)をかけていないので、非常に高いスループットが出ています。

104% (500.00M rows / ~480.49M rows), 526.09K rows/s, 99.84 MB/s uncompressed, 44.90 MB/s compressed
Dump duration: 00:15:02s
Total duration: 00:15:02s
Schemas dumped: 1
Tables dumped: 100
Uncompressed data size: 97.89 GB
Compressed data size: 44.09 GB
Compression ratio: 2.2
Rows written: 500000000
Bytes written: 44.09 GB
Average uncompressed throughput: 108.49 MB/s
Average compressed throughput: 48.86 MB/s

104% (500.00M rows / ~480.49M rows), 526.09K rows/s, 99.84 MB/s uncompressed, 44.90 MB/s compressed

Dump duration: 00:15:02s

Total duration: 00:15:02s

Schemas dumped: 1

Tables dumped: 100

Uncompressed data size: 97.89 GB

Compressed data size: 44.09 GB

Compression ratio: 2.2

Rows written: 500000000

Bytes written: 44.09 GB

Average uncompressed throughput: 108.49 MB/s

Average compressed throughput: 48.86 MB/s

データインポート

所要時間は 29分弱 でした。

100% (97.89 GB / 97.89 GB), 49.81 MB/s, 100 / 100 tables done
Recreating indexes - done
Analyzing tables - done
Executing user accounts SQL...
NOTE: Skipping CREATE/ALTER USER statements for user 'root'@'localhost'
NOTE: Skipping GRANT statements for user 'root'@'localhost'
Executing common postamble SQL
NOTE: The redo log is currently disabled, which causes MySQL to not be crash safe! Do not forget to enable it again before putting this instance in production.
1800 chunks (500.00M rows, 97.89 GB) for 100 tables in 1 schemas were loaded in 29 min 9 sec (avg throughput 55.95 MB/s)

100% (97.89 GB / 97.89 GB), 49.81 MB/s, 100 / 100 tables done

Recreating indexes - done

Analyzing tables - done

Executing user accounts SQL...

NOTE: Skipping CREATE/ALTER USER statements for user 'root'@'localhost'

NOTE: Skipping GRANT statements for user 'root'@'localhost'

Executing common postamble SQL

NOTE: The redo log is currently disabled, which causes MySQL to not be crash safe! Do not forget to enable it again before putting this instance in production.

1800 chunks (500.00M rows, 97.89 GB) for 100 tables in 1 schemas were loaded in 29 min 9 sec (avg throughput 55.95 MB/s)

エクスポート開始からインポート完了までの所要時間チャートにまとめました。

同時並行で実行した場合、インポートの開始タイミングにもよりますが、エクスポート実行直後にインポートプロセスを開始したのであれば、総所要時間はインポート実行時間にほぼ等しくなります。

データエクスポート後にインポートを実行するパターンも確認した結果が以下となります。

この場合、インポート処理はエクスポートとリソース競合することなく実行されるため、並列実行時よりも処理時間は短くなります。

※メモリリソース、特に innodb_buffer_pool_size が少なくインポート時に不足する環境においては、インポート処理単体実行時にディスクI/O(書き込み待ち)が発生し、結果的に並列実行時よりも処理時間が短くなる場合もあります。

この検証環境の結果では、エクスポート・インポート同時並行のほうが、直列実行よりも7分程度、全体の作業時間としては短縮されるということが分かりました。

ただこれだけでは、効果のほどはイマイチのように受け取られるかもしれませんが、実際には実行状況(環境や対象データのサイズ)で大きく変わってきます。

実際に過去、某商用環境のデータインポートを行った際、概ね4時間半強も差が生じました。

CPU:32*2コア、メモリ:96GB
移行データサイズ:1.19TB

この環境では、移行元・先間のDBサーバのネットワークパフォーマンスの影響(18.24 MB/s)や、データ量も多いためエクスポートに18時間掛かるという実情がありました。

このときのデータ移行では、新DBへの切替え日よりも数日前にレプリカDBからデータを事前移行しておく方式で行われました。

時間の制約はあまり厳しくなかったとはいえ、更新が非常に多いDBだったため、早めにデータ移行を完了させてレプリケーションを再開・同期追い付きを本番切替え日までに済ませておく必要がありました。

同時並行にすることでインポート処理単体の4時間半強の分が短縮された結果となります。

使用上の注意点

waitDumpTimeout オプションを用いた loadDump() を実行する際の注意点をいくつか書き留めておきたいと思います。

処理時間に影響を与える要素

前述の通り、サーバリソース(CPU,メモリ)、ディスクI/O の性能によっては、当然ながら処理時間が変わってきます。

もしデータ移行先が新設DBサーバであれば、そのリソースを最大限データ移行に使用することが可能ではないかと考えます。(他の処理を同時実行することが無い前提)

一般的にはDBサーバは高いスペックで用意されることになると思いますので、その場合は潤沢なリソースを使って、許容範囲内で高並列度で実行しても問題はないと想定できます。

また、今回の検証では sysbench のテストテーブルのため、テーブル定義やデータ、行数が均一になっていますが、実際の環境では大小混在していると思いますので、実データでの検証は必要です。

DDL, ユーザ情報に変更を加えてからインポートしたい場合

デフォルトでは、エクスポートデータに DDL がある場合、DDLがロードされたあと、データがインポートされます。

また、ユーザ情報は、デフォルトではインポートされません(loadUsers: false)

もしデータ移行の際に、何らかの理由で既存の DDL やユーザ情報を変更してからデータを移行したい場合、以下のステップを踏む必要があります。

ダンプされた DDL を修正する
{ loadDdl: true, loadData: false, loadUsers: true } のオプションで loadDump() を実行し、DDL とユーザ情報のみインポートする
{ loadDdl: false, loadData: true, loadUsers: false, waitDumpTimeout: X } のオプションで loadDump() を実行し、データのみインポートする

※先に DDL とユーザ情報のみインポートしたあと、ALTER コマンドで変更を行うという順序でも可です。

`waitDumpTimeout` オプションの `loadDump()` プロセスは、インスタンス/スキーマダンプユーティリティの実行後に実行する必要がある

ダンプユーティリティがエクスポート時に生成する @.json が無いと監視できないため、以下のエラーとなってしまいます。

Loading DDL, Data and Users from '/var/lib/mysql/src_dump' using 8 threads.
Opening dump...
Util.loadDump: Cannot open file '/var/lib/mysql/src_dump/@.json': No such file or directory (RuntimeError)

Loading DDL, Data and Users from '/var/lib/mysql/src_dump' using 8 threads.

Opening dump...

Util.loadDump: Cannot open file '/var/lib/mysql/src_dump/@.json': No such file or directory (RuntimeError)

まとめ

MySQL Shell ダンプロードユーティリティの waitDumpTimeout オプションを使うことで、基本的にはエクスポート・インポートを直列で実行するよりも全体作業時間の短縮が期待できます。

実行状況によっては直列実行と同等時間となる可能性もありますが、直列実行より長くなることはないと言えます。

よって、インポートを単発で行った場合とスループットに大きな差が無い限りは並列実行したほうがよい、と考えます。

実際に使用する場合には、事前検証を必ず行うようにしていただきたいですが、データ移行の際にはぜひご活用ください。

よかったらシェアしてね！

URLをコピーしました！