先月リリースされた MySQL Shell 8.0.21 にバックアップのスレッドを並列化させてパラレルで実行する機能と、そのバックアップを同じくパラレルでインポートするユーティリティが追加されました。
公式リファレンスは以下になります。
上記のリファレンスに記載されていますが、バックアップ及びリストアをパラレルで実行できるだけでなく、バックアップの保存先として、Oracle Cloud の Object Storage を指定できるという画期的な機能も持っています。
今回は、上記の機能について簡単に確認してみたいと思います。
（MySQL Shellで、1つのテーブルデータファイルをパラレルでインポートする Parallel Table Import Utility についても過去のブログで取り上げておりますので、まだ見られていない方は、是非、こちらもご一読下さい。）
Instance Dump Utility and Schema Dump Utility
バックアップには、以下の２つのユーティリティがあります。
・util.dumpSchemas(schemas, outputUrl[, options])
特定のデータベースを対象にバックアップを取得したい場合は util.dumpSchemas() 、対象データベースを限定しない場合は util.dumpInstance() を実行します。
util.dumpSchemas() の場合は、最初の引数に対象のデータベースを配列で指定するという事以外、オプション指定等の違いはなさそうです。
公式サイトからダウンロードできる world データベースを使用して確認してみます。
|
1 2 3 4 5 6 7 8 9 |
mysql> SHOW TABLES FROM world; +-----------------+ | Tables_in_world | +-----------------+ | city | | country | | countrylanguage | +-----------------+ 3 rows in set (0.01 sec) |
まずは MySQL Shell でログインし、バックアップ前に dryRun オプションを有効にして、ダンプされる対象を確認します。
util.dumpSchemas() を使用し、バックアップ対象を world データベース、バックアップディレクトリに /backup を指定しています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
MySQL localhost:33060+ ssl JS > util.dumpSchemas(["world"], "/backup", {dryRun: true}) Acquiring global read lock All transactions have been started Locking instance for backup Global read lock has been released Writing global DDL files Preparing data dump for table `world`.`city` Writing DDL for schema `world` Data dump for table `world`.`city` will be chunked using column `ID` Preparing data dump for table `world`.`country` Data dump for table `world`.`country` will be chunked using column `Code` Preparing data dump for table `world`.`countrylanguage` Writing DDL for table `world`.`city` Data dump for table `world`.`countrylanguage` will be chunked using column `CountryCode` Writing DDL for table `world`.`country` Writing DDL for table `world`.`countrylanguage` MySQL localhost:33060+ ssl JS > |
- 指定するバックアップディレクトリは事前に作成する必要はありませんが、指定のディレクトリの親ディレクトリは、バックアップ前に存在する必要があります。
- バックアップディレクトリ及び親ディレクトリには、MySQL Shellへログインした際のOSユーザでの書き込み権限が必要です。
world データベースが対象となっている事を確認したので、実際にバックアップを取得します。
実行スレッド数は、threads オプションで指定でき、デフォルトでは 4 に設定されています。
スレッド数を4としたい場合には、threadsオプションは省略可能です。（各オプションについては、リファレンスをご参照下さい。）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
MySQL localhost:33060+ ssl JS > util.dumpSchemas(["world"], "/backup") Acquiring global read lock All transactions have been started Locking instance for backup Global read lock has been released Writing global DDL files Preparing data dump for table `world`.`city` Writing DDL for schema `world` Writing DDL for table `world`.`city` Data dump for table `world`.`city` will be chunked using column `ID` Writing DDL for table `world`.`country` Preparing data dump for table `world`.`country` Writing DDL for table `world`.`countrylanguage` Data dump for table `world`.`country` will be chunked using column `Code` Preparing data dump for table `world`.`countrylanguage` Data dump for table `world`.`countrylanguage` will be chunked using column `CountryCode` Running data dump using 4 threads. NOTE: Progress information uses estimated values and may not be accurate. Data dump for table `world`.`city` will be written to 1 file Data dump for table `world`.`country` will be written to 1 file Data dump for table `world`.`countrylanguage` will be written to 1 file 1 thds dumping - 100% (5.30K rows / ~5.27K rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed Duration: 00:00:00s Schemas dumped: 1 Tables dumped: 3 Uncompressed data size: 194.62 KB Compressed data size: 62.42 KB Compression ratio: 3.1 Rows written: 5302 Bytes written: 62.42 KB Average uncompressed throughput: 194.62 KB/s Average compressed throughput: 62.42 KB/s |
実行後、バックアップディレクトリを確認してみると、以下のようになっています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
# ls -l /backup total 156 -rw-r----- 1 root root 216 Jul 30 11:21 @.done.json -rw-r----- 1 root root 467 Jul 30 11:21 @.json -rw-r----- 1 root root 240 Jul 30 11:21 @.post.sql -rw-r----- 1 root root 240 Jul 30 11:21 @.sql -rw-r----- 1 root root 68686 Jul 30 11:21 world@city@@0.tsv.zst -rw-r----- 1 root root 72 Jul 30 11:21 world@city@@0.tsv.zst.idx -rw-r----- 1 root root 626 Jul 30 11:21 world@city.json -rw-r----- 1 root root 958 Jul 30 11:21 world@city.sql -rw-r----- 1 root root 14324 Jul 30 11:21 world@country@@0.tsv.zst -rw-r----- 1 root root 8 Jul 30 11:21 world@country@@0.tsv.zst.idx -rw-r----- 1 root root 878 Jul 30 11:21 world@country.json -rw-r----- 1 root root 8693 Jul 30 11:21 world@countrylanguage@@0.tsv.zst -rw-r----- 1 root root 16 Jul 30 11:21 world@countrylanguage@@0.tsv.zst.idx -rw-r----- 1 root root 634 Jul 30 11:21 world@countrylanguage.json -rw-r----- 1 root root 989 Jul 30 11:21 world@countrylanguage.sql -rw-r----- 1 root root 1320 Jul 30 11:21 world@country.sql -rw-r----- 1 root root 381 Jul 30 11:21 world.json -rw-r----- 1 root root 555 Jul 30 11:21 world.sql |
各ファイルについてリファレンスには明記されておりませんが、主要なファイルは以下のような内容となっております。
| ファイル名 | 概要 |
|---|---|
| データベース名.sql | データベースのDDL |
| データベース名.json | 対象データベースのバックアップ対象に関する情報 |
| データベース名@テーブル名.json | テーブルのバックアップ実行に関する情報 |
| データベース名@テーブル名.sql | テーブルのDDL |
| データベース名@テーブル名@@連番.tsv.zst | データをTSV化して圧縮したファイル(※) |
※デフォルトで、データ内容のファイルは Zstandard で圧縮されるようになっており、compression オプションで gzip 形式もしくは圧縮なしとする事も可能です。
※同一テーブルのデータは、bytesPerChunk オプションで指定したサイズ（デフォルト32M）でファイルが分割され、連番が振られるようです。
Dump Loading Utility
今度は、取得したバックアップを util.loadDump() を使用してインポートしてみます。
（util.loadDump() を実行するには、local_infileシステム変数をONにする必要があります。）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
MySQL localhost:33060+ ssl JS > \sql Switching to SQL mode... Commands end with ; MySQL localhost:33060+ ssl SQL > drop database world; Query OK, 3 rows affected (0.0884 sec) MySQL localhost:33060+ ssl SQL > \js Switching to JavaScript mode... MySQL localhost:33060+ ssl JS > util.loadDump("/backup") Loading DDL and Data from '/backup' using 4 threads. Target is MySQL 8.0.21. Dump was produced from MySQL 8.0.21 Checking for pre-existing objects... Executing common preamble SQL Executing DDL script for schema `world` Executing DDL script for `world`.`countrylanguage` Executing DDL script for `world`.`country` Executing DDL script for `world`.`city` [Worker002] world@country@@0.tsv: Records: 239 Deleted: 0 Skipped: 0 Warnings: 0 [Worker003] world@countrylanguage@@0.tsv: Records: 984 Deleted: 0 Skipped: 0 Warnings: 0 [Worker000] world@city@@0.tsv: Records: 4079 Deleted: 0 Skipped: 0 Warnings: 0 Executing common postamble SQL 3 chunks (5.30K rows, 194.62 KB) for 3 tables in 1 schemas were loaded in 1 sec (avg throughput 194.62 KB/s) 0 warnings were reported during the load. |
インポートにも実行スレッド数を、threads オプションで指定できます。（こちらもデフォルトは 4 です。）
また、バイナリログに出力する必要がない場合には、skipBinlog オプションで、バイナリログに出力しないようにしてインポート時間の短縮も見込めそうです。
Oracle Cloud の Object Storage にバックアップ
今度は Oracle Cloud の Object Storage にバックアップを保存し、そのバックアップからインポートしてみます。
Object Storage のバケットは事前に作成しておく必要があります。
また、Oracle Cloudのコマンドライン・インタフェース(CLI)を使用できるように、こちらを参考に事前にセットアップをしておきます。
util.dumpSchemas() の第2引数には、ローカル保存の際には保存ディレクトリを指定しましたが、今度は Object Storage のバケット内での接頭辞文字列を指定します。
osBucketName オプションに Object Storage のバケット名、osNamespace オプションには、Object Storage のネームスペースを指定します。
（Object Storage のネームスペースは、OCIコンソール画面の「管理」-「テナンシ詳細」もしくは、Object Storage のバケット詳細画面で確認できます。）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
MySQL localhost:33060+ ssl JS > util.dumpSchemas(["world"], "worlddump", {"osBucketName": "blog-test", "osNamespace": "xxxxx"}) Acquiring global read lock All transactions have been started Locking instance for backup Global read lock has been released Writing global DDL files Preparing data dump for table `world`.`city` Writing DDL for schema `world` Writing DDL for table `world`.`city` Writing DDL for table `world`.`country` Writing DDL for table `world`.`countrylanguage` Data dump for table `world`.`city` will be chunked using column `ID` Preparing data dump for table `world`.`country` Data dump for table `world`.`country` will be chunked using column `Code` Preparing data dump for table `world`.`countrylanguage` Data dump for table `world`.`countrylanguage` will be chunked using column `CountryCode` Running data dump using 4 threads. NOTE: Progress information uses estimated values and may not be accurate. Data dump for table `world`.`country` will be written to 1 file Data dump for table `world`.`countrylanguage` will be written to 1 file Data dump for table `world`.`city` will be written to 1 file 3 thds dumping - 100% (5.30K rows / ~5.27K rows), 1.40K rows/s, 49.65 KB/s uncompressed, 0.00 B/s compressed Duration: 00:00:01s Schemas dumped: 1 Tables dumped: 3 Uncompressed data size: 194.62 KB Compressed data size: 62.42 KB Compression ratio: 3.1 Rows written: 5302 Bytes written: 62.42 KB Average uncompressed throughput: 109.95 KB/s Average compressed throughput: 35.27 KB/s |
バックアップ実行後、OCIコンソール画面で確認すると以下のように指定バケットに保存されている事が確認できます。
インポートする際には、ダンプと同じように Object Storage のバケット内での接頭辞文字列、バケット名、ネームスペースを指定します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
MySQL localhost:33060+ ssl JS > \sql Switching to SQL mode... Commands end with ; MySQL localhost:33060+ ssl SQL > drop database world; Query OK, 3 rows affected (0.0884 sec) MySQL localhost:33060+ ssl SQL > \js Switching to JavaScript mode... MySQL localhost:33060+ ssl JS > util.loadDump("worlddump", {osBucketName: "blog-test", osNamespace: "xxxxx"}) Loading DDL and Data from OCI ObjectStorage bucket=blog-test, prefix='worlddump' using 4 threads. Target is MySQL 8.0.21. Dump was produced from MySQL 8.0.21 Checking for pre-existing objects... Executing common preamble SQL Executing DDL script for schema `world` Executing DDL script for `world`.`countrylanguage` Executing DDL script for `world`.`country` Executing DDL script for `world`.`city` [Worker001] world@country@@0.tsv.zst: Records: 239 Deleted: 0 Skipped: 0 Warnings: 0 [Worker000] world@countrylanguage@@0.tsv.zst: Records: 984 Deleted: 0 Skipped: 0 Warnings: 0 [Worker003] world@city@@0.tsv.zst: Records: 4079 Deleted: 0 Skipped: 0 Warnings: 0 Executing common postamble SQL 3 chunks (5.30K rows, 194.62 KB) for 3 tables in 1 schemas were loaded in 3 sec (avg throughput 64.87 KB/s) 0 warnings were reported during the load. |
CLIさえ使用できるようにしておけば、すごくお手軽に実行できるのが嬉しいですね。
mysqldump との時間比較
バックアップ、インポートをパラレルで実行する事により、mysqldump を使用した場合と、バックアップ及びインポート時間がどの程度異なるか確認してみたいと思います。
検証環境
Oracle Cloud の Compute インスタンスを使用し、ローカルでのバックアップ、インポートを実行する事を前提とします。
Compute インスタンスには、VM.Standard2.4 のシェイプを利用し、ディスクは性能がなるべく頭打ちにならないよう 500GB のHigher Performance の Block Volumeを使用します。
(Block Volume はサイズによって性能が変わります。)
- Compute インスタンス情報
| 項目 | 項目値 |
|---|---|
| OS | Oracle Linux 7.8 |
| CPU | 4 OCPU （≒8 vCPU） |
| RAM | 60 GB |
- MySQLサーバ
検証対象のMySQLサーバは MySQL Server 8.0.21 とします。
デフォルト設定から以下の設定のみ変更します。
| パラメータ名 | 設定値 |
|---|---|
| innodb_buffer_pool_size | 20G |
| innodb_log_file_size | 1G |
| innodb_log_buffer_size | 64M |
| innodb_flush_method | O_DIRECT |
| innodb_io_capacity | 7000 |
| innodb_io_capacity_max | 8000 |
| innodb_lru_scan_depth | 3000 |
| local_infile | ON |
- 検証条件
本ブログでもおなじみの sysbench を使用して「sysbench」データベースに 100 テーブル、各テーブルレコード数 500,000 件のデータを準備しました。
対象データベースのディレクトリサイズは、以下のようになっています。
|
1 2 |
# du -h /var/lib/mysql/sysbench 13G /var/lib/mysql/sysbench |
mysqldump でのバックアップ取得とインポートは以下のコマンドで実施します。
|
1 2 |
# time mysqldump -u root -p sysbench > <ダンプファイル> # time mysql -u root -p sysbench < <ダンプファイル> |
MySQL Shell での実行は、以下のコマンドで実施します。
(バックアップ、インポートともにデフォルトの4スレッド、バックアップ時のチャンクサイズは128M)
|
1 2 |
MySQL Shell> util.dumpSchemas(["sysbench"], "/backup", {threads: 4, bytesPerChunk: "128M"}) MySQL Shell> util.loadDump("/backup", {threads: 4}) |
検証結果
検証結果は以下のようになりました。
- バックアップ
| Backup | Time |
|---|---|
| mysqldump | 2分12秒 |
| util.dumpSchemas() | 1分3秒 |
- インポート
| Import | Time |
|---|---|
| mysqldumpで取得したファイルのインポート | 19分16秒 |
| util.loadDump() | 5分40秒 |
Schema Dump Utility を使用したバックアップ時間が mysqldump の2倍程度、Dump Loading Utility は mysqldump のダンプファイルをインポートする時間の4倍近く高速化できました。
まとめ
新しく MySQL Shell に追加された Dump Utility 及び Loading Utility 、見逃せませんね。
今回の検証した結果からも、パラレル実行かつTSVファイル形式でのダンプ、インポートを行う事で、実行速度も大いに向上が見込めますし、実行中には%表示で進捗が見られるのも非常に嬉しいです。
また、データのインポートに関しては、MySQL Server 8.0.21 でRedoログを無効化する機能がリリースされているので、こちらも初期データを用意する際のインポート時間を短縮したい場合には有用なものになるかと思います。
今回のブログでは、Dump Utility、Loading Utility について、いくつかのオプションについては触れさせて頂きましたが、他にも多くのオプションがありますので、皆さんも是非、お試し下さい！
今後も便利な新機能が加わっていく MySQL Shell に期待したいと思います。
