はじめに
MySQL Shell 8.0.21 で MySQLインスタンスのダンプ機能 が実装されてから１年以上が経過しました。こちらは従来の mysqldump と違って、処理の並列化（効率UP）や細かな制御が可能ということもあり、弊社でも徐々に実際の業務で使用するケースが増えています。
また、過去に本技術ブログでも何度か取り上げています。
MySQL Shell で バックアップとリストアをパラレルで実行する
MySQL Shell の dumpInstance() を使ってMySQL5.6をバージョンアップする
そんな MySQL Shell のdump機能ですが、今回はこれまで取り上げてこなかった帯域制限オプションについて紹介したいと思います。
帯域制御オプションとは
MySQLインスタンスを丸ごとダンプ（バックアップ）をする場合、取得したダンプファイルは他のサーバに転送して保管するケースも多いかと思います。また、ダンプファイルを別のサーバに転送・リストアすることで、新規にMySQLを構築するケースもあるでしょう。
この時、専用のNWセグメントが存在するのであれば問題ないのですが、セグメントが１つしかなかったり、他のサービスと共有していたりすると、ダンプの転送時の負荷が問題になってしまう可能性があります。特にデータサイズが大きければ大きいほど、帯域の使用量は多くなりますし、転送時間も長期化してしまいます。
そこで役に立つのが、今回紹介する帯域制御オプションです。
MySQL Shell のdump機能（本稿では主に dumpInstance() を想定）には、様々なオプションが存在します。その中で、ダンプ処理中の帯域を制御するためのオプションは以下の2つです。
| オプション名 | デフォルト | 説明 |
|---|---|---|
| maxRate | 0 (= 上限なし） | dumpを実行する１スレッド当たりの最大使用帯域。100M を設定した場合、１スレッド当たりの上限が 100MB / s になる。 |
| threads | 4 | dumpを実行する並列スレッド数。スレッド数を増やすと実行時間が短くなる可能性があるが、MaxRate による合計の帯域上限も引き上がる。 |
それでは、実際にこのオプションを使って、dumpInstance() の帯域制限を検証してみましょう。
テスト環境
今回は OCI の Compute を使って、以下のテスト用インスタンスを2台（Aサーバ、Bサーバ）用意しました。サーバスペックは両方とも同じです。
- Aサーバ … MySQLサーバ（MySQL 8.0.27）
- Bサーバ … MySQL Shell の dumpInstance() をAサーバに対して実行するサーバ
| Shape Name | VM.Standard.E4.Flex |
|---|---|
| CPU | 8 コア |
| MEMORY | 64 GB |
| Disk | 500 GB (※) |
| NW | 8 Gbps network bandwidth |
| OS | CentOS 8.4 |
※ マニュアルによれば、このボリュームサイズの場合は、最大スループットが「240 MB/s OR 200 MB/s」になり、最大IOPSは「25,000」になります
※ OCI の CentOS8 インスタンスではルートボリューム（/）の初期状態は LVM で割り当てられた30GBしかありません。そのため、インスタンス起動後に PV / LV / Filesystem を拡張する必要があります。
サーバインスタンスが立ち上がったら、以下の手順で環境を構築しています。
【Aサーバ】
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
# dnf install -y https://dev.mysql.com/get/mysql80-community-release-el8-1.noarch.rpm # dnf module disable mysql # dnf install -y mysql-community-server mysql-devel → mysql-8.0.27がインストールされました # vi /etc/my.cnf 以下を記載 [mysqld] innodb_buffer_pool_size = 48G innodb_log_file_size = 1G disable_log_bin # install -o mysql -g mysql -m 755 -d /var/lib/mysql # mysqld --no-defaults --initialize-insecure --user=mysql --datadir=/var/lib/mysql # setenforce 0 # systemctl start mysqld |
【Bサーバ】
|
1 2 3 4 |
# dnf install -y https://dev.mysql.com/get/mysql80-community-release-el8-1.noarch.rpm # dnf module disable mysql # dnf install -y mysql-shell → mysql-shell-8.0.27がインストールされました |
テストデータのロード
今回は、tpcc-mysql を使って、テストデータを用意します。以下のコマンドはAサーバで実施します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
$ sudo yum install -y gcc make git $ git clone https://github.com/Percona-Lab/tpcc-mysql.git $ cd tpcc-mysql/src $ make $ cd .. $ mysql -u root -e "create database tpcc_xlarge" $ mysql -u root tpcc_xlarge < create_table.sql $ mysql -u root tpcc_xlarge < add_fkey_idx.sql $ mysql -u root -e "create database tpcc_large" $ mysql -u root tpcc_large < create_table.sql $ mysql -u root tpcc_large < add_fkey_idx.sql $ mysql -u root -e "create database tpcc_medium" $ mysql -u root tpcc_medium < create_table.sql $ mysql -u root tpcc_medium < add_fkey_idx.sql $ mysql -u root -e "create database tpcc_small" $ mysql -u root tpcc_small < create_table.sql $ mysql -u root tpcc_small < add_fkey_idx.sql $ ./tpcc_load -h localhost -u root -p '' -d tpcc_xlarge -w 1500 1>/dev/null & $ ./tpcc_load -h localhost -u root -p '' -d tpcc_large -w 1000 1>/dev/null & $ ./tpcc_load -h localhost -u root -p '' -d tpcc_medium -w 300 1>/dev/null & $ ./tpcc_load -h localhost -u root -p '' -d tpcc_small -w 30 1>/dev/null & |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
# ls -lh /var/lib/mysql/tpcc_xlarge/ | head total 137G -rw-r-----. 1 mysql mysql 30G Oct 19 16:57 customer.ibd -rw-r-----. 1 mysql mysql 10M Oct 19 15:31 district.ibd ... # ls -lh /var/lib/mysql/tpcc_large/ | head total 91G -rw-r-----. 1 mysql mysql 20G Oct 19 05:01 customer.ibd -rw-r-----. 1 mysql mysql 9.0M Oct 19 03:43 district.ibd ... # ls -lh /var/lib/mysql/tpcc_medium/ | head total 28G -rw-r-----. 1 mysql mysql 6.0G Oct 19 14:02 customer.ibd -rw-r-----. 1 mysql mysql 448K Oct 19 13:43 district.ibd ... # ls -lh /var/lib/mysql/tpcc_small/ | head total 2.8G -rw-r-----. 1 mysql mysql 616M Oct 19 01:50 customer.ibd -rw-r-----. 1 mysql mysql 144K Oct 19 01:48 district.ibd ... |
また、MySQL Shell用のユーザも用意しておきます。
|
1 2 3 4 5 |
# mysql -u root mysql> CREATE USER `mysqlsh_admin`@`%` IDENTIFIED BY "MySQL8.0"; mysql> GRANT ALL ON *.* TO `mysqlsh_admin`@`%`; mysql> exit |
帯域制限オプションの検証
(1) デフォルト設定
テスト環境が用意できたので、帯域制限オプションの検証をしていきましょう。
まずは、デフォルトの設定で dumpInstance() を実行してみます。なお、これ以降はBサーバで作業を行います。
※ SELinux、iptables(firewalld) は無効にしてあります
|
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 33 34 35 36 37 38 39 |
$ mkdir /home/opc/80_dump $ mysqlsh mysqlsh_admin@10.0.0.129 Please provide the password for 'mysqlsh_admin@10.0.0.129': ******** mysqlsh> util.dumpInstance( "/home/opc/80_dump/", { threads: 4 } ) ... 4 thds dumping - 107% (1.41G rows / ~1.31G rows), 339.33K rows/s, 194.46 MB/s uncompressed, 4 thds dumping - 107% (1.41G rows / ~1.31G rows), 339.33K rows/s, 193.79 MB/s uncompressed, 4 thds dumping - 107% (1.41G rows / ~1.31G rows), 342.19K rows/s, 193.79 MB/s uncompressed, 4 thds dumping - 107% (1.41G rows / ~1.31G rows), 342.19K rows/s, 196.51 MB/s uncompressed, 4 thds dumping - 107% (1.41G rows / ~1.31G rows), 343.04K rows/s, 196.51 MB/s uncompressed, 4 thds dumping - 107% (1.41G rows / ~1.31G rows), 345.62K rows/s, 196.47 MB/s uncompressed, 2 thds dumping - 107% (1.41G rows / ~1.31G rows), 344.76K rows/s, 196.64 MB/s uncompressed, 107% (1.41G rows / ~1.31G rows), 344.76K rows/s, 196.64 MB/s uncompressed, 133.16 MB/s compressed Dump duration: 00:21:18s Total duration: 00:21:18s Schemas dumped: 4 Tables dumped: 36 Uncompressed data size: 204.19 GB Compressed data size: 120.89 GB Compression ratio: 1.7 Rows written: 1412588938 Bytes written: 120.89 GB Average uncompressed throughput: 159.75 MB/s Average compressed throughput: 94.58 MB/s $ ls -lh /home/opc/80_dump/ | head total 113G -rw-r-----. 1 opc opc 452K Oct 20 05:52 @.done.json -rw-r-----. 1 opc opc 974 Oct 20 05:30 @.json -rw-r-----. 1 opc opc 241 Oct 20 05:30 @.post.sql -rw-r-----. 1 opc opc 241 Oct 20 05:30 @.sql |
ダンプ取得中に dstat コマンドでネットワーク負荷を確認すると、以下のように合計 150M～200MB/s 程度が使われていました。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
$ sudo dnf install -y dstat $ dstat -tn ----system---- -net/total- time | recv send ... 20-10 05:33:46| 176M 244k 20-10 05:33:47| 200M 327k 20-10 05:33:48| 185M 279k 20-10 05:33:49| 171M 266k 20-10 05:33:50| 194M 295k 20-10 05:33:51| 200M 284k 20-10 05:33:52| 188M 268k 20-10 05:33:53| 184M 266k 20-10 05:33:54| 193M 293k 20-10 05:33:55| 194M 286k 20-10 05:33:56| 189M 274k ... |
OCIコンソールでBサーバのメトリクスを見ると、以下のような負荷状況でした。
(2) 帯域制限 10MB/s & 4threads
それでは、MaxRateオプションを付けて dumpInstance() を実行してみましょう。まずは、1スレッドあたり 10MB/s にしてみます。スレッド数は変わらずデフォルトの4スレッドです。
|
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 |
$ rm -rf /home/opc/80_dump/* mysqlsh> util.dumpInstance( "/home/opc/80_dump/", { threads: 4, maxRate: "10M" } ) ... 4 thds dumping - 107% (1.41G rows / ~1.31G rows), 112.25K rows/s, 65.89 MB/s uncompressed, 4 4 thds dumping - 107% (1.41G rows / ~1.31G rows), 109.68K rows/s, 63.30 MB/s uncompressed, 4 4 thds dumping - 107% (1.41G rows / ~1.31G rows), 106.83K rows/s, 61.82 MB/s uncompressed, 4 3 thds dumping - 107% (1.41G rows / ~1.31G rows), 106.83K rows/s, 61.82 MB/s uncompressed, 4 2 thds dumping - 107% (1.41G rows / ~1.31G rows), 104.25K rows/s, 60.08 MB/s uncompressed, 4 1 thds dumping - 107% (1.41G rows / ~1.31G rows), 104.25K rows/s, 60.08 MB/s uncompressed, 4 107% (1.41G rows / ~1.31G rows), 104.25K rows/s, 57.37 MB/s uncompressed, 39.01 MB/s compressed Dump duration: 00:58:12s Total duration: 00:58:13s Schemas dumped: 4 Tables dumped: 36 Uncompressed data size: 204.19 GB Compressed data size: 120.89 GB Compression ratio: 1.7 Rows written: 1412588938 Bytes written: 120.89 GB Average uncompressed throughput: 58.47 MB/s Average compressed throughput: 34.61 MB/s |
ダンプ取得中の dstat のログおよびメトリクスを見ると、MaxRate 未指定（＝制限なし）の時と比べると、明確に負荷が下がっていることが分かります。その代わり、ダンプの実行時間は長くなります。
|
1 2 3 4 5 6 7 8 9 10 |
$ dstat -tn ... ----system---- -net/total- time | recv send 20-10 06:04:47| 47M 86k 20-10 06:04:48| 70M 87k 20-10 06:04:49| 53M 64k 20-10 06:04:50| 49M 68k 20-10 06:04:51| 51M 71k ... |
(3) 帯域制限 10MB/s & 8threads
次に、上のパターンからスレッド数を 4 から 8 に増やして実行してみます。
|
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 |
$ rm -rf /home/opc/80_dump/* mysqlsh> util.dumpInstance( "/home/opc/80_dump/", { threads: 8, maxRate: "10M" } ) ... 8 thds dumping - 107% (1.41G rows / ~1.31G rows), 223.36K rows/s, 127.76 MB/s uncompressed, 86.54 MB 8 thds dumping - 107% (1.41G rows / ~1.31G rows), 225.94K rows/s, 127.01 MB/s uncompressed, 86.05 MB 8 thds dumping - 107% (1.41G rows / ~1.31G rows), 223.36K rows/s, 129.34 MB/s uncompressed, 87.61 MB 6 thds dumping - 107% (1.41G rows / ~1.31G rows), 217.94K rows/s, 126.99 MB/s uncompressed, 86.01 MB 3 thds dumping - 107% (1.41G rows / ~1.31G rows), 217.94K rows/s, 126.99 MB/s uncompressed, 86.01 MB 1 thds dumping - 107% (1.41G rows / ~1.31G rows), 200.23K rows/s, 119.80 MB/s uncompressed, 81.14 MB 107% (1.41G rows / ~1.31G rows), 200.23K rows/s, 119.80 MB/s uncompressed, 81.14 MB/s compressed Dump duration: 00:29:29s Total duration: 00:29:29s Schemas dumped: 4 Tables dumped: 36 Uncompressed data size: 204.19 GB Compressed data size: 120.89 GB Compression ratio: 1.7 Rows written: 1412588938 Bytes written: 120.89 GB Average uncompressed throughput: 115.41 MB/s Average compressed throughput: 68.33 MB/s |
実行時間は短くなりますが、MaxRateはスレッド当たりの帯域を制限するオプションのため、トータルのネットワーク負荷は増大しました。
|
1 2 3 4 5 6 7 8 9 10 |
$ dstat -tn ... ----system---- -net/total- 20-10 07:11:39| 91M 134k 20-10 07:11:40| 119M 180k 20-10 07:11:41| 106M 165k 20-10 07:11:42| 112M 171k 20-10 07:11:43| 95M 149k 20-10 07:11:44| 117M 183k ... |
(4) 帯域制限 20MB/s & 2threads
次に MaxRate:20MB かつ threads:2 のパターンを計測してみます。これは 20MB * 2 = 40MB なので、(2) の 10MB * 4 の時と同じ制限量になります。
|
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 |
$ rm -rf /home/opc/80_dump/* mysqlsh> util.dumpInstance( "/home/opc/80_dump/", { threads: 2, maxRate: "20M" } ) ... 2 thds dumping - 107% (1.41G rows / ~1.31G rows), 171.95K rows/s, 52.52 MB/s uncompressed, 37.81 MB/ 2 thds dumping - 107% (1.41G rows / ~1.31G rows), 171.66K rows/s, 53.38 MB/s uncompressed, 38.42 MB/ 2 thds dumping - 107% (1.41G rows / ~1.31G rows), 169.95K rows/s, 52.24 MB/s uncompressed, 37.61 MB/ 2 thds dumping - 107% (1.41G rows / ~1.31G rows), 169.38K rows/s, 52.71 MB/s uncompressed, 37.94 MB/ 2 thds dumping - 107% (1.41G rows / ~1.31G rows), 167.38K rows/s, 51.80 MB/s uncompressed, 37.28 MB/ 107% (1.41G rows / ~1.31G rows), 167.38K rows/s, 51.42 MB/s uncompressed, 37.00 MB/s compressed Dump duration: 01:07:00s Total duration: 01:07:00s Schemas dumped: 4 Tables dumped: 36 Uncompressed data size: 204.19 GB Compressed data size: 120.89 GB Compression ratio: 1.7 Rows written: 1412588938 Bytes written: 120.89 GB Average uncompressed throughput: 50.79 MB/s Average compressed throughput: 30.07 MB/s |
結果としては、(2) のパターンよりも実行時間は伸び、スループットの値も低くなりました。
|
1 2 3 4 5 6 7 8 9 10 11 |
$ dstat -tn ... ----system---- -net/total- 20-10 08:22:07| 51M 61k 20-10 08:22:08| 44M 51k 20-10 08:22:09| 51M 55k 20-10 08:22:10| 53M 75k 20-10 08:22:11| 45M 56k 20-10 08:22:12| 43M 60k 20-10 08:22:13| 54M 78k ... |
(5) 帯域制限 5MB/s & 8threads
最後に MaxRate:5MB かつ threads:8 のパターンを計測してみます。これも 5MB * 8 = 40MB なので、(2) の 10MB * 4 の時と同じ制限量になります。
|
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 33 34 35 36 37 38 |
$ rm -rf /home/opc/80_dump/* mysqlsh> util.dumpInstance( "/home/opc/80_dump/", { threads: 8, maxRate: "5M" } ) ... 8 thds dumping - 107% (1.41G rows / ~1.31G rows), 79.69K rows/s, 45.46 MB/s uncompressed, 30.88 MB/s 8 thds dumping - 107% (1.41G rows / ~1.31G rows), 79.12K rows/s, 45.46 MB/s uncompressed, 30.88 MB/s 7 thds dumping - 107% (1.41G rows / ~1.31G rows), 79.12K rows/s, 44.82 MB/s uncompressed, 30.44 MB/s 7 thds dumping - 107% (1.41G rows / ~1.31G rows), 78.26K rows/s, 44.82 MB/s uncompressed, 30.44 MB/s 7 thds dumping - 107% (1.41G rows / ~1.31G rows), 78.26K rows/s, 44.91 MB/s uncompressed, 30.54 MB/s 5 thds dumping - 107% (1.41G rows / ~1.31G rows), 76.83K rows/s, 44.91 MB/s uncompressed, 30.54 MB/s 4 thds dumping - 107% (1.41G rows / ~1.31G rows), 76.83K rows/s, 43.66 MB/s uncompressed, 29.71 MB/s 3 thds dumping - 107% (1.41G rows / ~1.31G rows), 73.69K rows/s, 43.66 MB/s uncompressed, 29.71 MB/s 3 thds dumping - 107% (1.41G rows / ~1.31G rows), 73.69K rows/s, 41.71 MB/s uncompressed, 28.36 MB/s 3 thds dumping - 107% (1.41G rows / ~1.31G rows), 69.41K rows/s, 41.71 MB/s uncompressed, 28.36 MB/s 1 thds dumping - 107% (1.41G rows / ~1.31G rows), 69.41K rows/s, 38.68 MB/s uncompressed, 26.31 MB/s 1 thds dumping - 107% (1.41G rows / ~1.31G rows), 64.55K rows/s, 38.68 MB/s uncompressed, 26.31 MB/s 1 thds dumping - 107% (1.41G rows / ~1.31G rows), 59.70K rows/s, 35.43 MB/s uncompressed, 24.11 MB/s 107% (1.41G rows / ~1.31G rows), 59.70K rows/s, 35.43 MB/s uncompressed, 24.11 MB/s compressed Dump duration: 00:55:13s Total duration: 00:55:13s Schemas dumped: 4 Tables dumped: 36 Uncompressed data size: 204.19 GB Compressed data size: 120.89 GB Compression ratio: 1.7 Rows written: 1412588938 Bytes written: 120.89 GB Average uncompressed throughput: 61.63 MB/s Average compressed throughput: 36.49 MB/s |
こちらは、(2) / (4)のパターンよりも若干結果が良くなりました。今回のケースでは、「帯域を広く使って、少ないスレッド数でダンプ」よりも「帯域は狭くても、多くのスレッド数でダンプ」を行う方が効率が良くなるようです。
|
1 2 3 4 5 6 7 8 9 10 |
$ dstat -tn ... ----system---- -net/total- 20-10 09:03:17| 47M 92k 20-10 09:03:18| 69M 114k 20-10 09:03:19| 61M 105k 20-10 09:03:20| 66M 114k 20-10 09:03:21| 64M 109k 20-10 09:03:22| 77M 134k ... |
検証結果
| No | MaxRate: | Threads: | 実行時間 | スループット（非圧縮） | スループット（圧縮） |
|---|---|---|---|---|---|
| 1 | 0 (no limit) | 4 | 00:21:18s | 159.75 MB/s | 94.58 MB/s |
| 2 | 10M | 4 | 00:58:12s | 58.47 MB/s | 34.61 MB/s |
| 3 | 10M | 8 | 00:29:29s | 115.41 MB/s | 68.33 MB/s |
| 4 | 20M | 2 | 01:07:00s | 50.79 MB/s | 30.07 MB/s |
| 5 | 5M | 8 | 00:55:13s | 61.63 MB/s | 36.49 MB/s |
上記の結果から以下のことが分かります。
- 帯域制限オプションを使用しない場合、最も早くdumpInstance()は完了するが、ネットワーク負荷は高くなる
- MaxRateオプションを設定するとネットワーク負荷は軽減されるが、dumpInstance()の実行時間は長くなる
- MaxRateオプションを設定した状態でも、Threads オプションを引き上げることで実行時間は短くできる（ネットワーク負荷は増大）
- MaxRate * Threads の合計値が同じになる設定値の組み合わせであっても、実行時間やスループットに差異は発生する
- MaxRate * Threads の値を実際のスループットの値が超えているケースもあることから、MaxRate:による制限を超えてダンプ処理が行われる可能性もある
ただし、上記は今回のテスト環境、テストデータで実施した結果なので、サーバスペックやデータの内容・サイズが変われば結果や傾向が変わる可能性がある点にはご留意ください。
まとめ
MySQL Shell のダンプ機能は非常に便利ですが、デフォルトの設定ではネットワーク帯域を無制限に使用する仕様になっています。もし、ネットワーク負荷を気にされる方がいれば、MaxRate / Threads オプションを活用してみてください。
