[DBチューニングコンテスト とんがりナレッジ] 優勝への道③ TPC-H チューニング編
最後に、決勝で実施したTPC-Hについてです。
競争条件は、下記の通りです。
<決勝> (1) TPC-H 4セッション(Scale Factor=10 )
まずは、事前準備から。
表領域は特に意識せずに作成しました。
<実行コマンド> CREATE BIGFILE TABLESPACE "TPCH10" LOGGING DATAFILE '/opt/oracle/base/oradata/tpch10/tpch10.dbf' SIZE 30720M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ; CREATE BIGFILE TABLESPACE "TPCH10_LINEITEM" LOGGING DATAFILE '/opt/oracle/base/oradata/tpch10/tpch10_lineitme.dbf' SIZE 4700M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; CREATE BIGFILE TABLESPACE "TPCH10_ORDERS" LOGGING DATAFILE '/opt/oracle/base/oradata/tpch10/tpch10_orders.dbf ' SIZE 1500M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ; ※複数作ったのは、大きい表だけでもRAM上に配置しようかと考えたためです。(実際は、Diskネックにはならなかったため、RAM上には配置しませんでした。)
チューニングの優先順位は、TPC-Cとほぼ同じです。
※やり取りするデータサイズが増えることから、ネットワーク部分も考慮に入れました。
【チューニングの優先順位】 1. Diskアクセスを減らす 2. 減らせないものは、Diskからのアクセスを高速にする 3. ネットワークはアクセスを減らす。 4. 減らせないものは、ネットワークはアクセスを高速にする。 4. Memoryアクセスを減らす 5. 減らせないものは、Memoryからのアクセスを高速にする 6. CPUリソースを無駄に使用している処理を減らす 7. 減らせない処理は、CPUリソースの使用方法を効率化する
※TPC-C同様、チューニング前後のベンチマーク結果は記載できないため、代わりに効果(高,中,低)という表現で記載することをご了承ください。
TPC-Hと考えた時、まず最初に思いつくのは、
インメモリ化とパラレルクエリ、COMPRESSです。
【インメモリの使用】(効果:高)
一見、インメモリですべて処理したほうが速いように思いますが、実際はディスクから読み込んでポピュレート(カラム型フォーマットでのデータロード)する処理があるため、
今回の環境では、LINEITEMのみが一番速くなりました。
※当然、ポピュレート完了後のインメモリのみの処理では、すべて行っている状態のほうが速いです。
<実行コマンド> alter TABLE TPCH10.LINEITEM inmemory; alter TABLE TPCH10.ORDERS no inmemory; alter TABLE TPCH10.PART no inmemory; alter TABLE TPCH10.REGION no inmemory; alter TABLE TPCH10.NATION no inmemory; alter TABLE TPCH10.SUPPLIER no inmemory; alter TABLE TPCH10.CUSTOMER no inmemory; alter TABLE TPCH10.PARTSUPP no inmemory;
【テーブルのパーティション化(効果:高)とCOMPRESS(効果:中)】
パラレル・クエリで高速化を実現するために、大きいテーブルを対象に、パーティション化しました。
合わせて、ディスク読み込み量を減らすため、COMPRESSしています。
ただし、COMPRESSはLINEITEN以外では、効果がほとんどない状態でCPUリソースを無駄に使用してしまっていたため、LINEITEMのみ実施しました。
また、パーティション・キーは、HammerDBのアクセスが年や月単位のアクセスが比較的多かったため、
1ヶ月から1年の間隔の中で、どのクエリもなるべく速くなる状態を目指し調整しました。
※参考値取得のために、日単位も実行したのですが、その場合、信じられないくらい遅くなりました。。。
(パラレルクエリの性質上当然と言えば当然ですが)
<実行コマンド>
create table TPCH10.lineitem
inmemory
COMPRESS FOR DIRECT_LOAD OPERATIONS
pctfree 1
pctused 99
initrans 10
parallel 2
nologging
tablespace TPCH10_LINEITEM
partition by range (l_shipdate)
(
PARTITION P_920101 VALUES LESS THAN (TO_DATE('1992-01-01','YYYY-MM-DD')) TABLESPACE TPCH10_LINEITEM,
PARTITION P_920401 VALUES LESS THAN (TO_DATE('1992-04-01','YYYY-MM-DD')) TABLESPACE TPCH10_LINEITEM,
PARTITION P_920701 VALUES LESS THAN (TO_DATE('1992-07-01','YYYY-MM-DD')) TABLESPACE TPCH10_LINEITEM,
PARTITION P_921001 VALUES LESS THAN (TO_DATE('1992-10-01','YYYY-MM-DD')) TABLESPACE TPCH10_LINEITEM,
PARTITION P_930101 VALUES LESS THAN (TO_DATE('1993-01-01','YYYY-MM-DD')) TABLESPACE TPCH10_LINEITEM,
PARTITION P_930401 VALUES LESS THAN (TO_DATE('1993-04-01','YYYY-MM-DD')) TABLESPACE TPCH10_LINEITEM,
PARTITION P_930701 VALUES LESS THAN (TO_DATE('1993-07-01','YYYY-MM-DD')) TABLESPACE TPCH10_LINEITEM,
PARTITION P_931001 VALUES LESS THAN (TO_DATE('1993-10-01','YYYY-MM-DD')) TABLESPACE TPCH10_LINEITEM,
PARTITION P_940101 VALUES LESS THAN (TO_DATE('1994-01-01','YYYY-MM-DD')) TABLESPACE TPCH10_LINEITEM,
PARTITION P_940401 VALUES LESS THAN (TO_DATE('1994-04-01','YYYY-MM-DD')) TABLESPACE TPCH10_LINEITEM,
PARTITION P_940701 VALUES LESS THAN (TO_DATE('1994-07-01','YYYY-MM-DD')) TABLESPACE TPCH10_LINEITEM,
PARTITION P_941001 VALUES LESS THAN (TO_DATE('1994-10-01','YYYY-MM-DD')) TABLESPACE TPCH10_LINEITEM,
PARTITION P_950101 VALUES LESS THAN (TO_DATE('1995-01-01','YYYY-MM-DD')) TABLESPACE TPCH10_LINEITEM,
PARTITION P_950401 VALUES LESS THAN (TO_DATE('1995-04-01','YYYY-MM-DD')) TABLESPACE TPCH10_LINEITEM,
PARTITION P_950701 VALUES LESS THAN (TO_DATE('1995-07-01','YYYY-MM-DD')) TABLESPACE TPCH10_LINEITEM,
PARTITION P_951001 VALUES LESS THAN (TO_DATE('1995-10-01','YYYY-MM-DD')) TABLESPACE TPCH10_LINEITEM,
PARTITION P_960101 VALUES LESS THAN (TO_DATE('1996-01-01','YYYY-MM-DD')) TABLESPACE TPCH10_LINEITEM,
PARTITION P_960401 VALUES LESS THAN (TO_DATE('1996-04-01','YYYY-MM-DD')) TABLESPACE TPCH10_LINEITEM,
PARTITION P_960701 VALUES LESS THAN (TO_DATE('1996-07-01','YYYY-MM-DD')) TABLESPACE TPCH10_LINEITEM,
PARTITION P_961001 VALUES LESS THAN (TO_DATE('1996-10-01','YYYY-MM-DD')) TABLESPACE TPCH10_LINEITEM,
PARTITION P_970101 VALUES LESS THAN (TO_DATE('1997-01-01','YYYY-MM-DD')) TABLESPACE TPCH10_LINEITEM,
PARTITION P_970401 VALUES LESS THAN (TO_DATE('1997-04-01','YYYY-MM-DD')) TABLESPACE TPCH10_LINEITEM,
PARTITION P_970701 VALUES LESS THAN (TO_DATE('1997-07-01','YYYY-MM-DD')) TABLESPACE TPCH10_LINEITEM,
PARTITION P_971001 VALUES LESS THAN (TO_DATE('1997-10-01','YYYY-MM-DD')) TABLESPACE TPCH10_LINEITEM,
PARTITION P_980101 VALUES LESS THAN (TO_DATE('1998-01-01','YYYY-MM-DD')) TABLESPACE TPCH10_LINEITEM,
PARTITION P_980401 VALUES LESS THAN (TO_DATE('1998-04-01','YYYY-MM-DD')) TABLESPACE TPCH10_LINEITEM,
PARTITION P_980701 VALUES LESS THAN (TO_DATE('1998-07-01','YYYY-MM-DD')) TABLESPACE TPCH10_LINEITEM,
PARTITION P_981001 VALUES LESS THAN (TO_DATE('1998-10-01','YYYY-MM-DD')) TABLESPACE TPCH10_LINEITEM,
PARTITION P_MAX VALUES LESS THAN (MAXVALUE)
)
as select * from TPCH10.LINEITEM_BK;
create table TPCH10.PARTSUPP
pctfree 1
pctused 99
initrans 10
parallel 2
nologging
tablespace TPCH10
partition by range (PS_PARTKEY)
(
PARTITION P_001 VALUES LESS THAN ( 500000) TABLESPACE TPCH10,
PARTITION P_002 VALUES LESS THAN (1000000) TABLESPACE TPCH10,
PARTITION P_003 VALUES LESS THAN (1500000) TABLESPACE TPCH10,
PARTITION P_MAX VALUES LESS THAN (MAXVALUE)
)
as select * from TPCH10.PARTSUPP_BK;
create table TPCH10.ORDERS
pctfree 1
pctused 99
initrans 10
parallel 2
nologging
tablespace TPCH10_ORDERS
partition by range (O_ORDERDATE)
(
PARTITION P_920102 VALUES LESS THAN (TO_DATE('1992-01-01','YYYY-MM-DD')) TABLESPACE TPCH10_ORDERS,
PARTITION P_930101 VALUES LESS THAN (TO_DATE('1993-01-01','YYYY-MM-DD')) TABLESPACE TPCH10_ORDERS,
PARTITION P_940101 VALUES LESS THAN (TO_DATE('1994-01-01','YYYY-MM-DD')) TABLESPACE TPCH10_ORDERS,
PARTITION P_950101 VALUES LESS THAN (TO_DATE('1995-01-01','YYYY-MM-DD')) TABLESPACE TPCH10_ORDERS,
PARTITION P_960101 VALUES LESS THAN (TO_DATE('1996-01-01','YYYY-MM-DD')) TABLESPACE TPCH10_ORDERS,
PARTITION P_970101 VALUES LESS THAN (TO_DATE('1997-01-01','YYYY-MM-DD')) TABLESPACE TPCH10_ORDERS,
PARTITION P_980101 VALUES LESS THAN (TO_DATE('1998-01-01','YYYY-MM-DD')) TABLESPACE TPCH10_ORDERS,
PARTITION P_MAX VALUES LESS THAN (MAXVALUE)
)
as select * from tpch10.ORDERS_BK;
【PGAの調整】
PGAの調整をしていない場合、一時表領域が使われてしまい、せっかくのパラレルクエリも遅くなってしまいます。
今回は、流れるクエリがわかりきっているため、セッションレベルで下記を指定しました。
※一時表領域が使用される場合に悪化するだけのため、効果は記載していません。
<変更パラメータ> WORKAREA_SIZE_POLICY = MANUAL SORT_AREA_SIZE = 524288000 HASH_AREA_SIZE = 1048576000 ※実際の設定は、ログオントリガーで実施
【direct path readの高速化】(効果:中)
LINEITEM(inmemory)をポピュレートする際に実行されるdirect path readを高速化します。
OSのIOサイズ(1MB)に合わせて調整しました。
※iostat上で見ても、最初に測定したディスク性能が出ていました。
<パラメータ変更内容> _adaptive_direct_read=TRUE _db_file_direct_io_count=1048576 db_file_multiblock_read_count=32
【direct path readのの抑制】(効果:低)
一見、上記の高速化と矛盾しているように見えますが、こちらはインメモリ化しないほうのテーブルの話です。
メモリが足りる環境で、あえてdirect path readを何回も発生させているのはどう考えても非効率です。
そのため、強制的にdirect path readを抑制しました。
<パラメータ変更内容> alter session set events '10949 trace name context forever, level 1' _small_table_threshold=1000000 _VERY_LARGE_OBJECT_THRESHOLD=100000
【リザルトキャッシュの使用】(効果:低)
いかにインメモリの処理が速いとは言っても、リザルトキャッシュを使用できた場合よりは速くなりません。
乱数の生成結果にも左右されますが、少しでも速度がほしかったため、設定しました。
※実行が1回のみのため、恩恵はそこまでありませんでしたが、複数回行う場合は、(効果:高)となります。
<パラメータ変更内容> result_cache_max_size=1G result_cache_mode=FORCE
【ネットワーク関連のチューニング】(効果:低)
せっかく速くしたクエリでも、ネットワーク部分で遅延があっては意味がないため、ネットワーク関連のパラメータを変更します。
SDUでの分割を少なくし、下位レイヤーでのパケット数を減らします。
また、合わせて送受信バッファも調整しました。
<パラメータ変更内容(listener.ora,tnsnames.ora)> (SDU=2097152) (RECV_BUF_SIZE=4194304) (SEND_BUF_SIZE=4194304)
【その他パラメータの調整】
inmemoryサイズとDB Bufferのサイズは実際にテーブルサイズに合わせて調整しました。
また、パラレルクエリでは、ラージプールも使用するため、少し大きめに設定しました。
<パラメータ変更内容> db_block_size=32768 inmemory_size=4500m db_cache_size=6000m large_pool_size=5G
ここまでで、CPU(全コア100%)もDisk(1GB/s)も完全に性能が出ている状態になっていました。
そのため、少しでも高速化を目指すとなると、それぞれの処理の効率化です。
【パラレルクエリの更なる高速化】(効果:低)
パラレルクエリを高速化するために、メッセージサイズを調整しました。
※実際はそこまで効果はありませんでしたが。。。
<パラメータ変更内容> PARALLEL_EXECUTION_MESSAGE_SIZE=32768
【ポピュレートの高速化】(効果:低)
今度は、ポピュレートを高速化します。
なぜなら、ポピュレート完了までは、ひたすらディスクからデータを取ってくるからです。(インメモリ領域にデータがないため)
TPC-Hだけで考えた場合、どう考えても、速くポピュレートを終わらしたほうがいいに決まっています。
通常、ポピュレートはバックグラウンドプロセスで行われます。
また、使えるCPUリソースもデフォルトでは1%に制限されている(INMEMORY_TRICKLE_REPOPULATE_SERVERS_PERCENT)ため、
最大値の50%に引き上げます。
合わせて、最大プロセス数もCPUコア数*2の値に設定します。
※デフォルト1なのは、ポピュレート中でもシステムに負荷をかけずに運用を続けられるようにするためです。
<パラメータ変更内容> INMEMORY_TRICKLE_REPOPULATE_SERVERS_PERCENT=50 inmemory_max_populate_servers=16
結果、ポピュレート終了までの時間は多少速くなりましたが、まだ物足りません。
【もっとポピュレートを高速化！！】(効果:中)
50%ではやはり、同じデータを何度もディスクから読み込みます。
そのため、1度だけのディスク読み込みになるように、ポピュレートをバックグラウンドではなくフォアグランドで実施するように変更しました。
また、データが変わらないこともわかりきっているので、不要な再ポピュレートも抑制させました。
結果として、高速化 + 計測結果の安定化(フォアグラウンドなので)を実現できました。
リソース状態は、CPUネック(ポピュレート)の状態で、ディスクIOは300MB/s程度になっていました。
<パラメータ変更内容> _inmemory_populate_fg=TRUE _inmemory_repopulate_disable=TRUE
ここまでで、当初の目標値は達成していたため、ひとまず満足していたのですが、勝負となると欲が出てきます。
結果、邪道な方法に手を出してしまいました。。。
【禁断の・・・】
データ変えなければルール違反ではないかな？？
とルールの穴を突いているようで、最後まで使うか、本当に悩みましたが、勝負に勝ちたい気持ちに負けて使ってしまいました。
Materialized View(MVIEW)！！！
当然データは変えてはいけないので、MVIEWを作りクエリ・リライトでMVIEWを使用するようにしました。
ただ、正直、結構面倒な作業になりました。
どの乱数にも対応できるようなMVIEW(乱数部分の条件なし)の場合
→インメモリ処理のほうが圧倒的に速い
乱数部分を埋めた状態のMVIEWを作成
→HammerDBのソースを確認して、乱数生成のパターンの確認したところ、一つあたり数万以上のパターンになるクエリもあったため
MVIEW作成自体、かなり時間がかかりました。。。
仕様上クエリ・リライトできないパターン
→作成しながら気づいたのがこのパターンです。
結果、一番時間のかかるLINEITEMを読む時間は短縮できずに、
INMEMORYクエリ→MVIEW化の高速化になってしまいました。
何万ものMVIEWのクエリリライトでは、今度はパースの時間がインメモリパラレルクエリの時間より遅くなってしまいました。
結果、パターンがそれほど多くないもののみをMVIEW化しました。
※効果は言うまでもないため、記載していません。
<作成したMVIEW数> query 1 : 61 query 4 : 60 query 5 : 35 query 9 : 93 query 18 : 4 query 21 : 25 <パラメータ変更内容> alter session set query_rewrite_enabled=FORCE ※ログオントリガーで設定
【バチがあたったのか。。。】
勝負前日の夜、最後に実行時間の計測だけ行ったところ、いきなり速度が80%程度に落ちてしまいました。
待機イベントを確認していると、リソース・マネージャに制限されていたため、最後に無効にしました。
<パラメータ変更内容>
alter system set resource_manager_plan='';
execute dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('WEEKEND_WINDOW','RESOURCE_PLAN','');
以上が今回、TPC-Hで実施した内容です。