はじめに
最終的なゴールとしてはMDS HeatWave のTPC-H性能比較テストを目指しているものの、今回はTPC-H性能比較テストのための準備編として主に以下の作業を実施しました。
TPC-H性能比較テストのためのデータを準備する
TPC-Hデータをオブジェクトストレージに格納する
踏み台サーバーにMySQL Shell をインストールする
HeatWave専用のMDSを作成する
MDSにTPC-Hデータをインポートする
お試し環境
前提
- MDSへアクセスするための踏み台サーバー（パブリック・サブネット）は既に作成済み
- セキュリティリストで踏み台サーバーからプライベートサブネットに作成されるMDSへのアクセスが許可されている（ポート：3306）
- オブジェクトストレージは作成済み
踏み台サーバの追加設定
HeatWave Quickstart ドキュメントにそって、TPC-H用のデータベースとテーブルを作成し、オブジェクトストレージからTPC-Hデータをインポートするところまでを本稿の対象とします。オブジェクトストレージからMDSへデータをインポートするためには、次の追加設定が必要です。
MySQL Shellのインストール
踏み台サーバーにて、MySQL Shell をインストールします。
sudo yum install -y https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
sudo yum install -y mysql-shell
OCI CLIインストール
MySQL Shellは、オブジェクトストレージにアクセスするときにCLI構成ファイルを参照するため、OCI CLIのインストールも必要です。OCI CLIをインストールします。
sudo yum install -y python36-oci-cli
TPC-Hデータの取得と生成
TPC-Hのデータを生成するためには、まずTPCのウェブサイトでライセンスに合意の上でツールをダウンロードします。ダウンロードしたファイルを踏み台サーバーにコピーしunzip します。
unzip ef98b03f-9f19-492f-8ff8-39c4e4f1a464-tpc-h-tool.zip
dbgenコマンドの生成
TPC-H_Tools_v3.0.0 ディレクトリが作成されます。続けて dbgen ディレクトリへ移動します。
cd TPC-H_Tools_v3.0.0/dbgen
dbgen ディレクトリにある makefile.suite を以下のように編集します。
CC = gcc
DATABASE= SQLSERVER
MACHINE = LINUX
WORKLOAD = TPCH
make を実行します。
make -f makefile.suite
makeを実行すると以下のログが表示されました。
[opc@bastion01 dbgen]$ make -f makefile.suite
gcc -g -DDBNAME=\"dss\" -DLINUX -DSQLSERVER -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o build.o build.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DSQLSERVER -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o driver.o driver.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DSQLSERVER -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o bm_utils.o bm_utils.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DSQLSERVER -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o rnd.o rnd.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DSQLSERVER -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o print.o print.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DSQLSERVER -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o load_stub.o load_stub.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DSQLSERVER -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o bcd2.o bcd2.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DSQLSERVER -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o speed_seed.o speed_seed.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DSQLSERVER -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o text.o text.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DSQLSERVER -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o permute.o permute.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DSQLSERVER -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o rng64.o rng64.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DSQLSERVER -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -O -o dbgen build.o driver.o bm_utils.o rnd.o print.o load_stub.o bcd2.o speed_seed.o text.o permute.o rng64.o -lm
gcc -g -DDBNAME=\"dss\" -DLINUX -DSQLSERVER -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o qgen.o qgen.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DSQLSERVER -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o varsub.o varsub.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DSQLSERVER -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -O -o qgen build.o bm_utils.o qgen.o rnd.o varsub.o text.o bcd2.o permute.o speed_seed.o rng64.o -lm
make に成功しdbgen, qgen コマンドが作成されました。
[opc@bastion01 dbgen]$ ll dbgen qgen
-rwxrwxr-x. 1 opc opc 106520 Oct 22 20:56 dbgen
-rwxrwxr-x. 1 opc opc 98608 Oct 22 20:56 qgen
dbgen コマンドでTPC-Hデータを作成
dggenコマンドで10GBのデータを作成します。
[opc@bastion01 dbgen]$ ./dbgen -s 10
TPC-H Population Generator (Version 3.0.0)
Copyright Transaction Processing Performance Council 1994 - 2010
dbgenに成功し８つのテーブルが作成されました。
[opc@bastion01 dbgen]$ ll *.tbl
-rw-rw-r--. 1 opc opc 244847642 Oct 22 21:16 customer.tbl
-rw-rw-r--. 1 opc opc 7775727688 Oct 22 21:16 lineitem.tbl
-rw-rw-r--. 1 opc opc 2224 Oct 22 21:16 nation.tbl
-rw-rw-r--. 1 opc opc 1749195031 Oct 22 21:16 orders.tbl
-rw-rw-r--. 1 opc opc 1204850769 Oct 22 21:16 partsupp.tbl
-rw-rw-r--. 1 opc opc 243336157 Oct 22 21:16 part.tbl
-rw-rw-r--. 1 opc opc 389 Oct 22 21:16 region.tbl
-rw-rw-r--. 1 opc opc 14176368 Oct 22 21:16 supplier.tbl
TPC-Hデータをオブジェクトストレージへアップロード
HeatWave Quickstart ドキュメントにそって、生成された８つのサンプルデータをオブジェクトストレージにアップロードします。オブジェクトストレージは bucket-tpch という名前で事前作成しておいたので、こちらのオブジェクトストレージを使います。
オブジェクトストレージのマルチパート・アップロード
ドキュメントではオブジェクトストレージの[アップロード]から手動でドラッグ＆ドロップでのアプロード手順が記載されてますが、例えば踏み台サーバーにある最大データ量の lineitem.tbl (約8GB)をWindows10ラップトップに移動してドラッグ＆ドロップ操作するにはコピーに膨大な時間を要します。
トータル10GBをオブジェクトストレージへのアップロードする必要があるため、今回はオブジェクトストレージのマルチパート・アップロード機能でアップロードします。
マルチパート・アップロードとは
""マルチパート・アップロードでは、オブジェクトの個々のパーツを並行してアップロードして、アップロードに費やす時間を短縮します。単一のアップロード操作に対して 大きすぎるオブジェクト、例えば 100MiB以上のオブジェクトをアップロードする場合などに利用することをおすすめします。""
～※マルチパート・アップロードのドキュメントから引用～
マルチパート・アップロードは、API または CLIを介して実行し、CLIの場合は oci os object put コマンド を実行します。
マルチパート・アップロード実行
例えば、region.tbl をオブジェクトストレージ bucket-tpch へアップロードする場合には、以下のCLIコマンドとなります。
oci os object put --bucket-name bucket-tpch --file '/home/opc/TPC-H_Tools_v3.0.0/dbgen/region.tbl' --name region.tbl
約8GBの lineitem.tbl をマルチパート・アップロードで実行してみます。実行ログからは58のパートにスプリットされ平行してアップロードされたことが伺えます。アップロードも数秒程度で完了し、これは使える！と実感しました。大容量データをオブジェクトストレージへアップロードする際には是非ご検討ください。お勧めです。
oci os object put --bucket-name bucket-tpch --file '/home/opc/TPC-H_Tools_v3.0.0/dbgen/lineitem.tbl' --name lineitem.tbl
実行ログ：
Upload ID: a88b8e63-9022-d24e-9c87-948476ef4c95
Split file into 58 parts for upload.
Uploading object [####################################] 100%
{
"etag": "77b40d6a-fa98-432b-882f-bac9bd0d0865",
"last-modified": "Fri, 22 Oct 2021 23:52:07 GMT",
"opc-multipart-md5": "5wwhGcPnRVAw0xRDstFo+A==-58"
}
残りの各データも同様にアップロードし、８つ全てのデータのアップロードが短期間で完了しました。
HeatWave用のMDSの作成
MDSの作成
OCIにサインイン後、左上のナビゲーションメニューから[データベース]-[MySQL]を選択し、MySQL DBシステムの作成 をクリックします。
[コンパートメントに作成] 任意のコンパートメントを指定します。
[名前] 任意の名前を指定します。例）mds-heatwave4tpcs
[HeatWave] HeatWaveを選択します。
管理者資格証明の作成
[ユーザー名] adminとします。
[パスワード] 任意のパスワードを指定します。
ネットワーキングの構成
[仮想クラウド・ネットワーク] 既存の仮想クラウド・ネットワークを選択。例）osaka-vcn
[サブネット] プライベート・サブネットを選択します。
以下はすべてデフォルト設定のまま。作成 をクリックします。
約７～８分後にMDSが作成されました。
MDSへTPC-Hデータをインポート
踏み台サーバーへ接続
踏み台サーバーへ接続します。
ssh -i ~/.ssh/id_rsa opc@168.138.44.xxx
MySQL ShellからMDSへ接続
踏み台サーバーでMDSのエンドポイントを指定して、MySQL Shellを起動しMDSに接続します。
MDSのエンドポイントは、リソースから参照することもできますし、詳細画面からコピーすることも可能です。
次の書式で接続します。
mysqlsh --mysql admin@192.168.100.86
パスワードを聞かれるので、MDS作成時に定義したパスワードを入力します。次のようにMySQLシェルが起動されました。MySQLシェルはデフォルトでJavaScript実行モードで開きます。
MySQLシェルの実行モードをJavaScriptからSQLに変更します。
SQLモードにスイッチしました。
データベースとテーブルを作成
HeatWave Quickstart ドキュメントにそって、tpchサンプルデータベースとテーブルを作成します。MySQLシェルのSQLモードで次のコマンドを順次実行します。
CREATE DATABASE tpch character set utf8mb4;
USE tpch;
CREATE TABLE nation ( N_NATIONKEY INTEGER primary key,
N_NAME CHAR(25) NOT NULL,
N_REGIONKEY INTEGER NOT NULL,
N_COMMENT VARCHAR(152));
CREATE TABLE region ( R_REGIONKEY INTEGER primary key,
R_NAME CHAR(25) NOT NULL,
R_COMMENT VARCHAR(152));
CREATE TABLE part ( P_PARTKEY INTEGER primary key,
P_NAME VARCHAR(55) NOT NULL,
P_MFGR CHAR(25) NOT NULL,
P_BRAND CHAR(10) NOT NULL,
P_TYPE VARCHAR(25) NOT NULL,
P_SIZE INTEGER NOT NULL,
P_CONTAINER CHAR(10) NOT NULL,
P_RETAILPRICE DECIMAL(15,2) NOT NULL,
P_COMMENT VARCHAR(23) NOT NULL );
CREATE TABLE supplier ( S_SUPPKEY INTEGER primary key,
S_NAME CHAR(25) NOT NULL,
S_ADDRESS VARCHAR(40) NOT NULL,
S_NATIONKEY INTEGER NOT NULL,
S_PHONE CHAR(15) NOT NULL,
S_ACCTBAL DECIMAL(15,2) NOT NULL,
S_COMMENT VARCHAR(101) NOT NULL);
CREATE TABLE partsupp ( PS_PARTKEY INTEGER NOT NULL,
PS_SUPPKEY INTEGER NOT NULL,
PS_AVAILQTY INTEGER NOT NULL,
PS_SUPPLYCOST DECIMAL(15,2) NOT NULL,
PS_COMMENT VARCHAR(199) NOT NULL, primary key (ps_partkey, ps_suppkey) );
CREATE TABLE customer ( C_CUSTKEY INTEGER primary key,
C_NAME VARCHAR(25) NOT NULL,
C_ADDRESS VARCHAR(40) NOT NULL,
C_NATIONKEY INTEGER NOT NULL,
C_PHONE CHAR(15) NOT NULL,
C_ACCTBAL DECIMAL(15,2) NOT NULL,
C_MKTSEGMENT CHAR(10) NOT NULL,
C_COMMENT VARCHAR(117) NOT NULL);
CREATE TABLE orders ( O_ORDERKEY INTEGER primary key,
O_CUSTKEY INTEGER NOT NULL,
O_ORDERSTATUS CHAR(1) NOT NULL,
O_TOTALPRICE DECIMAL(15,2) NOT NULL,
O_ORDERDATE DATE NOT NULL,
O_ORDERPRIORITY CHAR(15) NOT NULL,
O_CLERK CHAR(15) NOT NULL,
O_SHIPPRIORITY INTEGER NOT NULL,
O_COMMENT VARCHAR(79) NOT NULL);
CREATE TABLE lineitem ( L_ORDERKEY INTEGER NOT NULL,
L_PARTKEY INTEGER NOT NULL,
L_SUPPKEY INTEGER NOT NULL,
L_LINENUMBER INTEGER NOT NULL,
L_QUANTITY DECIMAL(15,2) NOT NULL,
L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
L_DISCOUNT DECIMAL(15,2) NOT NULL,
L_TAX DECIMAL(15,2) NOT NULL,
L_RETURNFLAG CHAR(1) NOT NULL,
L_LINESTATUS CHAR(1) NOT NULL,
L_SHIPDATE DATE NOT NULL,
L_COMMITDATE DATE NOT NULL,
L_RECEIPTDATE DATE NOT NULL,
L_SHIPINSTRUCT CHAR(25) NOT NULL,
L_SHIPMODE CHAR(10) NOT NULL,
L_COMMENT VARCHAR(44) NOT NULL,
primary key(L_ORDERKEY,L_LINENUMBER));
tpchデータベースとテーブルが作成されたことを確認します。
show tables; を実行して、tpchデータベースに８つのテーブルが作成されたことが確認できました。
オブジェクトストレージからデータのインポート
MySQLシェル 8.0.17 よりパラレルテーブルインポート・ユーティリティが提供され、OCIオブジェクトストレージにも対応しています。
パラレルテーブルインポート・ユーティリティとは
"MySQLシェルのパラレルテーブルインポートユーティリティは、入力データファイルを分析し、それをチャンクに分散し、並列接続を使用してターゲットMySQLサーバーにチャンクをアップロードします。LOAD DATAステートメントを使用した標準のシングルスレッドアップロードよりも何倍も速く大量のデータのインポートを完了することができます。" ～※パラレルテーブルインポート・ユーティリティのドキュメントから引用～
今回はこのユーティリティを使い高速インポートを実行します。
オブジェクトストレージでの利用条件
HeatWave Quickstart ドキュメントから飛べるページに以下の利用条件があるので、事前にチェックしておきます。
-
Oracle Cloud Infrastructure Object Storageバケット（MySQL Shell 8.0.21から）
- MySQLシェル 8.0.26 --- OK!
-
URLで指定されたHTTPまたはHTTPSを介してクライアントからアクセスできるリモートロケーション。
- オブジェクトストレージに踏み台サーバ、またはWebブラウザからアクセスできるかを確認します。例）nation.tbl の場合
- 踏み台からのアクセス
- 例）wget -XGET https://objectstorage.ap-osaka-1.oraclecloud.com/p/IYpunZLMQC1yg8Qi9_QER16TDg35VhsIcjET5H3ZpUi7XKVTI5Q1fM11klXYnp2e/n/orasejapan/b/bucket-tpch/o/nation.tbl --- OK!
- Webブラウザからのアクセス
- 例) https://objectstorage.ap-osaka-1.oraclecloud.com/p/IYpunZLMQC1yg8Qi9_QER16TDg35VhsIcjET5H3ZpUi7XKVTI5Q1fM11klXYnp2e/n/orasejapan/b/bucket-tpch/o/nation.tbl --- OK!
-
パラレルテーブルインポート・ユーティリティはLOAD DATA LOCAL INFILEステートメントを使用してデータをアップロードするため 、MDSサーバーでlocal_infileシステム変数をONにする必要があります。
- MDS に接続し、SHOW VARIABLES; を実行したところ、local_infile = ON であることを確認 --- OK!
- MDS に接続し、SHOW VARIABLES; を実行したところ、local_infile = ON であることを確認 --- OK!
パラレルテーブルインポート・ユーティリティの実行
パラレルテーブルインポート・ユーティリティは、MySQLシェルのJavaScript実行モードで util.ImportTable()コマンドで実行します。
書式例）
util.importTable("インポートファイル", {schema: "MDSのデータベース名", table: "MDSのテーブル名", fieldsTerminatedBy:"|", threads:16, bytesPerChunk: "100M", skipRows:1, showProgress: true})
各指定オプションについては、ドキュメント等でご確認ください。
次の８つのテーブルを順次インポートしますが、ここでは最初の custoer テーブルと、データ量が最大の lineitem テーブルの実行結果について取り上げます。
show tables;
+----------------+
| Tables_in_tpch |
+----------------+
| customer |
| lineitem |
| nation |
| orders |
| part |
| partsupp |
| region |
| supplier |
+----------------+
customer データのインポート
オブジェクトストレージの customer.tbl を指摘して、customerテーブルへのインポートを実行
実際のコマンド例：customer の場合：
util.importTable("https://objectstorage.ap-osaka-1.oraclecloud.com/p/IYpunZLMQC1yg8Qi9_QER16TDg35VhsIcjET5H3ZpUi7XKVTI5Q1fM11klXYnp2e/n/orasejapan/b/bucket-tpch/o/customer.tbl", {schema: "tpch", table: "customer", fieldsTerminatedBy:"|", threads:16, bytesPerChunk: "100M", skipRows:1, showProgress: true})
実行結果(customer)
約245MBのデータ(行数：1,499,999)が約5.1秒で正常にインポートされました。
lineitem データのインポート
続いて、約8GBの lineitem.tbl データの lineitemテーブルへのインポートを実行します。
util.importTable("https://objectstorage.ap-osaka-1.oraclecloud.com/p/IYpunZLMQC1yg8Qi9_QER16TDg35VhsIcjET5H3ZpUi7XKVTI5Q1fM11klXYnp2e/n/orasejapan/b/bucket-tpch/o/lineitem.tbl", {schema: "tpch", table: "lineitem", fieldsTerminatedBy:"|", threads:16, bytesPerChunk: "100M", skipRows:1, showProgress: true})
実行結果(lineitem)
約8GBのlineitemデータ(行数：59,986,051)が約66秒で正常にインポートされたことが確認できました。
残り６つのテーブルも同様に正常にインポートでき、TPC-HデータをMDSへ無事格納することができました。
例）nation テーブルからのデータ抽出
MySQL tpch SQL > select * from nation order by N_NATIONKEY limit 10;
+-------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------+
| N_NATIONKEY | N_NAME | N_REGIONKEY | N_COMMENT |
+-------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------+
| 1 | ARGENTINA | 1 | al foxes promise slyly according to the regular accounts. bold requests alon |
| 2 | BRAZIL | 1 | y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special |
| 3 | CANADA | 1 | eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold |
| 4 | EGYPT | 4 | y above the carefully unusual theodolites. final dugouts are quickly across the furiously regular d |
| 5 | ETHIOPIA | 0 | ven packages wake quickly. regu |
| 6 | FRANCE | 3 | refully final requests. regular, ironi |
| 7 | GERMANY | 3 | l platelets. regular accounts x-ray: unusual, regular acco |
| 8 | INDIA | 2 | ss excuses cajole slyly across the packages. deposits print aroun |
| 9 | INDONESIA | 2 | slyly express asymptotes. regular deposits haggle slyly. carefully ironic hockey players sleep blithely. carefull |
| 10 | IRAN | 4 | efully alongside of the slyly final dependencies. |
+-------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.0009 sec)
まとめ
オブジェクトストレージへの大量データのアップロードにはマルチパート・アップロードがとても有効だと実感できました。
MySQLシェルからパラレルテーブルインポート・ユーティリティを使ってオブジェクトストレージの大量データを高速にインポートできました。MySQLシェルとOCIオブジェクトストレージの親和性の高さが伺えました。
HeatWave用のMDSにTPC-Hデータが準備できたので、次回はHeatWaveでの性能比較を予定してます。
参考URL
[OCI]MySQL Shellを使用してMySQL DBシステムから直接Object StorageにExportして、MySQL DBシステム作成時にImportしてみた