Search

Top 60 Oracle Blogs

Recent comments

In-Memory Non PQ

PQ, In-Memory PQ, In-Memory Non PQを比較する

hammeroraのTPC-Hベンチマークの10回繰り返しを行いASH Viewerで結果をモニタリングした:

A,B Parallel Query (PQが得意なCOMPRESSモードで実行)
C,D In-Memory Parallel Query (In-Memoryが得意なNOCOMPRESSモードで実行。ただしlineitemのみ)
E,F In-Memory Non Parallel Query (同上)

それぞれ、同時1セッションと2セッションの結果だ。

  • 時間軸(X軸)を見るとCおよびDのIn-Memory Parallel Queryが一番速いのが分かる
  • AのPQ 1セッションとCのIn-Memory PQのCPU(緑)を見るとCのIn-Memory PQの方がCPUを使っているように見えるが、In-Memory PQはCPU dpend?で証明したようにParallel QueryのCPU使用時間の方が大きい。I/O待ちでCPUが回らないだけ
  • BのPQ 2セッションではI/O待ち(青)が限界となり、1セッションの2倍ぐらいの時間がかかっている
  • TPC-Hの結果でも以下のように書いた:

    Parallel Queryは同時2セッションが限界で、それ以上は安定した計測値が出ないし、時間も掛かり過ぎた

  • Cの1セッションIn-Memory PQは、初めにphysical readsを行いbuffer_cacheにデータを読み込んでいるが、I/O待ちは発生しないので(青)が出ていない
  • Dの2セッションIn-Memory PQは1セッション実行より若干時間は掛かったが優秀にスケーラブルであった
  • しかし同時3セッションで全てのCPUスレッドが一杯になり、4セッションでRun Queue待ちが予測できる
  • E,FのIn-Memory Non PQはスケーラブルにほぼ同一時間内に終了している
  • そして、同時8セッションまである程度スケーラブルに処理されることが予測できる
  • TPC-Hの結果でも以下のように書いた:

    それから、
    In-Memoryはスケーラビリティでは大勝です。
    でも、メモリに収まるサイズだからです。。。

    でも、よくよく考えてみると、当たり前だな、同時8セッションまではスケーラブルで、それ以上は動かなくなるんだろう。

    そして、Parallel Queryは初めから限界ディスク転送量を出してしまうので「同時実行制御」の工夫が必要だということが、今回のテストを通じて理解できた。

    In-Memory non Parallel Queryじゃダメなんだよ!の中で書いたけど、In-Memory (Non) PQにも同じことが言える。In-Memory PQにはQueueing制御があるのだけど、

    8セッションからのキューイング管理は実装CPUのスレッド数から割り出されているのだろうけど、今回使用しているCore i7 860だと、管理が始まる8セッションは少し遅すぎるのではないかと感じる。
    実際4セッションでCPUは振り切れている。「In-Memory Parallel Queryセッション数」

    同時8セッションまで動かしてしまうと、全てが動かなくなるので、やはり、同時制御の工夫が大事だと思う。

    最後に、
    Parallel QueryはCOMPRESSが得意。
    In-Memory PQはNOCOMPRESSが得意。だから、

    ALTER TABLE lineitem MOVE PARTITION 今月 NOCOMPRESS;
    ALTER TABLE lineitem MOVE PARTITION 2ヶ月前 COMPRESS;
    ALTER TABLE lineitem MOVE PARTITION 3ヶ月前 COMPRESS;
    ..
    ALTER TABLE lineitem MOVE PARTITION 1年前 COMPRESS;

    のように工夫する。

    Heavy SQLの比較

    以前行ったHeavy SQL Top5の実行時間(秒数)にIn-Memory PQも追加してみた:
    A=non parallel(all physical reads)
    B=in-memory nonParallel
    C=parallel(DOP=6)
    D=In-Memory Parallel

    SQL# A B C D
    1 7.95 3.63 3.24 1.22
    2 7.67 2.60 2.71 1.33
    3 5.24 1.01 1.51 0.35
    4 8.61 3.95 3.82 1.66
    5 2.81 1.02 2.82 0.66

    繰り返しになるが、In-Memory Parallel Queryでは明示的にパラレル度を設定することはできない。
    しかし、前回のテストで示したようにパラレル度(DOP)は自動的に6になっている。
    そして上記CのParallel(DOP=6)と比較すると2倍から5倍の性能が出ている。

    1 select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity)
    from customer, orders, lineitem
    where o_orderkey in
    ( select l_orderkey
    from lineitem
    group by l_orderkey
    having sum(l_quantity) > 313)
    and c_custkey = o_custkey
    and o_orderkey = l_orderkey
    group by c_name, c_custkey
    , o_orderkey
    , o_orderdate
    , o_totalprice
    order by o_totalprice desc, o_orderdate;
    2 select nation, o_year, sum(amount) as sum_profit
    from ( select n_name as nation, extract(year from o_orderdate) as o_year
    , l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
    from part, supplier, lineitem, partsupp, orders, nation
    where s_suppkey = l_suppkey
    and ps_suppkey = l_suppkey
    and ps_partkey = l_partkey
    and p_partkey = l_partkey
    and o_orderkey = l_orderkey
    and s_nationkey = n_nationkey
    and p_name like '%navy%') profit
    group by nation, o_year
    order by nation, o_year desc;
    3 select l_shipmode, sum(case when o_orderpriority = '1-URGENT' or o_orderpriority = '2-HIGH' then 1 else 0 end) as high_line_count
    , sum(case when o_orderpriority <> '1-URGENT' and o_orderpriority <> '2-HIGH' then 1 else 0 end) as low_line_count
    from orders, lineitem
    where o_orderkey = l_orderkey
    and l_shipmode in ('SHIP', 'FOB')
    and l_commitdate < l_receiptdate
    and l_shipdate < l_commitdate
    and l_receiptdate >= date '1997-01-01'
    and l_receiptdate < date '1997-01-01' + interval '1' year
    group by l_shipmode
    order by l_shipmode;
    4 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
    5 select supp_nation, cust_nation, l_year, sum(volume) as revenue
    from
    ( select n1.n_name as supp_nation, n2.n_name as cust_nation
    , extract(year from l_shipdate) as l_year, l_extendedprice * (1 - l_discount) as volume
    from supplier, lineitem, orders, customer, nation n1, nation n2
    where s_suppkey = l_suppkey
    and o_orderkey = l_orderkey
    and c_custkey = o_custkey
    and s_nationkey = n1.n_nationkey
    and c_nationkey = n2.n_nationkey
    and ( (n1.n_name = 'JAPAN' and n2.n_name = 'ETHIOPIA')
    or (n1.n_name = 'ETHIOPIA' and n2.n_name = 'JAPAN'))
    and l_shipdate between date '1995-01-01'
    and date '1996-12-31') shipping
    group by supp_nation, cust_nation, l_year
    order by supp_nation, cust_nation, l_year;

    TPC-Hの結果

    In-Memory Non Parallel Queryのテストなんて、あまり見かけない。
    でも「Parallel Queryが使えないOracle SEでmini DWHができるか?」という意味でテストをした。
    それに今回の試作機は16GBのメモリを積んだんだから、メモリを使い倒すテストはやりたくて仕方がなかった。

    当然、次回からはIn-Memory Parallel Queryのテスト結果をBlogするのだけど、今回は、これまで結果を整理することにした。

    今までの結果をグラフにしてみた

    T1 Parallel Query no_compress + no_partion table
    T2 compress + no_partion table
    T3 no_compress + partion table
    T4 compress + partion table
    T5 In-Memory Non PQ compress
    T6 no_compress
    T7 compress + result_cache
    T8 no_compress + result_cache

                   [X軸=同時セッション数、Y軸=qph(Query Per Hour)]

  • qphは同時8セッションでIn-Memory Non PQ(Parallel Query)が優れているのが分かる(T5-T8)
  • そして、Compressをしていない(no_compress)がダントツ1位に見える(T8)
  • でも、これをセッション数で割ると:

  • Compress Tableに対する1セッションのParallel Queryが1位になる(T2)
  • でもParallel Queryは同時2セッションが限界で、それ以上は安定した計測値が出ないし、時間も掛かり過ぎた(T1-T3)
  • その場合でもPartitioning+Compressを行えば6セッションまで動いた(T4)
  • 対して、In-Memory Non PQは8セッションまで安定して動いた(T5-T8)
  • そしてIn-Memory non Parallel Queryじゃダメなんだよ!の中で、

    In-Memory nonParallelはTPC-Hの総合点には大きく貢献したが、実際のデータウェアハウスではParallel Queryの方が優れていることが確認できた。
    そして、Parallel Queryは初めから限界ディスク転送量を出してしまうので「同時実行制御」の工夫が必要だということが、今回のテストを通じて理解できた。

    と書いた。

    ディスク転送量はCrystalDiskMarkの限界量522MB/sを常に超える場合もあった(T2)
    でもReadされているデータは殆んどが必要のないもの、Readしてメモリ上でフィルタリングされる。
    1分間のRead量は:

    500MB/s x 60(秒) = 30000MB = 30GB  ....たった1分で実際のデータ量を簡単に超えている。

    Exadata Storage ServerがインテリジェントにWhere条件をフィルタリングして結果を返したり、Join条件をハッシュ値として返すことが重要な機能だということが実感できた。
    以前Oracle Closed Worldの中で、

    でもStorage Server内での「Secondary Oracle」はExadataだけのClosedなものでしょ?
    →そんなのこれからも「どんどん変わる」。既に細かな話になり始めている。

    と書いた。
    「SSDの普及で転送速度が上がればフィルタリングなんてそれほど重要じゃなくなる」というのは認識不足でした。

    でも、
    TPC-Hベンチマークのように同時8セッションがThink Time無しにHeavy SQLを発行し続けるようなことは、現実世界ではあまりないので、2セッションぐらいで限界になるParallel Queryが活躍できるんだね。
    そうそう、T4はCompress+Partitioningで実質Read量を減らしたので、6セッションまで行けた。

    それから、
    In-Memoryはスケーラビリティでは大勝です。
    でも、メモリに収まるサイズだからです。。。

    In-Memory nonParallel + nocompressで16000qph達成

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

    今までの結論は、

    In-Memory nonParallelはTPC-Hの総合点には大きく貢献したが、実際のデータウェアハウスではParallel Queryの方が優れていることが確認できた。

    もう少し、総合点を上げるためにIn-Memory nonParallelのテストを追加した。

    Parallel Queryを速くするCompressオプションを外してみる。

    1セッション COMPRESS:

    NOCOMPRESSにすると

    同時4セッションCOMPRESS:

    NOCOMPRESSにすると

    同時8セッションCOMPRESS:

    NOCOMPRESSにすると

    そして、Resut_Cacheを使った、今までの最高点:

    NOCOMPRESSにすると

    COMPRESSされたOracle BlockをUNCOMPRESSするCPU負荷がなくなったことが高結果の要因だと思う。
    COMPRESSでディスクI/O回数を減らすコストの方がUNCOMPRESSのコストより数倍大きい。でも今回はディスクI/OゼロだからUNCOMPRESSの負荷が目立ったんだな。。。

    今回の結果はあくまで参考値。
    大規模データウェアハウス環境ではParallel Query効率化のためのCOMPRESSは非常に有効。
    In-Memory nonParallelは、TPC-Hベンチマークの点数を上げるためには都合の良いテクニックだったけど、せっかくのSSD RAID0の意味がなくなっている。

    因みに、Exadata1のTPC-Hベンチマークでは、sub partition(composit key)を使っているのでCOMPRESSは使えない筈。そしてV1なので、カラムCOMPRESSも使えない。

    In-Memory non Parallel Queryじゃダメなんだよ!

    Heavy SQL Top5の実行時間(秒数)を表にしてみた
    A=non parallel(all physical reads)
    B=in-memory nonParallel
    C=parallel(DOP=6)

    SQL# A B C A/B
    1 7.95 3.63 3.24 1.12
    2 7.67 2.60 2.71 0.96
    3 5.24 1.01 1.51 0.67
    4 8.61 3.95 3.82 1.03
    5 2.81 1.02 2.82 0.36

    Heavy SQLだけで比較するとそれほどIn-memoryが優れているわけではないことに気づく。
    初めから時間のかかる(負荷の高い)SQLでは、むしろParallel Queryの方が速い。
    In-Memory nonParallelはTPC-Hの総合点には大きく貢献したが、実際のデータウェアハウスではParallel Queryの方が優れていることが確認できた。

    そして、Parallel Queryは初めから限界ディスク転送量を出してしまうので「同時実行制御」の工夫が必要だということが、今回のテストを通じて理解できた。

    1 select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity)
    from customer, orders, lineitem
    where o_orderkey in
    ( select l_orderkey
    from lineitem
    group by l_orderkey
    having sum(l_quantity) > 313)
    and c_custkey = o_custkey
    and o_orderkey = l_orderkey
    group by c_name, c_custkey
    , o_orderkey
    , o_orderdate
    , o_totalprice
    order by o_totalprice desc, o_orderdate;
    2 select nation, o_year, sum(amount) as sum_profit
    from ( select n_name as nation, extract(year from o_orderdate) as o_year
    , l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
    from part, supplier, lineitem, partsupp, orders, nation
    where s_suppkey = l_suppkey
    and ps_suppkey = l_suppkey
    and ps_partkey = l_partkey
    and p_partkey = l_partkey
    and o_orderkey = l_orderkey
    and s_nationkey = n_nationkey
    and p_name like '%navy%') profit
    group by nation, o_year
    order by nation, o_year desc;
    3 select l_shipmode, sum(case when o_orderpriority = '1-URGENT' or o_orderpriority = '2-HIGH' then 1 else 0 end) as high_line_count
    , sum(case when o_orderpriority <> '1-URGENT' and o_orderpriority <> '2-HIGH' then 1 else 0 end) as low_line_count
    from orders, lineitem
    where o_orderkey = l_orderkey
    and l_shipmode in ('SHIP', 'FOB')
    and l_commitdate < l_receiptdate
    and l_shipdate < l_commitdate
    and l_receiptdate >= date '1997-01-01'
    and l_receiptdate < date '1997-01-01' + interval '1' year
    group by l_shipmode
    order by l_shipmode;
    4 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
    5 select supp_nation, cust_nation, l_year, sum(volume) as revenue
    from
    ( select n1.n_name as supp_nation, n2.n_name as cust_nation
    , extract(year from l_shipdate) as l_year, l_extendedprice * (1 - l_discount) as volume
    from supplier, lineitem, orders, customer, nation n1, nation n2
    where s_suppkey = l_suppkey
    and o_orderkey = l_orderkey
    and c_custkey = o_custkey
    and s_nationkey = n1.n_nationkey
    and c_nationkey = n2.n_nationkey
    and ( (n1.n_name = 'JAPAN' and n2.n_name = 'ETHIOPIA')
    or (n1.n_name = 'ETHIOPIA' and n2.n_name = 'JAPAN'))
    and l_shipdate between date '1995-01-01'
    and date '1996-12-31') shipping
    group by supp_nation, cust_nation, l_year
    order by supp_nation, cust_nation, l_year;

    だから、In-Memory Parallel Queryが最強なんだね。

    result_cacheで更に50%アップ

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

    In-Memory-nonParallelでの最高結果は同時8セッションで:

    今度はResult_Cacheを使ってみると:
    同じ8セッションで最高値15000qphを記録した。

    result_cacheが効く場合と効かない場合

    TPC-HベンチマークのHeavy SQL Top5からひとつ選んで「Where条件」を変えるテストを行ってみた。

    buffer_cache flush後


    SQL> select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity)
    2 from customer, orders, lineitem
    3 where o_orderkey in
    4 ( select l_orderkey
    5 from lineitem
    6 group by l_orderkey
    7 having sum(l_quantity) > 312)
    8 and c_custkey = o_custkey
    9 and o_orderkey = l_orderkey
    10 group by c_name, c_custkey
    11 , o_orderkey
    12 , o_orderdate
    13 , o_totalprice
    14 order by o_totalprice desc, o_orderdate;
    ...
    ...
    15行が選択されました。

    経過: 00:00:08.52

    統計
    ----------------------------------------------------------
    1 recursive calls
    0 db block gets
    145349 consistent gets
    83465 physical reads
    0 redo size
    1725 bytes sent via SQL*Net to client
    520 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    15 rows processed

    8.52秒かかった(physical readsが発生しているから)

    having sum(l_quantity) > 312)を313に変えて実行してみる


    SQL> select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity)
    2 from customer, orders, lineitem
    3 where o_orderkey in
    4 ( select l_orderkey
    5 from lineitem
    6 group by l_orderkey
    7 having sum(l_quantity) > 313)
    8 and c_custkey = o_custkey
    9 and o_orderkey = l_orderkey
    10 group by c_name, c_custkey
    11 , o_orderkey
    12 , o_orderdate
    13 , o_totalprice
    14 order by o_totalprice desc, o_orderdate;
    ...
    ...
    12行が選択されました。

    経過: 00:00:00.01

    統計
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    0 consistent gets
    0 physical reads
    0 redo size
    1563 bytes sent via SQL*Net to client
    520 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    12 rows processed

    択された行数も違うのにresult_cacheが効いて0.01秒で終わった。

    もう一度312に戻すと


    SQL> select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity)
    2 from customer, orders, lineitem
    3 where o_orderkey in
    4 ( select l_orderkey
    5 from lineitem
    6 group by l_orderkey
    7 having sum(l_quantity) > 312)
    8 and c_custkey = o_custkey
    9 and o_orderkey = l_orderkey
    10 group by c_name, c_custkey
    11 , o_orderkey
    12 , o_orderdate
    13 , o_totalprice
    14 order by o_totalprice desc, o_orderdate;
    ...
    ...
    15行が選択されました。

    経過: 00:00:00.02

    統計
    ----------------------------------------------------------
    1 recursive calls
    0 db block gets
    0 consistent gets
    0 physical reads
    0 redo size
    1725 bytes sent via SQL*Net to client
    520 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    15 rows processed

    recursive callが発行され0.02秒となったが、result_cacheは効いた。

    今度は314にしてみる


    SQL> select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity)
    2 from customer, orders, lineitem
    3 where o_orderkey in
    4 ( select l_orderkey
    5 from lineitem
    6 group by l_orderkey
    7 having sum(l_quantity) > 314)
    8 and c_custkey = o_custkey
    9 and o_orderkey = l_orderkey
    10 group by c_name, c_custkey
    11 , o_orderkey
    12 , o_orderdate
    13 , o_totalprice
    14 order by o_totalprice desc, o_orderdate;
    ...
    ...

    10行が選択されました。

    経過: 00:00:04.17

    統計
    ----------------------------------------------------------
    1 recursive calls
    0 db block gets
    145349 consistent gets
    0 physical reads
    0 redo size
    1463 bytes sent via SQL*Net to client
    520 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    10 rows processed

    result_cacheは効かなかった。しかし、buffer_cache scan(physical reads=0)で4.17秒で終わった。

    結果がまったく同じでなくともresult_cacheは働く。
    その理屈は、僕には説明できない。

    因みに、Exadata1のTPC-Hベンチマークでも:
    result_cache_mode = FORCE を設定している。

    Parallel Query と In-Memory nonParallelの比較

    TPC-Hベンチマーク続き

    前回のIn-Memory nonParallel Queryでは「同時セッション数」の増加とともに、スケーラブルにqphは上がった。そして、8セッションでCPUの限界となった。

    しかし、Parallel Queryでは、そうは行かない。
    同時1セッションでは:

    同時4セッションでは:

    同時6セッションでは:
    qphはそれほど変わらない。

    結果として、6セッションのときのユーザ・レスポンスは1セッションのときの6倍遅いということになる。

    今回使用したKingstonのお買い得SSD4本で構築したRAID-0のCrystalDiskMarkで計った限界量は

    In Memory QueryでTPC-H 10000qphの大台に!!!

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

    Parallel Queryをやめて、Buffer_Cache=5GBですべてをOn Memoryとした:

    同時ユーザを4セッションに増やしてみた:

    その時点でCPU%は50%。

    倍の8セッションに増やす:
    CPU 100%ととなり、Intel Core i7 860(実売価格25000円)は限界。

    qphは10000を超えた。

    Parallel Queryで「ほぼ安定する」最高qphは: