TPC-H

適当に考えて作った問題のSQL：

SQL> select A.* from lineitem A, lineitem_org B, lineitem_comp C
  2  where A.l_orderkey = B.l_orderkey
  3  and A.l_orderkey = C.l_orderkey
  4  and (
  5     (A.l_receiptdate < B.l_commitdate and A.l_comment like '%obata%')
  6  or (A.l_receiptdate > B.l_commitdate and B.l_comment like '%tanaka%')
  7  or (A.l_receiptdate <> B.l_commitdate and A.l_comment like '%ichiro%')
  8  or (A.l_receiptdate > C.l_commitdate and C.l_comment like '%tanaka%')
  9  or (A.l_receiptdate <> C.l_commitdate and C.l_comment like '%ichiro%')
 10  )

少しやり方を変えてみる：

SQL> select A.* from lineitem A, lineitem_org B, lineitem_comp C
  2  where A.l_orderkey = B.l_orderkey
  3  and A.l_orderkey = C.l_orderkey
  4  and A.l_receiptdate < B.l_commitdate and A.l_comment like '%obata%'
  5  union
  6  select A.* from lineitem A, lineitem_org B, lineitem_comp C
  7  where A.l_orderkey = B.l_orderkey
  8  and A.l_orderkey = C.l_orderkey
  9  and A.l_receiptdate > B.l_commitdate and B.l_comment like '%tanaka%'
 10  union
 11  select A.* from lineitem A, lineitem_org B, lineitem_comp C
 12  where A.l_orderkey = B.l_orderkey
 13  and A.l_orderkey = C.l_orderkey
 14  and A.l_receiptdate <> B.l_commitdate and A.l_comment like '%ichiro%'
 15  union
 16  select A.* from lineitem A, lineitem_org B, lineitem_comp C
 17  where A.l_orderkey = B.l_orderkey
 18  and A.l_orderkey = C.l_orderkey
 19  and A.l_receiptdate > C.l_commitdate and C.l_comment like '%tanaka%'
 20  union
 21  select A.* from lineitem A, lineitem_org B, lineitem_comp C
 22  where A.l_orderkey = B.l_orderkey
 23  and A.l_orderkey = C.l_orderkey
 24  and A.l_receiptdate <> C.l_commitdate and C.l_comment like '%ichiro%'
 25  /

レコードが選択されませんでした。

TPC-Hベンチマークの続き

CPUはたいして回らなかった、そしてdirect path read待ちばかり、、、
ディスク転送量は限界に近づくどころか、むしろ下がっていった。
絶対におかしい！！！

Parallel Queryを検証する

初心に帰って、Parallel Queryが「どのくらい価値のあるものなのか？」を検証してみた。

過去の検証で作った大きなテーブルを使い負荷の高そうなSQLを適当に作った。
LINEITEM　　　　　：　partitioning+compress
LINEITEM_COMP　　：　compress
LINEITEM_ORG 　　：　オリジナル

SQL> select A.* from lineitem A, lineitem_org B, lineitem_comp C
  2  where A.l_orderkey = B.l_orderkey
  3  and A.l_orderkey = C.l_orderkey
  4  and (
  5     (A.l_receiptdate < B.l_commitdate and A.l_comment like '%obata%')
  6  or (A.l_receiptdate > B.l_commitdate and B.l_comment like '%tanaka%')
  7  or (A.l_receiptdate <> B.l_commitdate and A.l_comment like '%ichiro%')
  8  or (A.l_receiptdate > C.l_commitdate and C.l_comment like '%tanaka%')
  9  or (A.l_receiptdate <> C.l_commitdate and C.l_comment like '%ichiro%')
 10  )

まずはNO_PARALLELで動かしてみる：

SQL> ALTER SESSION FORCE PARALLEL QUERY PARALLEL 1;

SQL> select A.* from lineitem A, lineitem_org B, lineitem_comp C
  2  where A.l_orderkey = B.l_orderkey
  3  and A.l_orderkey = C.l_orderkey
  4  and (
  5     (A.l_receiptdate < B.l_commitdate and A.l_comment like '%obata%')
  6  or (A.l_receiptdate > B.l_commitdate and B.l_comment like '%tanaka%')
  7  or (A.l_receiptdate <> B.l_commitdate and A.l_comment like '%ichiro%')
  8  or (A.l_receiptdate > C.l_commitdate and C.l_comment like '%tanaka%')
  9  or (A.l_receiptdate <> C.l_commitdate and C.l_comment like '%ichiro%')
 10  )
 11  /

レコードが選択されませんでした。

経過: 00:14:33.61


実行計画
----------------------------------------------------------
Plan hash value: 2728639899

前回までのおさらい

1. ディスク転送量を上げるために１セッションのテストを行ってきた
2. その結果420から450MB/sがでた
3. qphを上げるためにpartitioning+compressを行い最高300%アップが達成できた

当初はdirect path readを効率化すれば、おのずとディスク転送量も上がり、結果TPC-Hベンチマークの得点もあがるはずと考えていた。しかし現在は、

• qphは上がったがディスク転送量は200MB/s程度となった
  Compressして読み出す量が減ったから下がるのは当然？
  月単位のPartitioningでParallel Queryが効果的に働いたから？
• CPU%は最高でも20%程度しか回っていないのに
  Compressして読み出す量が減ったら、もっと次をdirect path readしてほしい！
  月単位のPartitioningでParallel Queryを効率的にしたのだから、もっとdirect path read量を増やしてほしい！

大体、安定していないのは反則の300%アップだ！

セッション数を増やしてみる

DOP=4でセッション数2->4->8と増やしてみると：CPUは回りましたが、殆んどがdirect path read待ちの元の悪い状況に逆戻りしました。

DB OptimizerではCPU Max(赤線)をProcessor Queue Lengthで見ている。
Unix環境であればRun Queueだ。
このブログでもCPU%を指標として書いてきたがProcessor Queue Lengthを指標としたほうが的確だ。
今回のテストの場合、8セッションでCPU%はMax70%程度。しかしProcessor Queue Lengthは実装Core数を超え、処理はQueuingされるだけとなっていることが分かる。

CPU%はdirect path read待ちでProcessor Queuingされ回らない。

最後に、

今回の試作機でTPC-HをThink Time無しに激しく動かすと同時2セッションが限界ということになる。
結局4台のSSDでRAID0にして520MB/sのSequential Read幅を持たせてもセッション数の増加とともにdirect path read待ちが指数関数的に増加する。520MB/sじゃ足りない、、、
同時2セッションくらいで限界？

クリックで表示されたSQL文の全容は：

SELECT
    s_name,
    COUNT (*) AS numwait
FROM
    supplier,
    lineitem l1,
    orders,
    nation
WHERE
    s_suppkey = l1.l_suppkey AND
    o_orderkey = l1.l_orderkey AND
    o_orderstatus = 'F' AND
    l1.l_receiptdate > l1.l_commitdate AND
    EXISTS (SELECT *
            FROM lineitem l2
            WHERE
                l2.l_orderkey = l1.l_orderkey AND
                l2.l_suppkey <> l1.l_suppkey) AND
    NOT EXISTS (SELECT *
                FROM lineitem l3
                WHERE
                    l3.l_orderkey = l1.l_orderkey AND
                    l3.l_suppkey <> l1.l_suppkey AND
                    l3.l_receiptdate > l3.l_commitdate) AND
    s_nationkey = n_nationkey AND
    n_name = 'CHINA'
GROUP BY s_name
ORDER BY
    numwait DESC,
    s_name;

Embarcadero® DB Optimizer™ XEではHint句をシュミレートしてくれる。
赤い棒グラフのHintは「使うとえらい目に合う」ことを示している。これでHintのテストがとても楽になる。

TPC-Hベンチマーク続き
前回のCompressを行う前は：

次にLINEITEMをl_shipdateでPartitioningしたら

そして、各パーティションをCompressしたら、2倍以上のqphをたたき出した

Partitioning前はLINITEMが全体の70%の負荷オブジェクトだったが、今では負荷はある程度分散された。

TPC-Hの続き、

前回の分析でLINEITEMのdirect path readアクティビティが高いのが分かった。
そこで、表圧縮をしてみる：

SQL> ALTER TABLE LINEITEM MOVE COMPRESS;

前回の結果：

TPC-Hベンチマーク指標のqphが40%以上改善された。

そして、DB Optimizerで比較してみると：
今回

TPC-Hベンチマークの続き
現在のディスク転送量は420MB/s強。

１セッションでこれ以上望めないのであれば、効率的な読み込み量を追求する。

オブジェクトを特定する

上の時系列スパイクチャートから、レスポンス要因はUser I/O

下のオブジェクトごとのDBアクティビティから、
LINEITEMのdirect path readが全体の78%の負荷が分かる。

だから、LINEITEMのREAD量を減らす、パーティショニング、コンプレス機能が効果的なことが分かる。

念のためにスパイクが飛び出た時のNo1 SQLのPlanを見てみると：
なるほど、LINEITEMへのアクセス負荷がポイントだな。。。

最後に、
Embarcadero® DB Optimizer™ XE を初めて使ってみたのだけど、
ものすごく簡単でした。

前回のテストではI/O buffer sizeを最適化することによりdb_file_multiblock_read_count = 64でも安定するようになった。そして、全体を通してみると、この64の結果が一番よい：
db_file_multiblock_read_count = 64, _db_file_direct_io_count = 524288

db_file_multiblock_read_count = 128, _db_file_direct_io_count = 1048576画面右上に出ている数値は最後のサンプリングタイミングでの数値。平均ではない。

今回の試作機はKingstonのお買い得SSD4本でRAID-0を構築した。

そのときのStrip Sizeは128KBとした。

このサイズはIntel Matrix Storage ManagerでRAID0を設定するときの最大値だ。
そして、exFAT形式のファイルをフォーマットするときに、

Allocation Unit Sizeを512KBとした。128KB x 4本 = 512KB

その結果

前回db_file_multiblock_read_count=64のとき、安定しない結果に終わった。
そして「ディスク転送量＋アルファ」の何かがあると書いた。

_db_file_direct_io_count　：　Sequential I/O buf size

今回のテストはBLOCK_SIZE=8K=8192Bでテストをしている。
このI/O buffer sizeを最適化してからテストを再度行う：

db_file_multiblock_read_count = 64, _db_file_direct_io_count = 524288

db_file_multiblock_read_count = 128, _db_file_direct_io_count = 1048576
db_file_multiblock_read_count = 256, _db_file_direct_io_count = 2907152

TPC-Hベンチマークの続き、