HammerDBをCLIで使うなど（８）：PostgreSQLにTPC-Hを実行してみる

基本的な流れ

前回の「MySQLにTPC-H」とほぼ一緒です。

淡々と進めていきましょう。

対象DBMSとベンチマークの選択

hammerdb>dbset db pg
Database set to PostgreSQL

hammerdb>dbset bm TPC-H
Benchmark set to TPC-H for PostgreSQL

詳細パラメータの表示と設定

hammerdb>print dict
Dictionary Settings for PostgreSQL
connection {
 pg_host = localhost
 pg_port = 5432
}
tpch       {
 pg_scale_fact         = 1
 pg_tpch_superuser     = postgres
 pg_tpch_superuserpass = postgres
 pg_tpch_defaultdbase  = postgres
 pg_tpch_user          = tpch
 pg_tpch_pass          = tpch
 pg_tpch_dbase         = tpch
 pg_tpch_gpcompat      = false
 pg_tpch_gpcompress    = false
 pg_num_tpch_threads   = 1
 pg_total_querysets    = 1
 pg_raise_query_error  = false
 pg_verbose            = false
 pg_refresh_on         = false
 pg_degree_of_parallel = 2
 pg_update_sets        = 1
 pg_trickle_refresh    = 1000
 pg_refresh_verbose    = false
 pg_cloud_query        = false
 pg_rs_compat          = false
}

TPC-Cと、だいぶパラメータが変わっています。
GUIのメニュールートと微妙にマッチしてないので、GUIベースでしかパラメータの解説がない公式ドキュメントとの対応関係を見るのが少し大変でした。

tpchパラメータの意味は以下のとおりです。

名称	説明	初期値
pg_scale_fact	データのサイズです。	1
pg_tpch_superuser	接続先PostgreSQLデータベースのスーパーユーザー。	postgres
pg_tpch_superuserpass	接続先PostgreSQLデータベースのスーパーユーザーのパスワード。	postgres
pg_tpch_defaultdbase	接続先PostgreSQLデータベースのデフォルトデータベース。	postgres
pg_tpch_user	テストに使用するユーザー。自動で作られるため、先に存在している必要はない。	tpch
pg_tpch_pass	テストに使用するユーザーのパスワード。	tpch
pg_tpch_dbase	テストに使用するデータベース名。	tpch
pg_tpch_gpcompat	Greenplum対応オプションでスキーマを作成する。	false
pg_tpch_gpcompress	pg_tpch_gpcompat=trueのときに、圧縮形式で列を作成するスイッチ。	false
pg_num_tpch_threads	テストスキーマ構築を実行するユーザー数。	1
pg_total_querysets	テストユーザーが１回ログインして、TPC-Hの1セット（22クエリ）を何回反復実行するか。	1
pg_raise_query_error	エラー発生時にテスト全体を中止する（true）か、次のクエリを継続するか（false）。	false
pg_verbose	実行したクエリ自体も出力するモードのスイッチ。結果行も出るのですごい量になる。	false
pg_refresh_on	trueにするとTPC-Hのクセリセット実行ではなく、更新処理を実行する。	false
pg_degree_of_parallel	パラレルクエリクエリの並列度指定。max_parallel_workers_per_gather。	2
pg_update_sets	refresh_onがtrueのとき、更新処理を何回実行するか。	1
pg_trickle_refresh	refresh_onがtrueのとき、更新処理における挿入と削除の間隔を何ミリ秒あけるか。	1000
pg_refresh_verbose	refresh_onがtrueのときに更新処理を実行するユーザーの処理内容を出力するモードのスイッチ。	false
pg_cloud_query	TPC-H規定の22本のクエリセットとは別の13のクエリが実行されるモードのスイッチ。	false
pg_rs_compat	（ドキュメントに説明がない）Redshift対応。	false

上記は抜粋超訳なので、正確な解説を見たい場合に参照してください。
3. Configuring Schema Build Options

5. Configuring Driver Script Options

基本的にはMySQLと同じですが、PostgreSQLのSQLをおしゃべりできるDBとしてGreenplumやRedshiftが存在していて、それらに対応するためのパラメータがありますね。

また、PostgreSQLはパラレルクエリに対応しているため、その並列度を調整するパラメータがあります。
このパラレルクエリ系のパラメータは、PostgreSQLの他、Oracle、db2へのTPC-Hのときに設定が可能です。

公式ドキュメントにパラメータについての解説がない「pg_rs_compat」ですが、ソースを雑に追った限りだと、「pg_degree_of_parallel＝PostgreSQLのmax_parallel_workers_per_gather」を設定するコードを迂回するためのフラグのようです。特別にRedshiftらしい設定や処理を入れているようには読めませんでした。Greenplumは圧縮対応とかしているのにね。

一方、上記の表では端折りましたが、Greenplumのオプションの解説のところで

Building the schema by inserting into Greenplum is not recommended and instead a bulk load of data created with the datagen option should be used.

と、このツールでデータ突っ込むの非推奨、別途生成したデータから取り込んだほうがいいよ、って話が出ています。
このテスト用データだけをパイプ区切りファイルに出力する「datagen」については、別の回で説明します。

前回のMySQL TPC-Hのときも触れた「cloud query」の件、ここでも同じです。
なおAurora PostgreSQLにもRedshiftにも、13本のcloud queryではないフツウの22本のTPC-Hクエリも実行できます。

接続先を環境と合わせる以外は、特にオプションをいじらずに進めます。

スキーマ作成

テストで流す前に必要なスキーマ作成、及び、テストデータの投入を実行します。

hammerdb>buildschema
....
ALL VIRTUAL USERS COMPLETE

処理が完了しても、データ生成のために立ち上がったクライアントプロセスは起動したままになります。
次の処理の前に、スキーマ作成用のユーザーセッションが完了しているか確認するコマンドを投げて確認し、完了ステータスになっていたら、そのセッションは一度破棄しておきます。

hammerdb>vustatus
1 = FINISH SUCCESS

hammerdb>vudestroy
Destroying Virtual Users
Virtual Users Destroyed
vudestroy success

hammerdb>vustatus
No Virtual Users found

テストスクリプトのロード

hammerdb>loadscript
Script loaded, Type "print script" to view

テスト実行用クライアント（Virtual User）の設定

ワークロードを実行するために接続する同時実行ユーザー数を確認・調整します。

今回も、こんな設定で。

hammerdb>print vuconf
Virtual Users = 1
User Delay(ms) = 500
Repeat Delay(ms) = 500
Iterations = 1
Show Output = 1
Log Output = 0
Unique Log Name = 0
No Log Buffer = 0
Log Timestamps = 0

hammerdb>vuset
Usage: vuset [vu|delay|repeat|iterations|showoutput|logtotemp|unique|nobuff|timestamps] value

hammerdb>vuset logtotemp 1

hammerdb>vuset unique 1

hammerdb>print vuconf
Virtual Users = 1
User Delay(ms) = 500
Repeat Delay(ms) = 500
Iterations = 1
Show Output = 1
Log Output = 1
Unique Log Name = 1
No Log Buffer = 0
Log Timestamps = 0

テスト実行用クライアント（Virtual user）の起動

設定に従ってクライアント（Virtual user）を起動します

hammerdb>vucreate
Vuser 1 created - WAIT IDLE
Logging activated
to /tmp/hammerdb_5D6DEA05591903E243735383.log
1 Virtual Users Created

テストの実行

hammerdb>vurun

で実行し、終わるまで待ちます。

実行結果の出力例

１セット２２本のSQLの実行結果なので、省略せずに見ていきましょう。

hammerdb>vurun
RUNNING - PostgreSQL TPC-H
Vuser 1:RUNNING
Vuser 1:Executing Query 14 (1 of 22)

Vuser 1:query 14 completed in 0.73 seconds
Vuser 1:Executing Query 2 (2 of 22)
Vuser 1:query 2 completed in 1.4 seconds
Vuser 1:Executing Query 9 (3 of 22)
Vuser 1:query 9 completed in 13.771 seconds
Vuser 1:Executing Query 20 (4 of 22)
Vuser 1:query 20 completed in 0.782 seconds
Vuser 1:Executing Query 6 (5 of 22)
Vuser 1:query 6 completed in 3.064 seconds
Vuser 1:Executing Query 17 (6 of 22)
Vuser 1:query 17 completed in 4.531 seconds
Vuser 1:Executing Query 18 (7 of 22)
Vuser 1:query 18 completed in 29.776 seconds
Vuser 1:Executing Query 8 (8 of 22)
Vuser 1:query 8 completed in 1.636 seconds
Vuser 1:Executing Query 21 (9 of 22)
Vuser 1:query 21 completed in 8.142 seconds
Vuser 1:Executing Query 13 (10 of 22)
Vuser 1:query 13 completed in 10.706 seconds
Vuser 1:Executing Query 3 (11 of 22)
Vuser 1:query 3 completed in 4.255 seconds
Vuser 1:Executing Query 22 (12 of 22)
Vuser 1:query 22 completed in 0.781 seconds
Vuser 1:Executing Query 16 (13 of 22)
Vuser 1:query 16 completed in 3.396 seconds
Vuser 1:Executing Query 4 (14 of 22)
Vuser 1:query 4 completed in 1.554 seconds
Vuser 1:Executing Query 11 (15 of 22)
Vuser 1:query 11 completed in 0.396 seconds
Vuser 1:Executing Query 15 (16 of 22)
Vuser 1:query 15 completed in 8.857 seconds
Vuser 1:Executing Query 1 (17 of 22)
Vuser 1:query 1 completed in 30.982 seconds
Vuser 1:Executing Query 10 (18 of 22)
Vuser 1:query 10 completed in 3.997 seconds
Vuser 1:Executing Query 19 (19 of 22)
Vuser 1:query 19 completed in 0.427 seconds
Vuser 1:Executing Query 5 (20 of 22)
Vuser 1:query 5 completed in 4.884 seconds
Vuser 1:Executing Query 7 (21 of 22)
Vuser 1:query 7 completed in 4.433 seconds
Vuser 1:Executing Query 12 (22 of 22)
Vuser 1:query 12 completed in 4.178 seconds
Vuser 1:Completed 1 query set(s) in 142 seconds
Vuser 1:FINISHED SUCCESS
ALL VIRTUAL USERS COMPLETE
                          TPC-H Driver Script

表示の内容は、MySQL用と同じです。

実はコレ、言われたとおりに実際に手を動かしていくと躓くんです。

Vuser 1:Executing Query 20 (4 of 22)

でSQLが終わらなくなります。

ていうか、デフォ（HammerDBのパラメータも、DB側のサーバーパラメータもデフォ）で動かしたとき、分析系SQLが苦手と言われているMySQLより、全般的に遅いんです。

なぜか。。。

作成されるインデックスが、MySQL用と異なっているからー！

例えば、TPC-Hで一番大きいテーブル「LINEITEM」には、以下のようにPARTSUPPテーブルを参照する外部キー制約が付与されています。

CONSTRAINT lineitem_partsupp_fk FOREIGN KEY (l_partkey, l_suppkey)
        REFERENCES public.partsupp (ps_partkey, ps_suppkey)

PostgreSQLでは

外部キーは主キーであるかまたは一意性制約を構成する列を参照しなければなりません。 これは、被参照列は常に(主キーまたは一意性制約の基礎となる)インデックスを持つことを意味します。 このため、参照行に一致する行があるかどうかのチェックは効率的です。 被参照テーブルからの行のDELETEや被参照行のUPDATEは、古い値と一致する行に対して参照テーブルのスキャンが必要となるので、参照行にもインデックスを付けるのは大抵は良い考えです。 これは常に必要という訳ではなく、また、インデックスの方法には多くの選択肢がありますので、外部キー制約の宣言では参照列のインデックスが自動的に作られるということはありません。
5.3. 制約

ということで、PARTSUPPテーブルの(ps_partkey, ps_suppkey列には主キー制約が適用されている＝インデックスも存在するものの、LINEITEMのl_partkey, l_suppkey列には、インデックスがない状態です。

SQL-20では、LINEITEMテーブルのl_partkey, l_suppkeyついて、PARTSUPPテーブルのps_partkey, ps_suppkeyに対するFKが張ってあり、これを使って結合されているのですが、l_partkey, l_suppkeyに自体にはインデックスが張ってないんで、フルスキャン（SeqScan）になっています。

そして、この外部参照の関係にある列で結合して絞り込んだ結果を集約関数で処理するサブクエリの実行コストが異常に大きな値となります。

MySQLだと、外部キー制約の宣言で、自動的にインデックスが作成されます。

MySQL では、外部キーチェックを高速に実行でき、かつテーブルスキャンが必要なくなるように、外部キーおよび参照されるキーに関するインデックスが必要です。参照しているテーブルには、外部キーカラムが同じ順序で最初のカラムとしてリストされているインデックスが存在する必要があります。このようなインデックスが存在しない場合は、参照しているテーブル上に自動的に作成されます。このインデックスは、外部キー制約を適用するために使用できる別のインデックスを作成した場合、あとで暗黙のうちに削除される可能性があります。index_name (指定されている場合) は、前に説明したとおりに使用されます。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.17.2 外部キー制約の使用

そんな事情ですので、外部キー制約をしている列など、後付でインデックスを付けてあげましょう。
MySQLに対して作成したテーブル情報から、以下のインデックスの有無が差となっています。
（外部キー関係ないやつもいそう。。。）

CREATE INDEX ON customer (C_NATIONKEY);
CREATE INDEX ON lineitem (L_ORDERKEY);
CREATE INDEX ON lineitem (L_SUPPKEY);
CREATE INDEX ON lineitem (L_PARTKEY,L_SUPPKEY);
CREATE INDEX ON nation (N_REGIONKEY);
CREATE INDEX ON orders (O_CUSTKEY);
CREATE INDEX ON partsupp (PS_PARTKEY);
CREATE INDEX ON partsupp (PS_SUPPKEY);
CREATE INDEX ON supplier (S_NATIONKEY);
CREATE INDEX ON lineitem (L_SHIPDATE);
CREATE INDEX ON lineitem (L_COMMITDATE);
CREATE INDEX ON lineitem (L_RECEIPTDATE);
CREATE INDEX ON orders (O_ORDERDATE);

付与前後で、処理時間が大きく変わるのはもちろん、実行計画を比較するとよりはっきりと違いがわかると思います。

まとめ

• PostgreSQLへのTPC-Hも、MySQLとほぼ同じ。
• パラレルクエリの並列度調整に対応している。
• Greenplumの列圧縮を有効にするオプションがある。
• Redshiftに対応すべく、パラレルクエリの並列度調整コマンドが走らないようにするオプションがある。
• PostgreSQLは外部キー制約を付けても、参照元にインデックスは作られない。少なくともTPC-Hクエリにおいては、ここにインデックスを作成するのが有効だし、MySQLは自動でインデックスが作成される。

DBの細かな違い、こんなところに影響してくるんですね。

WEB+DB PRESS Vol.112