 |
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Informix DSS照会のチューニングJack Parker |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
この記事は当初Informix Tech Notes Volume 10、2000年第1号に掲載されたものです。IBM Informix® Dynamic ServerTM（IDS）向けに書かれました。内容はIBM Informix Foundationにもあてはまります。IBM Informix Extended Parallel Server（XPS）向けに一部コメントを追加してあります。 はじめに データウェアハウスと非常に大規模なデータベースは、従来のWRITE OUT（OLTP）データベース・システムとは異なる使い方をされる傾向があります。OLTP環境では、目標は短い時間フレームで数多くの別個のトランザクションを実行することです。OLTP管理者は、数百、数千というユーザーをサポートし、データの非常に具体的な部分に注目しがちです（たとえば、顧客の注文の項目4およびその注文マスター・レコードの残高を更新できなければならない）。そのようなOLTPトランザクションは、1つまたは複数のUPDATEステートメントWHERE cust_id/ord_no = ?で実行できることもあります。 DSS環境において、ほんの一握りのユーザーしか抱えておらず、その各々が大量のデータに対して照会を行っている、という可能性はさらに高くなります。OLTPモデルでは、このようなユーザーはひどい罰を受けることになります。しかし、DSSの世界では、この種のアクティビティーは奨励されます。通常、DSS照会は1つまたは複数の表のあらゆる行をスキャンします。さらに、通常、OLTPシステムのサイズが百メガバイト単位で測定される状況で、DSSシステムは百ギガバイト単位で測定されます。 DSSシステムは、Business ObjectsまたはSASのようなフロントエンド・ツールによって照会が行われる傾向があり、照会を行うSQLはツールによって生成されます。往々にして、照会そのものをあまり管理できないことが多くなります。 OLTPシステムで使用される同じデータ・モデル、索引読み取り、エンジンのチューニングを用いてDSS照会をサポートしようとする場合、そのような照会の応答時間は往々にして日にち単位で測定されることになりなります。ごく最近の事例では、そのような照会の応答時間が40日でした。このような特殊な状況で私は、この記事で詳しく説明しているチューニング方式を使用して、照会結果時間を約15分まで短縮することができました。これは、照会に変更を加えることも、スキーマを変更することもなく実現できたのです。この記事では、DSS照会の実行時間を短縮するチューニング手順について具体的に説明します。
監視ツール
Explain plan リスト1. Explain planの出力
見積りコストが非常に低いことに注目してください。UPDATE STATISTICSを実行し忘れたようです。 このEXPLAIN planの最初のセクションは、照会に続いて見積りコストが表示されています。これは、照会を解決するために必要であると最適化プログラムが判断する命令の数から導かれます。ここで示されている値は、この照会に対してはあまりに小さすぎます（10億単位の数値であればより標準的といえます）。 返される行の予想数は、少なくとも1つの表が空であると最適化プログラムが見なしている示度です。最低1つの行が常にcount(*)オペレーションから返されます。 おそらくは空の表の読み取りにはコストが生じないために、各々の表は順次スキャンを使用して読み取られます。「Serial」は、表のあるディスクの各フラグメントが、望ましい「並列」ではなく、順次に読み取られることを示しています。フラグメントは照会から除去されていません（それゆえ「fragments: ALL」です）。 一時表は、もちろん、実際のgroup byオペレーションを実行するために必要です。2つの表を結合するために、ハッシュ結合が使用されます。この場合も、表の1つが空であるため、またこれらの表に索引がないからです。 実際に表示させたいのは、もっと高い見積りコストです。望ましいのは順次スキャンです。本当は逐次ではなく、並列スキャンなのですが。WHERE条件（フィルター）を通じてフラグメントの一部を除去できたのであれば、スキャンすべきフラグメントのリストだけを表示したかもしれません。最終的に、次のような行も表示させたいと考えました。
実際には、より高い並列性を示すもっと大きい値を表示させたかったのですが、この例の性質上、21にします。これは、PDQPRIORITYを使用していることを示しています。スレッドの正確な数は、各表がまたがっているDB領域の数と、各追加タスクに割り当てられているスレッドの数によって異なります。リスト2に示される onstat -g sqlコマンドの出力でご覧いただけるように、1つの表に9つのスキャン・スレッドがあり、2番目の表に11のスキャン・スレッドがあり、すべてをまとめる1つのsqlexecスレッドがあります。PDQPRIORITYをさらに高く設定すると、グループ化とソートのさらに特殊化されたスレッドを表示することができます。 これらの情報の主要な断片から、照会を解決するために選ばれた最適化プログラムに何か問題があることが分かります。 Xtree 以下の図1は、代表的なハッシュ結合のXtree画面ショットを示しています。この図の下部には、2組のスキャン・スレッドがあります。右側は9スレッド、左側は11スレッドで実行しています。右側の表は、ハッシュ・テーブルのメモリーに読み込まれ、左側の表は読み込み中にこのハッシュに対してスキャンと突き合わせが行われています。 各ボックスの右の数字は、照会のこの部分を実行するために使われているスレッドの数を示しています。ボックス内のメーター表示の下には、この1秒間に照会のこの部分によって処理された行数を示す数字があります。ボックスの上部には、これまで処理された行数が示されています。約114,000行がこの1秒間に左側の表から読み取られ、同じこの1秒間に約89,000の突き合わせが行われました。ハッシュ・テーブルのスワップが発生すると、毎秒プロセス・カウンターがすべてゼロに落ちます。こうした状況を監視することで、プロセスがどのくらいの頻度でどのくらいの時間ハッシュ・テーブルのスワップに費やしているのか突き止めることができます。 スキャン・ボックスの横にある数字は、表が断片化されるDB領域の数値と一致するはずです。このマシンでは、毎秒フラグメントあたりおよそ50,000〜70,000行程度がスキャンされると見込んでいます。この数値が最後に落ち込んだ場合は、1つまたは複数のフラグメントがおそらくは他のフラグメントよりも先に完了しています。この状態が最後の5〜10秒で発生した場合にはそれで大丈夫ですが、その落ち込みが照会の半ばで発生した場合には、データ・スキューの断片化スキームを調べたほうがよいでしょう。 フィルター・ボックスは、フィルターを使用している場合には（where state_cd = 'MA'など）、スキャンされた行数を減らすはずです。フィルターされた合計数は、スキャン速度とすぐに同じペースになるはずです。 図1： Xtreeハッシュ結合 図2は、典型的なネストされたループ結合を示しています。左側の表は、順次スキャンされています。次に、2番目の表へのプローブがこの最初の表から行ごとに行われます。ご覧のように、このスナップショットがとられた瞬間に、照会が1,373行のすべてを結合しましたが、これはあまり感心しません。ここでは、結合自体がスキャン速度を制限しています。さらに興味深いことに、読み取られた行よりも多くの行が結合されていることがわかります。これは重複する行があることを示していますが、同じ住所に複数の人が住んでいることと同じことになるので容認できます。 図2： Xtreeのネストされたループ結合 onstat -g sesおよびonstat -g sqlコマンド
Controlling sqlexec thread
9 Scan threads (0-8) for Table 1
11 Scan threads (0-10) for Table 2
出力には、多くの情報が含まれています。もしかすると必要以上かもしれませんが、着目すべき重要な手がかりがいくつかあります。現在のステートメント名は、セッションが実際に実行している照会のSQLに関する詳細を提供します。開始されたスレッドの数とタイプが分かります（たとえば、scan_2.3など、1つの表に対して9のスキャン・スレッド、他の表に対して11のスキャン・スレッド、PDQPRIORITY=1（照会を開始したのは私なので、これは分かります）でハッシュ結合を行う1つのsqlexecスレッドがあります。onstat -g mgmを使ってPDQPRIORITYを表示します）。 onstat -g mgmコマンド
これは9.20エンジンから実際に出力されます。7.xおよび8.xメモリー/リソース・グラント・マネージャーの出力と同じ基本要素を含んでいます。この場合には、インスタンスは限られたメモリーでLinux上で実行します。ご覧のとおり、20MBのDSメモリーがあり、1MBのQuantumsで割り当てられています。40のスキャンが可能ですが、その1つが実行中です。問題となっている照会（一番下のセッション33）は、すべてのメモリーと、必要な2つのスキャンの1つを使っています。マルチユーザー環境で特に関心を引くのは、Gateです。2番目の照会がサブミットされると「Ready Queries」のもとにリストされます。MemoryおよびPriorityのもとの待ち行列の長さは1になります。照会はメモリーまたはPDQPRIORITYでなければ実行できないからです。 onstat -g lscコマンド onstat -g xqpおよびxqsコマンド（XPS固有） リスト4. onstat -g xqpコマンドの出力
リスト4の出力は、以下の照会を参照しています。
オペレーションは、segidに基づいて降順に行われます。ですから最初のオペレーションはscan/groupです。group自体は、入力に1回と出力に1回の2回レポートされます。この場合「unique」文節が第2のgroupを強制しました。最後に、データは最終表に挿入されます。 リスト5〜10は、plan_idのコマンドonstat -g xqsの部分的出力を示しています。 簡略にするため、各セグメントの最初の1行と最後の3行以外をすべて削除しました。 リスト5. コマンドonstat -g xqsの部分出力（第1部）
このセグメントでは、16のスレッドが開始されました。各スレッドは、約32分で3940万行を読み取りました。 （timeの列は秒単位で表示されています。）たいしたことはありません。フィルターがなかったので、生成された行の数（rows_prod）はスキャンされた行の数（rows_scan）と等しくなっています。 リスト6. コマンドonstat -g xqsの部分出力（第2部）
各「group」オペレーションは、データの収集と実際のgroupという2つのフェーズに分割されます。したがって、リスト6で示されるセグメントは、最初のgroup（固有）オペレーションに行を押し込んだことを表しています。この場合も、16スレッドで同じ時間がかかり、これはスキャン時間と同時でした。生成された行数は、押し込まれた行数（rows_cons）と同じです。このオペレーションのメモリー量は2Kでした（16スレッド全体）。 リスト7. コマンドonstat -g xqsの部分出力（第3部）
リスト7は、リスト6で示されているgroupセグメントの後半部分で、ここでは固有の行が現れています。このフェーズではほぼ4GBのメモリーを使用していることに注目してください。ovfl列は、メモリーがオーバーフローしたことを示しています。ovflは、オーバーフローしたページ数のカウントではなく、オーバーフローしたセグメント数のカウントです。リストからオーバーフローが重大なものであるかどうか見分けることは困難です。しかし、このセグメントに3時間かかったことを考慮すれば、これが重大であったことは推測できます。これこそ、おそらくはスキャンをゲート制御したものでしょう。 リスト8. コマンドonstat -g xqsの部分出力（第4部）
リスト9は、2番目のgroupオペレーションの第2フェーズを示しています。この照会が作成された方法に原因があって、このオペレーションに他の4GBのメモリーが使用されました。このパフォーマンスの問題を解決するための1つの戦略としては、2つのgroupを分割することがあります。1つの表にselect uniqueを行い、次にその2番目の表にcount offを行います。 リスト10. コマンドonstat -g xqsの部分出力（第6部）
リスト10は、一時表に書き出す照会の最終挿入フェーズを示しています。オペレーションのこのフェーズは、2番目のgroupが出力の発行を始めるまで開始されませんでした。従って時間がはるかに少なくなります。 xmp_monスクリプト
出力は、照会が表tab1をスキャニング中であることを示しています。3億行がスキャンされ、最初のgroupオペレーションに渡され、そこで待機しています。上部にあるのは照会、分離レベル、PDQおよびメモリーといったいくつかの主要要素です。
照会の例
Table 1には2,900万行あり、Table 2には2,700万行あります。Table 1は4.5 GBで、Table 2は6 GBです。表はそれぞれ、単一のDB領域に置かれています。いずれの表のいずれのキーにも索引はありません。使用しているマシンは、メモリー4GB搭載の8プロセッサーSun 6500です。この照会がOLTP環境で実行されたとすれば、実行にさらに70または80時間を要したことでしょう。 往々にして、DSS照会のチューニングは単に「OLTP-think」から「DSS-think」への移行の問題ですが、まさにそれはここで行うことなのです。 最適化プログラムは、ハッシュ結合で例の照会を解決することにするはずです。そうしなければ、ここでの照会の場合のように表の1つが空であると最適化プログラムが見なしてしまうような、別の問題が生じることもあります（ですから、UPDATE STATISTICSを実行する必要があります）。PDQPRIORITYに何も設定されていないと想定することにしましょう。これはOLTP環境に特有のものです。これはつまり、効果的にオフにされるということです。照会が実行されると、どのようなことが生じるでしょうか。 以上の設定で、エンジンは最初に小さいほうの表（Table 2）を読み取り、フィルターを適用し、各レコードにハッシュ・テーブル・エントリーを構築します。これは、このハッシュ・テーブルをメモリー内に構築します。次に大きい表を読み取り、これをハッシュ・テーブルに突き合わせます。一致が見つかると、一致した行を結合の反対側に押し出します。 2つの表のうち「大きいほうの」表のフィルターが、その表からの結果セットを、「小さいほうの」表からの結果セットよりも小さくすることは可能です。最適化プログラムがこのことを認識していれば、フィルターを適用して大きいほうの表を先に読み取るよう適切な選択をして、ハッシュ・テーブルが大量のメモリーを占めないようにします。最適化プログラムには、この判断をより正確に行うために分散情報が必要ですが、これはUPDATE STATISTICS highまたはmediumを実行することによってしか得られません。 この照会で37分後に、Xtreeは両方の表がスキャンされてハッシュ結合に移動したことを示します。割り振られているメモリーが十分ではないので、一時ディスクからハッシュ・テーブル・ページをスワップ中にハッシュ結合は停止します。ハッシュ・テーブルの構築にさらに多くのメモリーが必要になると、それに応じてさらに多くのメモリーが必要になります。照会はOLTP環境で実行されているので、LRUバッファーは表からのすべてのデータと、関連するバッファー管理オーバーヘッドで一杯になり、全員の1日の仕事（あるいは1週間、1カ月かもしれません）を台無しにしてしまいます。ハッシュ・テーブル・メモリーの所要量は非常に大きいので、一時スペースにオーバーフローした照会は、もし適正にセットアップされていなければ、他のすべての一時スペースも奪い去ってしまったかもしれません。数日間実行したのち、おそらくはとにかく照会を強制終了することになるでしょう。これがまさに、典型的な40日の照会の始まりです。
基本的なチューニングの手順 この照会をチューニングする際に、表スキャンおよび結合という2つの部分で進めると効果的です。最初に、以下の照会を使って、それぞれの表をスキャンするだけで要する時間のベンチマークを得ることにしましょう。こうすることで、照会時間を予測できるようになるだけでなく、照会がスロー・ダウンしたときに目立たせ、しかもかなりの速度で表を自ら読み取れるようになります。
この結果は、Table 1に17分、Table 2に20分を要し、毎秒約30,000行を示しています。これらの結果は挿入されます。実際の読み取り時間は、いずれの表も37分でした。
データの分散
PDQPRIORITYにより並列を使用可能に
注： 結果：
バッファー・オーバーヘッドを解消するためのライトスキャンの強制 ライトスキャンはDSS照会用に設計されています。ライトスキャンは、通常の常駐メモリー・バッファー・キャッシュを使用するのではなく、オーバーヘッドのはるかに少ない独自のライトスキャン・バッファーを使用します。照会は各自バッファー・プールのセットを確保します。そのため、読み取り速度に劇的な効果が得られます。 ライトスキャンを強制するための秘訣は、読み取り対象の表が常駐メモリー・バッファー・サイズよりも大きくなるようにし、ISOLATIONモードを共用表ロックでDIRTY READ, OR Repeatable/Commiteed Read Isolation Levelに設定することです。また、環境変数（エクスポートLIGHT_SCANS=FORCE）を設定することもできます。（IDSでは、ライトスキャンはvarcharsを持つ表に対して使用可能にできません。） 割り当てられるライトスキャン・バッファーの数は、RA（先読み）設定の係数です。Informix Masters Seriesのトレーニング・マニュアルでは、次のようになっています。
MAXAIOSIZEは各ポートの定数です。一般には16です。ただし、HP、SolarisおよびSequentでは、それぞれ60、60、64となっています。 RA_PAGESおよびRA_THRESHOLDの値をそれぞれ最大の128および120まで上げると、さらにライトスキャン・バッファー・プールが増えて、望ましい状態になります。 ライトスキャン・バッファーに対応できるよう、仮想セグメント（SHMVIRTSIZE）も大きくするようにしてください。 以前のベンチマークの実行では、エンジンはライトスキャンを使用しませんでした（ライトスキャンのパフォーマンスへの効果を示すため、意図的にそうしました）。エンジンでUPDATE STATISTICSを実行することにより、最適化プログラムは例の照会で読み取られている表のほうが常駐メモリー・バッファー・プールよりも大きいことを認識して、再びベンチマークが実行されるときにはライトスキャンを使用します。 UPDATE STATISTICSはもちろん、ライトスキャンを機能させるカギですが、それは一体どの程度でしょうか。UPDATE STATISTICS mediumまたはhighは、大規模なデータウェアハウスで何時間も、場合によっては何日も、稼働する傾向があります。その結果、索引付き結合またはフィルタリングを行う場合に非常に便利になりますが、このハッシュ結合の場合には、とにかく表全体のスキャンに目を配ることになるので、迅速なUPDATE STATISTICS lowを実行して、後はハッシュ結合に任せます。環境変数DBUPSPACEを設定すると、UPDATE STATISTICSを実行するために必要な時間を大幅に短縮することができます。その他UPDATE STATISTICSのチューニングに関するヒントは、 http://www7b.boulder.ibm.com/dmdd/zones/informix/library/techarticle/miller/0203miller.html のサイトを参照してください。 ベンチマーク照会を再び実行すると、結果はTable 1では43秒、Table 2では50秒となり、毎秒約700,000行になります。 私は、CPUVPあたり3つのスキャン・スレッドの制限があると告げられました。したがって、DB領域の数の上限は3 x CPUVPということになります。 現在PDQPRIORITY=1設定を保持している照会を返すとするならば、得られる結果にはさほど大きな相違はないでしょう。表のスキャンはより迅速になりますが、照会には依然としてメモリーが不足してほとんど息も絶え絶えの状態です。 次のチューニング作業は、結合そのものに迫ります。いくつかのオプションも利用できます。まず、索引を使用してネストされたループ決増で照会を実行してみましょう。 索引の追加の検討 索引を追加することで、最適化プログラムにデータに到達できる別のパスをもたらします。表の1つが十分に小さいと認識すれば、最適化プログラムはその表を読み取ることを選び、すべての行に対して大きい表をプローブして（索引を使用）結合を実行します。問題となる点は、エンジンがプローブを実行できる速度で、秒あたりの千単位で測定されます。3,000 行/秒で3,000万行というのは、ここで使用しているマシンでは楽観的なものですが、これは10,000秒、つまり約3時間にもなります。これではあまりに遅すぎます。索引を断片化して、それを表から切り離しても、ネストされたループをもっと速くするのに十分な劇的変化をプローブ速度にもたらしはしません。ですから、この場合には索引を追加しても何の足しにもならないばかりか、最適化プログラムを混乱させれば実害の及ぶおそれもあります。 動的ハッシュ結合を試してみましょう。 ハッシュ結合の強制 PDQPRIORITYは、メモリー割り振りを制御します。究極のPDQPRIORITY設定とは、(PDQPRIORITY (PDQ_PRIORITY/100 * MAX_PDQPRIORITY/100) * DS_TOTAL_MEMORYです。1.5GBのDS_TOTAL_MEMORYがあり、そのうち1%を占める（PDQPRIORITY=1）場合には、ハッシュ・テーブルを構築するために15MBのメモリーがあります。15MB/20バイト(ハッシュ・テーブル・エントリー) = 786,000ほどのエントリーになります。これは大まかな推測ですが、これほど多くのエントリーの後に、データベースはハッシュ・テーブル・エントリーを一時ディスクに押し出さなければなりません。16のスキャン・スレッドで、データベースはおそらく毎秒約160,000行を読み込んでいることになりますから、5秒でハッシュ・テーブルが満杯になって上記のメモリー割り振りにより一時ディスクに押し出す必要が生じます。 ハッシュ・テーブルのサイズを求める実際の公式は、32 + keysize（キーサイズ） + rowsize（行サイズ）」です。したがって、ハッシュ・テーブル・サイズは32 + 2 + 155、つまりtab1レコードあたり189バイトとなります。* 2,900万 = 5.4GB ここでの重要な改善点は、tab1レコードのサイズを低下させて、ハッシュ・テーブルのサイズを減らすことでしょう。私は、計算の「rowsize」が検索された列のrowsizeであると知らされていましたが、いまだそれを実証しようとしたことがありません。 注目すべき最初のことは、ハッシュ・テーブルを構築すると実際にスキャン速度を毎秒約 300,000行も低下させるという点です（結果には変動があります）。 40 PDQでは、この照会は22分12秒かかり、マッチ率は毎秒約40,000行でした。メモリー量が増えると、一時ディスクとの間のスワッピングの量を低下できるだけではなく、メモリー内のハッシュ・テーブルのサイズを増やして秒あたりのマッチ率も高められます。 ハッシュ結合方式を活用することで、例の照会は、約5.5分という予測合計に対して毎秒約90,000行（80 PDQ）で実行することが明らかになっています。これは、ハッシュ・テーブル全体がメモリーに収まらずに照会が表の一部をディスクからスワップしなければならなくなるという大いなる例外といえます。そのためこの照会の実際の結果が8分40秒になっています（PDQ = 80）。照会の突き合わせフェーズで、マッチ率は90,000行/秒でした。1,700万の突き合わせの後、データベースはスワッピングを開始しました。10の合計スワップを実行し、各スワップは約10秒の長さでした（100秒、つまり1分40秒）。表全体がメモリーに収まらなかったために、照会も90,000行/秒の速度を維持することができませんでした。そのため（スワップ時間なしで）照会が（最初に予測されたように）5.5分ではなく7分で実行します。ハッシュ結合に十分なメモリーがある場合には、スワップする必要はなくなります。 場合によっては、よかれと考えた意図にもかかわらず、最適化プログラムのヒントを使用したにもかかわらず、最適化プログラムはもどかしそうに照会をネストされたループ・パスを押し下げ続けることもあります。このような場合には、なにか索引で見つけ出せないようなものを示して、最適化プログラムをハッシュ結合へと巧みに導くこともできます。
「+ 0」は高価ではなく、しかも照会をハッシュ結合として実行するよう強制します。これは、tab1.keyが索引として最適化プログラムに認識できなくなっているためです。
一時ディスク バランス モデル変更
実のところ私は、BUFFERSを20000にし、必要に応じてOLTPアクティビティーをサポートするように設定するほうを選んでいます。表は十分な大きさがあるのでバッファーをあらゆる場合に小さく見せ、データにOLTPスタイルの作業が行われます。 まとめ 照会がフィルターで制限されない場合には、2つの表の完全な結合が必要になります。これは、索引読み取りで行うと極めて非効率的です。2つの表をスキャンして、メモリー内で結合を行うほうがはるかに効率的になります。ハッシュ結合は、これを行う理想的な方法です。したがって、チューニングは照会の3つの段階（scan1、scan2、および結合そのもの）を短縮する問題となります。ライトスキャンにより、データベースはハッシュ・テーブルの構築中に最初の表から読み取りを行うことができます。2番目の表で、データベースは、ほんのわずかだけ遅れをとって読み取ることができ、ハッシュ結合を含めます。さらに十分に高いPDQPRIORITYにより、データベースは、ハッシュ・テーブルから一時ディスクにスワップして時間を無駄にすることなくメモリー内でハッシュ結合を行える十分なメモリーを確保します。ハッシュ・テーブルが一時ディスクに押し出された場合、一時ディスクが良好に配置されていればさほど高価にはなりません。 読み取りロードを複数のディスクに分散して、十分なメモリーを予約し、十分な一時ディスクをとっておくことができる場合には、5分のウィンドウ内で各表をスキャンし、さらに1、2分で結合をまとめて、どのような照会時間も短縮することができます。照会に40時間、あるいは40日もかかるようであれば、先に述べた状況は実現していないのです。
IBM、DB2、および WebSphere は、米国およびその他の国におけるIBM Corporation の商標です。
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
