tom__bo’s Blog

MySQL!! MySQL!! @tom__bo

トップ > MySQLのデータベース(schema)のコピーにかかる時間を比較してみた

MySQLのデータベース(schema)のコピーにかかる時間を比較してみた

あるMySQLインスタンスで検証のためにschema(database)をコピーしてほしい。というリクエストを受けたときに最速で対応する方法は何か検証してみました。

MySQLにschemaをコピーするコマンドはありません。 schemaをコピーする方法として例えば以下の5つがあります。同じMySQLインスタンス上でschemaをコピーするならどの方法が最速でしょうか？

  • INSERT INTO ... SELECT ...
  • mysqldumpしてrestore
  • mydumper/myloaderでdump&insert
  • xtrabackupしてrestore
  • Transporable Tablespaceでdump & import

アプリケーション開発者視点で実行しやすい方法から、DBAなどインフラエンジニアの権限(*1)がないと実行が難しい方法の順で並べてみました。

schema Aをコピーしてschema Bを作成した場合を想定して手順を書くと大体以下になります。
（本番環境でサービス中にこういった作業をすることを想定していないので、レプリケーションやファイルフォーマットなどをどう扱うかについては考慮していません）

INSERT INTO ... SELECT ...

新しいschemaでCREATE TABLE LIKE ...でtableを用意し、INSERT INTO ... SELECTでデータを入れていきます。

  1. CREATE DATABASE B
  2. CREATE TABLE ${tbl} LIKE A.${tbl}でAの全テーブルをschema　Bに作成
  3. INSSERT INTO B(col1, col2, ...) SELECT * FROM A

mysqldumpしてリストア

mysqldumpでbackupを作成し、元のテーブルをrenameして移動した上でrestoreします。
backupファイルのCREATE DATABASE A部分やUSE Aの部分をsed等で書き換えても良いですが、データ量が多いとRENAME TABLEしていったほうが早いです。

  1. CREATE DATABASE B
  2. CREATE TABLE ${tbl} LIKE A.${tbl}でAの全テーブルをschema　Bに作成
  3. mysqldump --databases A > bk.dumpでダンプ
  4. Aの全テーブルをBに移動 (RENAME TABLE A.${tbl} TO B.${tbl})
  5. ダンプしたファイルをリストアmysql < bk.dump

mydumper/myloader

mysqldumpとやりたいことは同じですが、mydumper/myloaderを利用することで並列でdump, loadができます。
mydumper/myloaderのそれぞれのコマンドで--threadsを指定して並列度を調整して実行します。

  1. CREATE DATABASE B
  2. mydumper --database A --outputdir ${mydir}でダンプ
  3. myloader -d ${mydir}でロード

xtrabackup

xtrabackupでスキーマ指定のbackupを取り、restoreします。
schema指定やtable指定のbackup, restore方法は本家のブログが詳しいです。

  1. CREATE DATABASE B
  2. CREATE TABLE ${tbl} LIKE A.${tbl}でAの全テーブルをschema　Bに作成
  3. ALTER TABLE B.${tbl} DISCARD TABLESPACEでBの全テーブルスペースをdiscard
  4. xtrabackup --backup --dataabses=Aでバックアップ作成
  5. xtrabackup --prepare --exportでプリペア(バックアップ中の更新を適用)
  6. プリペアで作成されたibdファイル, cfgファイルをBのディレクトリにコピー
  7. ALTER TABLE B.${tbl} IMPORT TABLESPACE でインポート

Transporable Tablespaceでdump & import

Transporable TablespaceについてはMySQLのリファレンスマニュアルの"15.6.1.3 InnoDB テーブルのインポート"が詳しいです。

  1. CREATE DATABASE B
  2. CREATE TABLE ${tbl} LIKE A.${tbl}でAの全テーブルをschema　Bに作成
  3. ALTER TABLE B.${tbl} DISCARD TABLESPACEでBの全テーブルスペースをdiscard
  4. FLUSH TABLES ${tbls} FOR EXPORTでAの全テーブルをフラッシュ
  5. フラッシュで作成されたcfgファイル, ibdファイルをBのディレクトリにコピー
  6. ALTER TABLE B.${tbl} IMPORT TABLESPACE でインポート

実験

上記の5つの方法をシェルスクリプトにし、100テーブル合計約150GBの環境でどれくらいの時間で完了するかを試してみた結果、下の図のようになりました。 この図では一番時間のかかったmydumpを利用した方法にかかった時間を100として、それぞれの方法で完了までにかかった時間を示しています。

f:id:tom__bo:20211204004218p:plain
fig1

mysqldumpの方法と比較して、xtrabackupの方法で約1/30, Transportable Tablespaceで1/35程度の時間で完了しました。

この実験ではinnodb_buffer_pool_dump_at_shutdown及びinnodb_buffer_pool_load_at_startupをOFFにして、各方法の実験前にMySQLを再起動したことで特に上記の方法に有利になった可能性はありますが、これらがONだったとしてもそれほど差はなかったと思います。

状況に応じて上記の方法のどれが最適かは異なると思いますが、同じインスタンス中ではコピーする方法によってかなり差があることがわかりました。 IOがどう発生するかを考えるとだいたい予想はつきますが、ここまで差が出るとは思わなかったのでブログにしました。(binlog = off, innodb_flush_log_at_trx_commit = 2です)

各方法でとったスクリプトについては後日別の記事にしたいと思います。(もう眠い)

(*1) サーバへのアクセス権限やMySQLアカウントの権限

.