Search

OakieTags

more tags

Who's online

There are currently 0 users and 34 guests online.

Recent comments

Affiliations

Home

試作機でTPC-Hベンチマークテストを行う

Submitted by Ichiro Obata on Mon, 2010-08-02 16:13

前回の続きでKingstonのお買い得SSD4本で構築したRAID-0のTPC-Hベンチマークを行った。

初めは、１セッション、パラレル度=6でテスト：

そのときのディスク転送量は：
300-400MB/s程度しか出ていない。

CrystalDiskMarkで計った限界量は
1GBのReadで最高522MB/sを出しているのに、およそ120MB足りない。

そこで、4セッションにして、もう一度実行してみた：

限界転送量が出た。

522MB/sはすごい。でも、1セッションだと、パラレル度を増やしても、そこまで行かなかった。
Indexレンジスキャンなどが動かないように：
optimizer_index_cost_adj=1000;
と設定している。
それにTemporary sortも起こさないようにPGA_AGREGATE_TARGETも十分にとっている。
そしてCPUは合計で30%-70%程度しか消費していない。
そして、限界転送量を出し続けているにもかかわらず、qphは減速を繰り返し、最後は計測時間のタイムオーバーとなった：

  • Heavy SQLは全てdirect path read

  • 1セッションだと限界転送量は出ないのか？

  • 同時4セッションで失速する理由は？

    • 始めはqph性能を上げるのではなくdirect path readで522MB/sの限界転送量を出すことにこだわってみたい。

    waits event micro_sec sql
    7 direct path read 101175 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
    4 direct path read 106464 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
    4 direct path read 111444 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 direct path read 112964 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
    9 direct path read 127263 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 = 'EGYPT'
    group by s_name
    order by numwait desc, s_name
    5 direct path read 161197 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
    22 direct path read 169514 select s_suppkey, s_name, s_address, s_phone, total_revenue
    from supplier, revenue0
    where s_suppkey = supplier_no
    and total_revenue =
    ( select max(total_revenue) from revenue0)
    order by s_suppkey    		 

    SQL> select count(*),event,sum(TIME_WAITED) TIME_WAITED,SQL_TEXT
    from v$active_session_history a, v$sql b
    where sample_time > sysdate -1/144
    and event is not null
    and a.sql_id is not null
    and a.sql_id=b.sql_id
    group by event, SQL_TEXT
    order by TIME_WAITED;