MySQL Shell の 8.0.17 で Parallel Table Import Utility が追加されました。
Parallel Table Import Utility はファイルデータを元にテーブルにデータをインポートする機能で、スレッド数の指定が可能となっており、シングルスレッドで処理する LOAD DATAステートメント よりも、高速に処理できるとリファレンスに記載されています。
という事で、今回は Parallel Table Import Utility のインポート時間とLOAD DATAステートメントのインポート時間を簡単に比較してみたいと思います。
検証サーバ
| 項目 | 値 |
|---|---|
| OS | CentOS 7.5 |
| CPUコア数 | 8 |
| MySQL Server MySQL Shell |
Version 8.0.17（Community） |
検証前準備（インストール、インポートテーブル/インポートファイルの作成）
MySQLの公式レポジトリを追加し、MySQLサーバと MySQL Shell をインストールします。
|
1 2 |
# yum install -y https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm # yum install -y mysql-community-server mysql-shell |
余談となりますが、現バージョン（8.0.17）までの MySQL Shell では Python2.7 が必要でしたが、次バージョンとなる 8.0.18 からは Python3 が必要となるようです。
From version 8.0.18, MySQL Shell uses Python 3.
インストールが完了すれば、mysqldサービスを起動してrootユーザのパスワードを変更します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
# systemctl start mysqld # mysql -u root -p$(grep "temporary password" /var/log/mysqld.log | rev | awk '{print $1}' | rev) mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.17 Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> set password = 'RootUser_Password0'; Query OK, 0 rows affected (0.01 sec) mysql> quit Bye |
データをインポートするテーブルを作成します。
|
1 |
# mysql -u root -pRootUser_Password0 -e "create database blog; create table blog.data_load(col1 int ,col2 varchar(20), primary key(col1));" |
インポートするデータを簡易的に作成します。
|
1 |
# for i in {1..5000000}; do printf "\"%d\",\"xxxxxxxxxxxxxxxxxxxx\"\n" $i >> /var/lib/mysql-files/load.csv ; done |
これで500万行のデータファイルが作成されました。
ファイルサイズは 157MB となりました。
|
1 2 3 4 5 6 |
# wc -l /var/lib/mysql-files/load.csv 5000000 /var/lib/mysql-files/load.csv # tail -n 3 /var/lib/mysql-files/load.csv "4999998","xxxxxxxxxxxxxxxxxxxx" "4999999","xxxxxxxxxxxxxxxxxxxx" "5000000","xxxxxxxxxxxxxxxxxxxx" |
検証方法
- 前提条件
- インポートするデータファイルは、MySQLサーバ上に存在するものとします。
- 手順を簡略化させる為、インポートするユーザに rootユーザ を使用しています。（他ユーザで実行する場合は、INSERT及びFILE権限が必要となります。）
- Parallel Table Import Utility のスレッド数は、1, 2, 4, 6, 8, 10, 15 の 7パターン で確認します。
- インポートの度に、テーブルは TRUNCATE TABLE で再作成します。
LOAD DATAステートメントを実行する際はMySQLサーバにログインし、以下のコマンドでインポートします。
|
1 |
mysql> load data infile '/var/lib/mysql-files/load.csv' into table blog.data_load fields terminated by ',' enclosed by '"'; |
Parallel Table Import Utility を実行する際は、以下のコマンドで MySQL Shell にログインし、インポートを実行します。
|
1 2 |
# mysqlsh --mysql -u root -pRootUser_Password0 MySQL localhost JS > util.importTable("/var/lib/mysql-files/load.csv", {schema: "blog", table: "data_load", dialect: "csv-unix", threads: {スレッド数}, bytesPerChunk: "10M", showProgress: true}) |
- Parallel Table Import Utility を実行するには、Xプロトコルセッションではなく、Classicセッションで実行する必要があります。
- 内部的にLOAD DATA LOCALステートメントが使用される為、local_infileシステム変数をONにする必要があります。
bytesPerChunkでデータのチャンクサイズが指定可能で、デフォルトは50MBとなります。確認した限りでは、インポートはチャンク単位で行われる為、インポートデータサイズ / bytesPerChunkのサイズ以上にスレッド数を増加させることはできませんでした。
（インポートデータサイズが160MBで bytesPerChunk に50Mを指定した場合、最大で4スレッドまでしか使用されません。）
検証結果
- LOAD DATAステートメント
47.63 秒 - Parallel Table Import Utility
| スレッド数 | インポート時間 |
|---|---|
| 1 | 45.79 秒 |
| 2 | 25.37 秒 |
| 4 | 16.60 秒 |
| 6 | 14.25 秒 |
| 8 | 13.54 秒 |
| 10 | 13.76 秒 |
| 15 | 16.45 秒 |
- グラフ
まとめ
今回の検証では、スレッド数を調整する事で、LOAD DATAステートメントの1/3以下の時間でインポートする事ができました。
Parallel Table Import Utility は、内部的にLOAD DATA LOCALステートメントを並列に実行する事で処理が高速化されています。
ちなみに threads: 8 での実行中に show processlist を確認すると8スレッドで実行されていました。
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> pager grep -i "load data" PAGER set to 'grep -i "load data"' mysql> show processlist; | 138 | root | localhost | NULL | Query | 6 | executing | LOAD DATA LOCAL INFILE '/var/lib/mysql-files/load.csv' INTO TABLE `blog`.`data_load` FIELDS TERMINAT | | 139 | root | localhost | NULL | Query | 6 | executing | LOAD DATA LOCAL INFILE '/var/lib/mysql-files/load.csv' INTO TABLE `blog`.`data_load` FIELDS TERMINAT | | 140 | root | localhost | NULL | Query | 6 | executing | LOAD DATA LOCAL INFILE '/var/lib/mysql-files/load.csv' INTO TABLE `blog`.`data_load` FIELDS TERMINAT | | 141 | root | localhost | NULL | Query | 6 | executing | LOAD DATA LOCAL INFILE '/var/lib/mysql-files/load.csv' INTO TABLE `blog`.`data_load` FIELDS TERMINAT | | 142 | root | localhost | NULL | Query | 6 | executing | LOAD DATA LOCAL INFILE '/var/lib/mysql-files/load.csv' INTO TABLE `blog`.`data_load` FIELDS TERMINAT | | 143 | root | localhost | NULL | Query | 6 | executing | LOAD DATA LOCAL INFILE '/var/lib/mysql-files/load.csv' INTO TABLE `blog`.`data_load` FIELDS TERMINAT | | 144 | root | localhost | NULL | Query | 6 | executing | LOAD DATA LOCAL INFILE '/var/lib/mysql-files/load.csv' INTO TABLE `blog`.`data_load` FIELDS TERMINAT | | 145 | root | localhost | NULL | Query | 6 | executing | LOAD DATA LOCAL INFILE '/var/lib/mysql-files/load.csv' INTO TABLE `blog`.`data_load` FIELDS TERMINAT | |
本検証ではMySQLサーバ上からのインポートで確認しましたが、リモートサーバからも同様にパラレル実行が可能ですので、LOAD DATAステートメントや mysqlimport を使用してデータのインポート時間にお悩みの方は、Parallel Table Import Utility を試してみてください。
実行する際は、サーバリソースやMySQLの設定によって最適な設定は変わるので、threads、bytesPerChunkパラメータを調整して確認しましょう。
