なからなLife

geekに憧れと敬意を抱きながら、SE、ITコンサル、商品企画、事業企画、管理会計、総務・情シス、再び受託でDB屋さんと流浪する人のブログです。

HammerDBをCLIで使うなど(9):OracleにTPC-Hを実行してみる

基本的な流れ

MySQLTPC-H」「PostgreSQLTPC-H」とほぼ一緒です。


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

hammerdb>dbset db ora
Database set to Oracle

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

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

hammerdb>print dict
Dictionary Settings for Oracle
connection {
 system_user     = system
 system_password = manager
 instance        = oracle
 rac             = 0
}
tpch       {
 scale_fact         = 1
 tpch_user          = tpch
 tpch_pass          = tpch
 tpch_def_tab       = tpchtab
 tpch_def_temp      = temp
 num_tpch_threads   = 1
 tpch_tt_compat     = false
 total_querysets    = 1
 raise_query_error  = false
 verbose            = false
 degree_of_parallel = 2
 refresh_on         = false
 update_sets        = 1
 trickle_refresh    = 1000
 refresh_verbose    = false
 cloud_query        = false
}


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


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

名称 説明 初期値
scale_fact データのサイズです。 1
tpch_user テストに使用するユーザー。自動で作られるため、先に存在している必要はない。 tpch
tpch_pass テストに使用するユーザーのパスワード。 tpch
tpch_def_tab 接続先oracleデータベースのデフォルト表領域。 tpchtab
tpch_def_temp 接続先oracleデータベースのデフォルト一時表領域。 temp
num_tpch_threads テストスキーマ構築を実行するユーザー数。 1
tpch_tt_compat OracleTimesTenデータソース名を利用する。。 false
total_querysets テストユーザーが1回ログインして、TPC-Hの1セット(22クエリ)を何回反復実行するか。 1
raise_query_error エラー発生時にテスト全体を中止する(true)か、次のクエリを継続するか(false)。 false
verbose 実行したクエリ自体も出力するモードのスイッチ。結果行も出るのですごい量になる。 false
degree_of_parallel パラレルクエリクエリの並列度指定。 2
refresh_on trueにするとTPC-Hのクセリセット実行ではなく、更新処理を実行する。 false
update_sets refresh_onがtrueのとき、更新処理を何回実行するか。 1
trickle_refresh refresh_onがtrueのとき、更新処理における挿入と削除の間隔を何ミリ秒あけるか。 1000
refresh_verbose refresh_onがtrueのときに更新処理を実行するユーザーの処理内容を出力するモードのスイッチ。 false
cloud_query TPC-H規定の22本のクエリセットとは別の13のクエリが実行されるモードのスイッチ。 false

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




接続系、表領域などの話は、TPC-Cのときと同じです。指定する表領域だけは先にOracleの中に存在するようにしてください。



PostgreSQLと同様、パラレルクエリに対応しているため、その並列度を調整するパラメータがあります。

「cloud query」の件もMySQLPostgreSQLTPC-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

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

ここから先はMySQLと同じですので、さらっと流します。

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

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

実行結果の出力例

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

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

Vuser 1:query 14 completed in 0.394 seconds
Vuser 1:Executing Query 2 (2 of 22)
Vuser 1:query 2 completed in 0.094 seconds
Vuser 1:Executing Query 9 (3 of 22)
Vuser 1:query 9 completed in 2.404 seconds
Vuser 1:Executing Query 20 (4 of 22)
Vuser 1:query 20 completed in 0.529 seconds
Vuser 1:Executing Query 6 (5 of 22)
Vuser 1:query 6 completed in 0.35 seconds
Vuser 1:Executing Query 17 (6 of 22)
Vuser 1:query 17 completed in 0.503 seconds
Vuser 1:Executing Query 18 (7 of 22)
Vuser 1:query 18 completed in 2.307 seconds
Vuser 1:Executing Query 8 (8 of 22)
Vuser 1:query 8 completed in 0.708 seconds
Vuser 1:Executing Query 21 (9 of 22)
Vuser 1:query 21 completed in 1.899 seconds
Vuser 1:Executing Query 13 (10 of 22)
Vuser 1:query 13 completed in 0.938 seconds
Vuser 1:Executing Query 3 (11 of 22)
Vuser 1:query 3 completed in 1.079 seconds
Vuser 1:Executing Query 22 (12 of 22)
Vuser 1:query 22 completed in 0.306 seconds
Vuser 1:Executing Query 16 (13 of 22)
Vuser 1:query 16 completed in 0.797 seconds
Vuser 1:Executing Query 4 (14 of 22)
Vuser 1:query 4 completed in 0.722 seconds
Vuser 1:Executing Query 11 (15 of 22)
Vuser 1:query 11 completed in 0.17 seconds
Vuser 1:Executing Query 15 (16 of 22)
Vuser 1:query 15 completed in 0.408 seconds
Vuser 1:Executing Query 1 (17 of 22)
Vuser 1:query 1 completed in 1.902 seconds
Vuser 1:Executing Query 10 (18 of 22)
Vuser 1:query 10 completed in 1.588 seconds
Vuser 1:Executing Query 19 (19 of 22)
Vuser 1:query 19 completed in 0.844 seconds
Vuser 1:Executing Query 5 (20 of 22)
Vuser 1:query 5 completed in 1.43 seconds
Vuser 1:Executing Query 7 (21 of 22)
Vuser 1:query 7 completed in 0.789 seconds
Vuser 1:Executing Query 12 (22 of 22)
Vuser 1:query 12 completed in 0.656 seconds
Vuser 1:Completed 1 query set(s) in 22 seconds
Vuser 1:FINISHED SUCCESS
ALL VIRTUAL USERS COMPLETE
                          TPC-H Driver Script

こんな感じで、SQLは1~22がランダムに実行されて、それぞれの実行時間と、全体の実行時間が表示されます。
vuが1のまま、複数回実行しても、実行順序は同じだったりしますが、vuを2以上にしたとき、それぞれのvuでの実行順序は異なるようにできています。



さて、前回PostgreSQL編で、MySQLには付いていたインデックスが付いてなくて遅い!って話がありました。
気になって、Oracleに対して作ったテーブルにインデックスがどうやって作成されているか見てみました。


結果、、、、やっぱりPKのインデックスしかないやん。。。

SQL> column INDEX_NAME format a20
SQL> column TABLE_NAME format a20
SQL> select table_name,index_name  from user_indexes order by table_name;

TABLE_NAME           INDEX_NAME
-------------------- --------------------
CUSTOMER             CUSTOMER_PK
LINEITEM             LINEITEM_PK
NATION               NATION_PK
ORDERS               ORDERS_PK
PART                 PART_PK
PARTSUPP             PARTSUPP_PK
REGION               REGION_PK
SUPPLIER             SUPPLIER_PK

8 rows selected.

うーん。


でも、あっさり処理できた。PostgreSQLのときみたいに、返事がこなくなるような重い処理にはならなかった。。。


ドキュメントにあたってみても、

子表で外部キーの索引付けを行うと、次の利点があります。

子表に対する完全表ロックが防止されます。かわりに、データベースは索引に対する行ロックを取得します。

子表の全表スキャンを行う必要がなくなります。例として、ユーザーがdepartments表から部門10のレコードを削除する場合を想定します。employees.department_idが索引付けされていない場合、データベースはemployeesをスキャンして、部門10に従業員が存在するかどうかを確認する必要があります。

って、Oracle11gR2、12cR1、12cR2の「Database概要 -> 5 データ整合性 -> 索引と外部キー」のところに同じ記述があるくらいなので、外部キー制約における参照元へのインデクス作成は自動じゃなくて任意です。



これ、実際にPostgreSQLのときと同じインデックスをつける前後で実行計画をとると、違いが出ます。
SQL-20の実行計画全部は貼り付けませんが、インデックス付ける前は、LINEITEM表へのアクセスは

|  28 |                    JOIN FILTER USE                | :BF0000     |   950K|    18M|  7755   (1)| 00:00:01 |  Q1,01 | PCWP |            |
|  29 |                     PX BLOCK ITERATOR             |             |   950K|    18M|  7755   (1)| 00:00:01 |  Q1,01 | PCWC |            |
|* 30 |                      TABLE ACCESS FULL            | LINEITEM    |   950K|    18M|  7755   (1)| 00:00:01 |  Q1,01 | PCWP |            |

  30 - filter("L_SHIPDATE"<TO_DATE(' 1995-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "L_SHIPDATE">=TO_DATE(' 1994-01-01
              00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND SYS_OP_BLOOM_FILTER(:BF0000,"L_PARTKEY","L_SUPPKEY"))

といったように、テーブルフルスキャンが走りつつ、パラレルクエリ(PX)が動いていて、filterの中には、結合条件に使う「L_PARTKEY","L_SUPPKEY"」も現れています。


これに対し、インデックス付けた後は、

|* 26 |                INDEX RANGE SCAN                      | IDX_L_PARTKEY_L_SUPPKEY |    23 |       |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|* 27 |               TABLE ACCESS BY INDEX ROWID            | LINEITEM                |     1 |    20 |    25   (0)| 00:00:01 |  Q1,00 | PCWP |            |

  26 - access("L_PARTKEY"="PS_PARTKEY" AND "L_SUPPKEY"="PS_SUPPKEY")
  27 - filter("L_SHIPDATE"<TO_DATE(' 1995-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "L_SHIPDATE">=TO_DATE(' 1994-01-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

のように、テーブルフルスキャンが消えてインデックスを使用し、filterから結合条件もなくなっています。

そして、やっぱりインデックス追加したほうが速くなりました。


インデックスなしでもそこそこ速いのは、パラレルクエリの恩恵でしょうか、と思ったのですが、試しにStandard Edition(AWS/RDS db.m5.xlarge)でやったらあっさり処理が終わったので、また別の理由のようですね。



PostgreSQL(手元の環境は10.7)にもパラレルクエリがありますが、インデックスなしのとき、振り返って実行計画を見ても、パラレルクエリは動いていないようです。
用意した環境のCPU数が少ないのもあるのですが、実行計画に「Gather」も「Parallel ほげほげ」も現れませんね。
パラレルクエリに関するドキュメントを読んでも、パラレルクエリが採用されない理由がわかりませんでした。
15.2. どのような時にパラレルクエリは使用できるのか?



まとめ

  • OracleへのTPC-Hも、MySQLPostgreSQLとほぼ同じ。
  • パラレルクエリの並列度調整に対応している。
  • PostgreSQLは外部キー制約を付けても、参照元にインデックスは作られない。が、任意でのインデックス作成を行わなくても、それなりに速い。

Oracleさん、雑な設定でも捌いてしまうのはやっぱりすげえし、動いてしまうがゆえに、雑な設定&雑なSQLを投げる人を量産してしまう罪作りなヤツでもある。


WEB+DB PRESS Vol.112

WEB+DB PRESS Vol.112

  • 作者: 樋口剛,篠田典良,谷口慶一郎,大沼由弥,豊島正規,三村益隆,笹田耕一,牧大輔,大原壯太,門松宏明,鈴木恭介,新倉涼太,末永恭正,久保田祐史,池田拓司,竹馬光太郎,はまちや2,竹原,粕谷大輔,泉征冶
  • 出版社/メーカー: 技術評論社
  • 発売日: 2019/08/24
  • メディア: 単行本(ソフトカバー)
  • この商品を含むブログを見る

注意事項

どうやらHammerDB3.2でOracleTPC-HのBuildschemaする際、管理用ユーザー名を「system」固定で投げてるっぽいです。

AWS RDS/Oracleに投げるとコケるところで気づきました。

RDSにはSYSやSYSTEMではログインできず、構築時に利用者として使える最強ユーザーの名前を指定して、それを使うことになります。

なので、HammerDBの「system_user」「system_password」はそのユーザーのものを指定しますが、残念なことに、HammerDBのソースが「system」でハードコードされてます。(パスワードは指定したパラメータから受け取ってる)


具体的な場所は、HammerDB3.2の場合、
「HammerDB-3.2/src/oracle/oraolap.tcl:795行目」です。
ここが「ユーザー名:system」で固定化しているだけでなく、このソースが書いてあるプロシージャが受け取る引数にもsystem_userがありません。。。

とりあえずなんとかしたい人は、ここのハードコードのところに、管理用ユーザーの名前を直接埋め込んでからhammerdbcliを起動してください。

オンプレ(特に自分しか使わないローカルなヤツ)だと、SYSTEM潰すとかしないから、これは気が付かないわー。

RDS以外にも、SYS/SYSTEM潰しているようなマネージドサービスがあったら、同じ事象にハマリます。(Oracle本家以外のDBaaSでAWS以外にOracle使えるところあったかな?)


暇なときに、GithubにIssue上げておくか。。。