Go to Qiita Advent Calendar 2024 Top
LoginSignup
3
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@cocotaro0202(Cocotaro0202)

[OCI] MySQLデータベースサービス(MDS) へオブジェクトストレージからTPC-Hデータをインポートしてみる

Last updated at Posted at 2021-12-16

はじめに

最終的なゴールとしてはMDS HeatWave のTPC-H性能比較テストを目指しているものの、今回はTPC-H性能比較テストのための準備編として主に以下の作業を実施しました。

  • TPC-H性能比較テストのためのデータを準備する

  • TPC-Hデータをオブジェクトストレージに格納する

  • 踏み台サーバーにMySQL Shell をインストールする

  • HeatWave専用のMDSを作成する

  • MDSにTPC-Hデータをインポートする

お試し環境

image-20211206165829181.png

前提

  • 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 コマンド を実行します。
image-20211207164648379.png

マルチパート・アップロード実行

例えば、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"
}

残りの各データも同様にアップロードし、８つ全てのデータのアップロードが短期間で完了しました。
image-20211207165315083.png

HeatWave用のMDSの作成

MDSの作成

OCIにサインイン後、左上のナビゲーションメニューから[データベース]-[MySQL]を選択し、MySQL DBシステムの作成 をクリックします。
image-20211208092413417.png
[コンパートメントに作成] 任意のコンパートメントを指定します。
[名前] 任意の名前を指定します。例）mds-heatwave4tpcs
[HeatWave] HeatWaveを選択します。
管理者資格証明の作成
[ユーザー名] adminとします。
[パスワード] 任意のパスワードを指定します。
image-20211208092731990.png
ネットワーキングの構成
[仮想クラウド・ネットワーク] 既存の仮想クラウド・ネットワークを選択。例）osaka-vcn
[サブネット] プライベート・サブネットを選択します。
以下はすべてデフォルト設定のまま。作成 をクリックします。
image-20211208092911566.png
約７～８分後にMDSが作成されました。
image-20211208092731990.png

MDSへTPC-Hデータをインポート

踏み台サーバーへ接続

踏み台サーバーへ接続します。

ssh -i ~/.ssh/id_rsa opc@168.138.44.xxx

MySQL ShellからMDSへ接続

踏み台サーバーでMDSのエンドポイントを指定して、MySQL Shellを起動しMDSに接続します。
MDSのエンドポイントは、リソースから参照することもできますし、詳細画面からコピーすることも可能です。
image-20211208102345861.png

次の書式で接続します。

mysqlsh --mysql admin@192.168.100.86

パスワードを聞かれるので、MDS作成時に定義したパスワードを入力します。次のようにMySQLシェルが起動されました。MySQLシェルはデフォルトでJavaScript実行モードで開きます。
image-20211208102157900.png
MySQLシェルの実行モードをJavaScriptからSQLに変更します。
image-20211208103146272.png
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データベースに８つのテーブルが作成されたことが確認できました。
image-20211208103947472.png

オブジェクトストレージからデータのインポート

MySQLシェル 8.0.17 よりパラレルテーブルインポート・ユーティリティが提供され、OCIオブジェクトストレージにも対応しています。

パラレルテーブルインポート・ユーティリティとは

"MySQLシェルのパラレルテーブルインポートユーティリティは、入力データファイルを分析し、それをチャンクに分散し、並列接続を使用してターゲットMySQLサーバーにチャンクをアップロードします。LOAD DATAステートメントを使用した標準のシングルスレッドアップロードよりも何倍も速く大量のデータのインポートを完了することができます。" ～※パラレルテーブルインポート・ユーティリティのドキュメントから引用～

今回はこのユーティリティを使い高速インポートを実行します。

オブジェクトストレージでの利用条件

HeatWave Quickstart ドキュメントから飛べるページに以下の利用条件があるので、事前にチェックしておきます。

パラレルテーブルインポート・ユーティリティの実行

パラレルテーブルインポート・ユーティリティは、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秒で正常にインポートされました。
image-20211210174015274.png

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秒で正常にインポートされたことが確認できました。
image-20211215171040767.png
残り６つのテーブルも同様に正常にインポートでき、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

HeatWave Quickstart

[OCI]MySQL Shellを使用してMySQL DBシステムから直接Object StorageにExportして、MySQL DBシステム作成時にImportしてみた

MySQL Shell の Parallel Table Import Utility を使ってみる

3
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?