MySQLShellのベンチマークテスト
はじめに
こんにちは。スマサテ株式会社でデータエンジニアをしているオガサハラです。
私は普段、データパイプラインの構築などをしているのですが、データ移行時などにはMySQLShellを利用することが多いです。
そこで今回は、MySQLShellのベンチマークテストを行っていきたいと思います。
そもそもMySQLShellとは?
MySQLShellとは、MySQL5.7以降から追加された高度なクライアントツールです。
従来のMySQLDumpとの違いは、MySQLDumpはシングルスレッドでのみ動作するのに対して、MySQLShellは並列処理や圧縮に対応しておりMySQLDumpに比べると大幅にパフォーマンスが向上しています。
また、PythonやJavaScriptを用いたスクリプト実行や自動化処理にも対応しており、MySQLShellはMySQLDumpの後継にあたるツールといえます。
様々な機能が用意されていますが、弊社では特にデータベースのテーブルのデータをcsv形式のファイルでエクスポート / インポートできるテーブルエクスポートユーティリティ / パラレルテーブルインポートユーティリティ、データベースやテーブルのDump、Loadを行うことができるダンプユーティリティ / ダンプロードユーティリティを利用することが多いです。
ベンチマークテスト
では今回は、以下の前提のもとベンチマークテストを実施していきたいと思います。
テストデータ
- データ件数 :50,000,000件
- データサイズ :約22GB
MySQLShell
- スレッド数 :8
テーブル構造
| カラム名 | データ型 | NULL許可 | キー | デフォルト値 | その他 |
|---|---|---|---|---|---|
| id | bigint unsigned | NO | PRI | auto_increment | |
| column_1 | bigint unsigned | NO | |||
| column_2 | varchar(100) | NO | |||
| column_3 | varchar(100) | NO | |||
| column_4 | varchar(20) | NO | |||
| column_5 | int | NO | |||
| column_6 | decimal(10,2) | NO | |||
| column_7 | text | YES | |||
| created_at | timestamp | YES | CURRENT_TIMESTAMP | ||
| updated_at | timestamp | YES | CURRENT_TIMESTAMP |
MySQLDump vs MySQLShell(ダンプロードユーティリティ)
まずは、従来のMySQLDumpとMySQLShellのダンプロードユーティリティとのベンチマークテストを実施したいと思います。
結果は以下の通りになりました。
- MySQLDump : 35 min 5 sec
- MySQLShell (ダンプロードユーティリティ) : 6 min 41 sec
今回の検証下ではMySQLShell (ダンプロードユーティリティ)はMySQLDumpの約5倍の実行速度になりました。やはり従来のMySQLDumpと比べるとMySQLShell (ダンプロードユーティリティ)の方が実行速度は断然早いですね!
MySQLShell(ダンプロードユーティリティ) vs MySQLShell(パラレルテーブルインポートユーティリティ)
つぎにMySQLShellのダンプロードユーティリティとMySQLShellのパラレルテーブルインポートユーティリティのベンチマークテストを行いたいと思います。
結果は以下の通りになりました。
- MySQLShell (ダンプロードユーティリティ): 6 min 41 sec
- MySQLShell (パラレルテーブルインポートユーティリティ): 6 min 44 sec
今回の検証下ではダンプロードユーティリティとパラレルテーブルインポートユーティリティとでは、ほぼ同等の処理時間になっています。
これらのことからダンプロードユーティリティは、バックアップおよびリストア、パラレルテーブルインポートユーティリティは外部からのデータ連携(csv、tsv)など用途により使い分けるのが良さそうです。
MySQLShell(ダンプロードユーティリティ)
最後に、MySQLShellのダンプロードユーティリティを使用し、インデックスありのテーブルとインデックスなしのテーブルに対するロード処理のベンチマークテストを実施したいと思います。
一般的には、インデックスは登録や更新のたびにメンテナンスが行われるため、過剰に付与するとデータの登録、更新のパフォーマンスの低下を招くとされていますが、大量のデータを移行する際に実際にどのくらいパフォーマンスに影響するのか検証してみたいと思います。
今回はロードするデータ自体は、先述のMySQLShell(ダンプロードユーティリティ)で使用したDumpを利用しロード対象のテーブルに以下のインデックスを作成して実行速度を計測していきます。
ALTER TABLE benchmark_test
ADD KEY idx_column_1 (column_1),
ADD KEY idx_column_2 (column_2),
ADD KEY idx_column_3 (column_3),
ADD KEY idx_column_4 (column_4),
ADD KEY idx_column_5 (column_5),
ADD KEY idx_composite (column_1, column_2, column_3) ;
結果は以下の通りになりました。
- インデックスなしのMySQLShell(ダンプロードユーティリティ): 6 min 41 sec
- インデックスありのMySQLShell(ダンプロードユーティリティ): 26 min 24 sec
やはりインデックスありのテーブルへのロードの方が明らかに実行速度が遅くなっていますね。。。
50,000,000件のデータをロードするとインデックスの再構築コスト、I/Oオーバーヘッドなどが積み重なり約4倍の処理時間になってしまいました。
おわりに
今回は簡易的ではありますが、MySQLShellのベンチマークテストを実施しました。弊社では日常的に大量のデータを扱う機会に溢れており、技術的な挑戦がたくさんあります。
Webフルスタックエンジニア、AIエンジニアなどを積極的に採用しているので、
このような環境に少しでもご興味のある方はぜひWantedlyをご覧ください。
Discussion