データベースベンチマークTPC-HをMySQLで実行する(TPC-H v2.18.0)

# TL;DR

* RDBベンチマーク[TPC-H](http://www.tpc.org/tpch/default.asp)を実行し，動作を確認します
    * TPC-Hは複数のテーブルからなるデータベースで，22種類のクエリからなる，ベンチマークスイートです
    * データの選択量が多く，テーブルの結合処理を含むので，トランザクション性能というよりは，データ分析に要する処理の性能評価に利用されるものです
    * [TPC-H仕様(pdf)](http://www.tpc.org/TPC_Documents_Current_Versions/pdf/tpc-h_v2.18.0.pdf)が詳しいので，参考になります
* AWSのEC2インスタンスを使用しますが，MySQLのデータベースがあれば，環境に依存しません

# 実験概要

1. クエリを発行するノードとして，EC2のt2.smallインスタンスを使用します
    1. ベンチマーク実行用のインスタンスです
1. TPC-Hツールをダウンロードし，MySQL用のデータセット，クエリを作成します
1. いくつかのクエリを実行し，動作することを確認します

# 0. TPC-Hベンチマークの実行手順

ベンチマークの実行は，以下の手続きで実施します．

1. TPC-Hのデータとクエリを生成するツールのダウンロード
1. MySQL向けパッチの適用
1. テーブルの作成
1. データの生成
1. データの登録
1. インデックスの作成
1. 実行クエリの生成
1. クエリを実行して性能計測

## ホストのセットアップ

```bash
yum groupinstall -y 'Development Tools'
yum install -y mariadb
```


# 1. TPC-Hツールのダウンロード

* [TPC-H_Tools](http://www.tpc.org/tpch/)の[Download]から，TPC-Toolsを入手します
    * 入力するメールアドレスにダウンロードキー付きのURLが送付されるので，それをクリックしてダウンロードします
    * TPC-H_Tools_v2.18.0を入手した(プレフィックス文字列が付いているかもしれない（以降はtpc-h-tools.zipとして表記する)
    * scpコマンドなどで，EC2のVMにtpc-h-tools.zipをコピーします

# 2. MySQL向けのパッチの適用

[MySQLでTPC-Hベンチマークを実行する](http://itiut.hatenablog.com/entry/2014/06/24/050917)で，TPC-Hのデータ（生成用のddlファイル）とクエリをMySQLで実行できるようにするパッチが作成されています（感謝！）．

その一方で，上記記事が公開されてから，TPCツールのダウンロード方法が変わっているので，パッチ適用で不都合が生じます．そこで，[tpch-patches](https://github.com/itiut/tpch-patches)を活用する上で，さらに以下のような修正を行い,現在のバージョンに対応したものへ修正します．

* 適当なディレクトリを作成してtpc-h-tool.zipを配置し，パッチをgit cloneする

```bash
mkdir -p ~/{src,bin,share}
cd ~/src
git clone https://github.com/itiut/tpch-patches
cp ~/tpc-h-tool.zip ~/src/
```

* tpch-patches/install.shを以下のようにパッチする

```patch
diff --git a/install.sh b/install.sh
index 9d81955..bb0b6f5 100755
--- a/install.sh
+++ b/install.sh
@@ -22,7 +22,8 @@ esac

 BASE_DIR=$PWD
 DATABASE=$1
-TPCH_PROG=tpch_2_17_0
+TPCH_PROG=tpc-h-tool
+TPCH_VER=2.18.0_rc2
 : ${PREFIX:=$HOME}
 PREFIX=$(readlink -f $PREFIX)
 BIN_DIR=$PREFIX/bin
@@ -40,14 +41,11 @@ ensure_directories() {

 download_and_extract_dbgen() {
     pushd $SRC_DIR
-    if [ ! -f $TPCH_PROG.zip ]; then
-        echo "Download $TPCH_PROG.zip ..."
-        curl -LO http://www.tpc.org/tpch/spec/$TPCH_PROG.zip
-    fi
     if [ ! -d $TPCH_PROG ]; then
         echo "Extract $TPCH_PROG.zip ..."
-        unzip $TPCH_PROG
+        unzip $TPCH_PROG.zip
         rm -rf __MACOSX
+       mv $TPCH_VER $SRC_DIR/$TPCH_PROG
     fi
     popd
 }
```

* 上記の修正でやっていること
    * TPC-Hツールを自動ダウンロードしない（別途，ダウンロードしている）
    * 展開後のディレクトリ名を(2.18.0用に)修正

* tpch-patches/install.shを実行して，{データ,クエリ}生成プログラムを生成する

```bash
./install.sh mysql
```

# 3. テーブルの作成(スキーマの登録)

* ```sf1``` というデータベースを作ります
    * 名前に制約はないですが，別の名前を指定した場合，以降のコマンドの ```-D``` オプションの文字列を変更してください

```bash
cd ~/share/dbgen
mysql -u [ユーザ名] -h [mysqlホスト] -p -e "create database sf1;";
mysql -u [ユーザ名] -h [mysqlホスト] -p -D sf1 < dss.ddl
```

* [ユーザ名][mysqlホスト]などは，環境に合わせて設定してください
    * ローカルホストのサーバで実験するのであれば，[mysqlホスト]の指定は不要です


# 4. データの作成

* ベンチマーク用にデータを作成します
    * dbgenコマンドでデータを生成します
    * ```-s``` オプションで，ScaleFactorを指定します
        * ScaleFactorは，データの量を示す数値(GB)です
    * TPC-Hで使用されるテーブルが，tblファイルとして作られます
    * lineitem.tblが最も大きなテーブルです

```bash
cd ~/bin
./dbgen -s 1
mkdir ~/bin/sf1
mv *.tbl ~/bin/sf1
```

# 5. テーブルへデータの登録

* ```dbgen``` で生成したデータ（テーブルごとに ```.tbl``` 拡張子でまとめられたテキストファイル）を，mysqlコマンドを使用して，auroraのデータベースsf1にロードします

```bash
cd ~/bin/sf1
for tbl_file in *.tbl; do
mysql -u [ユーザ名] -h [mysqlホスト] -p[パスワード] -D sf1 -e \
"LOAD DATA LOCAL INFILE '$tbl_file' INTO TABLE $(basename $tbl_file .tbl) FIELDS TERMINATED by '|' LINES TERMINATED BY '\n';";
done
```

# 6. インデックスの作成

* ScaleFactorごとに，インデックス指定ファイル( ```.ri``` )を作成し，テーブルのインデックスを作成します．

```bash
cd ~/share/dbgen
sed -e 's/tpch/sf1/g' dss.ri > dss.ri.sf1
mysql -u [ユーザ名] -h [mysqlホスト] -p -D sf1 < dss.ri.sf1
```

# 7. クエリファイルの作成

* ```qgen``` コマンドを使って，TPC-Hのクエリファイルを生成します．
    * ScaleFactorごとに異なるクエリが必要なので， ```-s``` オプションを使って指定してください

```bash
cd ~/bin
mkdir -p ~/bin/sf1query
for i in $(seq 1 22); do ~/bin/qgen -s 1 $i > $i.sql; done
mv *.sql ~/bin/sf1query
```

# 8. クエリの実行

* mysqlコマンドでクエリファイルを入力し，クエリを実行します．以下のコマンドは，Query1を実行した場合の例です．

```bash
time mysql -u [ユーザ名] -h [mysqlホスト] -p[パスワード] -D sf1 < ~/bin/sf1query/1.sql

l_returnflag    l_linestatus    sum_qty sum_base_price  sum_disc_price  sum_charge      avg_qty a
vg_price       avg_disc        count_order
A       F       37734107.00     56586554400.73  53758257134.8700        55909065222.827692      25.522006       38273.129735    0.049985        1478493
N       F       991417.00       1487504710.38   1413082168.0541 1469649223.194375       25.516472       38284.467761    0.050093        38854
N       O       72798693.00     109186056038.16 103727910277.8472       107880806426.511496     25.501757       38248.437828    0.050000        2854654
R       F       37719753.00     56568041380.90  53741292684.6040        55889619119.831932      25.505794       38250.854626    0.050009        1478870

real    0m11.331s
user    0m0.005s
sys     0m0.000s
```

# 参考

* [TPC-H](http://www.tpc.org/tpch/default.asp)
* [部屋の中にも一年 MySQLでTPC-Hベンチマークを実行する](http://itiut.hatenablog.com/entry/2014/06/24/050917)
* [github - tpch-patches](https://github.com/itiut/tpch-patches)