Comments
Description
Transcript
第 6 回 パフォーマンスの問題判別 DB2 問題判別 習熟シリーズ
DB2 問題判別 習熟シリーズ 第 6 回 パフォーマンスの問題判別 第6回 パフォーマンスの問題判別 - インデックス ・セクション1:概要 ・セクション2:パフォーマンス問題のデバッグのロードマップ ・セクション3:DB2のモニター ・セクション4:バッファー・プール ・セクション5:ロッキング - ロック待機、ロック・タイムアウト、ロック・エスカレー ション ・セクション6:ソート ・セクション7:入出力の問題の追跡と指標 ・セクション8:アクセス・プランと照会オプティマイザー ・セクション9:チューニング操作 ・セクション10:結論 セクション1: この回について 対象読者と前提事項 この回は、パフォーマンス問題のデバッグに関する知識をほとんど(あるいはまったく)持 たない読者を対象としています。ただし、SQLコマンドやDB2コマンドの実行方法の知 識、基本的なデータベースとインスタンスのインストールおよび構成の技術、DB2のトラ ンザクション・モデルの高レベルの知識があると役に立ちます。 この回では、GUIインターフェースからは利用できない手法を検討します。したがって、 コマンド行の構文、手法、解釈が焦点となります。 目的 この回では、DB2 UDBのパフォーマンスの問題を見つけてデバッグする方法を学び、パ フォーマンスに影響を与える主要な要因を各モジュールで検討します。また、DB2のモニ ター機能やOSレベルのさまざまな診断情報を活用して、パフォーマンスの問題がありそ うな領域を切り分ける方法についても説明します。最後に、DB2 UDB照会オプティマイ ザーの概要を簡単に紹介し、オプティマイザーによる決定を利用してパフォーマンスの問 題をさらに詳しく調べる方法について説明します。 この回には、リレーショナル・データベースの理論、DB2の設計、および各トピックをよ り深く検討できる実践的な例が盛り込まれています。ここでの最終的な目標は、当然のこ とながら、DB2のパフォーマンス機能を効果的に切り分け、インテリジェントに調整し、 正しくモニターし、十分に活用するために必要な基礎を固めることです。 セットアップ 用意されているデータベース・イメージには、SAMPLEデータベースに変更を加えたバー ジョンが含まれています。この変更は、オプティマイザー・コンポーネントの実習で必要 になります。このイメージを使用するには、次の手順に従ってください。 1. 次のファイルをダウンロードします。 PERFTUT.0.rbahra.NODE0000.CATN0000.20021125175204.001.gz 2. 次のコマンドを使って、ダウンロードしたファイルを一時ディレクトリーに解凍し ます。 gzip -d PERFTUT.0.rbahra.NODE0000.CATN0000.20021125175204.001.gz 3. ファイルの解凍が完了したら、次のコマンドを使ってイメージを復元できます。 db2 restore db perftut into perftut このコマンドは、perftutという名前のデータベースが以前に作成されていないことを前提 としています。また、このコマンドは、ステップ(2)で使用した一時ディレクトリーから 実行する必要があります。perftutという名前を使用できない場合や、この名前が気に入ら ない場合は、上のrestoreコマンドを次のように自由に変更してかまいません。 db2 restore db perftut into <changed database name> なお、ここでは、実行されているバージョンのDB2にアクセスできること、およびテスト に使用するデータベースに対するインスタンス所有者権限があることを前提としていま す。 表記規則 以下のような単純な表記規則を使用しています。 太字体 - 特定のフィールドや値の強調 斜体 - トピックにとって本質的ではない追加のコメントや詳細情報 モノスペース - コマンド、照会、結果の出力 著者について 著者のRaj Bahraは、1999年からIBMトロント研究所で働いています。最近はDB2照会オプ ティマイザーを主な対象領域としており、オプティマイザー特有の問題についてDB2ユー ザー・コミュニティーを支援しています。 Raj Bahraの連絡先については、http://www.ibm.com/contact/employees/usのIBM Global Directoryをご覧ください。電子メール・アドレスを調べることができます。 セクション2:パフォーマンス問題のデバッグのロードマップ 概要 本編は、モジュール方式になっています。各主要セクションは、それぞれ別個に参照する ことも、全体としてまとめて読むこともできます。このセクションは、本編のロードマッ プとなります。また、パフォーマンスの問題をデバッグするための問題解決方法もここで 紹介します。パフォーマンスの問題をトラブルシューティングする際には、このセクショ ンをチェックリストとして使用できます。 最初の分析 パフォーマンス低下の報告を受けたら、まず以下の問いについて確認します。これによ り、原因究明の作業をどこから始めればよいのかをすばやく特定できます。 問題が最初に発生したのはいつですか? 問題が長期間にわたって発生している場合は、データベース・モニターのスケジュールが 実装されていれば、履歴データを使って違いを見つけることができます。これにより、ま ずシステムの振る舞いの変化に焦点を絞り、その後、それらの変化が持ち込まれた原因の 究明に集中できます。モニターについては、この回で後に説明します。 パフォーマンスの問題は持続的ですか? それとも断続的ですか?} パフォーマンスの低下が持続している場合は、システムの負荷が増えていないか、共用 データベース・リソースがボトルネックになっていないかを確認します。このほか、ユー ザー・アクティビティーの増加、複数の大きなアプリケーション、ハードウェア・デバイ スの除去なども、パフォーマンス低下の原因として考えられます。パフォーマンスが短い 期間だけ低下している場合は、まず、パフォーマンスが低下しているときに共通して実行 されているアプリケーションやユーティリティーを探します。パフォーマンスの問題が発 生しているアプリケーションのグループがユーザーから報告されている場合は、それらの アプリケーションに焦点を絞って分析を開始できます。 システム全体の問題ですか? それともDB2とそのアプリケーションだけの問題ですか? システム全体でパフォーマンスが低下している場合は、DB2外部の問題であると考えられ ます。この場合は、オペレーティング・システム・レベルでの対策が必要な可能性があり ます。パフォーマンスが低下しているのがDB2アプリケーションだけの場合は、当 然、DB2の動作に焦点を絞ります。 1つのアプリケーションを切り分けることができた場合、疑わしい照会はありますか? 疑わしいアプリケーションが1つになった場合は、さらに、パフォーマンスが低下してい る照会をユーザーが報告しているかどうかを調べます。これにより、問題を、1つのアプ リケーションと一連の照会に絞り込むことができる可能性があります。 パフォーマンスの低下になんらかの共通点がありますか? それともまったく不規則です か? 共通の表、表スペース、索引などが含まれていないかどうかを確認します。含まれている 場合は、それらのオブジェクトが問題の焦点になります。このほかに考えられる焦点とし ては、参照保全制約、外部キー・カスケード、ロッキングの問題などがあります。 最初の段階でのこうした短い問いは、パフォーマンスの問題のデバッグに向けた行動計画 を練る上で非常に重要です。 DB2の確認 最初の調査の結果、DB2がパフォーマンスの問題の原因になっている可能性が高いことが わかった場合は、以下の問いについて確認します。 ユーザーから応答時間が異常に長いと報告されているのはすべてのアプリケーションです か? 1. 大量の入出力が検出されませんでしたか? 検出された場合は、次のような原因が考え られます。 a. デバイスが過負荷になっている。これを確認する方法については、OSモニターの セクションで説明します。 b. 大量のソートや一時スペースの使用。これを確認する方法については、ソートと オプティマイザーのセクションで説明します。 c. 巨大な表の表スキャン。これを確認する方法については、オプティマイザーのセ クションで説明します。 d. バッファー・プールの不足。これについては、バッファー・プールのモニターの セクションで説明します。 2. CPUの使用率が高くなっていませんか? 高くなっている場合は、次のような原因が考 えられます。 a. 大量のソート。これを確認する方法については、ソートとオプティマイザーのセ クションで説明します。 b. DB2エージェントの制約。バッファー・プールのセクション(特に非同期入出力の 説明)を参照してください。 c. バッファー・プール・サイズの不足。バッファー・プールのセクション(特にヒッ ト率の説明)を参照してください。 3. (1)と(2)の両方が当てはまりますか? a. ユーザーの数は増えていませんか? チューニング操作のセクションを参照してくだ さい。 b. スピルした大きなソート。これを確認する方法については、ソートとオプティマ イザーのセクションで説明します。 c. アプリケーションの設計に問題はありませんか? 4. (1)と(2)のどちらも当てはまりませんか? a. アプリケーションはロックを待っていませんか? ロッキングと並行性のセクション を参照してください。 b. アプリケーションはデータを待っていませんか? データが妥当な速度で利用可能か どうかを調べる手法については、この回のバッファー・プールのセクションで紹 介します。 ユーザーから応答時間が異常に長いと報告されているのは1つのアプリケーションまたは 照会ですか? その場合は、次のような原因が考えられます。 1. ソート - ソートとオプティマイザーのセクションを参照してください。 2. 並行性の問題 - ロッキングと並行性のセクションを参照してください。 3. 古くなった統計情報 - オプティマイザーのセクションを参照してください。 これらは、パフォーマンスの問題に直面した際に問うべき一般的な問いです。現代のシス テムの規模や複雑さからすれば、このリストは決して包括的なものではありません。パ フォーマンスの問題には実にさまざまな原因が考えられます。とはいえ、これらの問い は、問題の原因を特定する作業を論理的に進めるための開始点にはなります。 セクション3:DB2のモニター モニターの目的 データベースのモニターは、データベース・サーバーのチューニングの重要な要素です。 まず、データベース・モニター・プランを練る必要があります。これにより、問題を見つ けやすくなるだけでなく、履歴データを参照して変化を切り分けたり、変化の影響を推測 したり、ユーザーやアプリケーションのアクティビティーや振る舞いの理解を深めたりで きます。もちろん、データベースのパフォーマンスを改善するのにも役立ちます。 当然ながら、モニターにはオーバーヘッドが伴います。モニター・プランを練る際には、 このことを考慮に入れて、モニターの頻度やボリュームを調整する必要があります。 データベースのモニターのための手法 DB2では、データベースをモニターするための手法が3つあります。この回では最初の2つ に焦点を絞ります。 ● ● ● スナップショット・モニター - ある特定の瞬間の照会操作データベース状態を提供 します。スナップショット・モニターは、データベースおよびそのアプリケーショ ンの現在の状態を確認するのに便利です。 イベント・モニター - 特定のアクティビティーについて、ある期間にわたる照会操 作データベース状態を提供します。イベント・モニターは、データベースやアプリ ケーションの状態の変化を特定するのに便利です。接続、表、ステートメントな ど、さまざまなイベントに関する変化の詳細情報を収集するために使用されます。 グラフィカル・パフォーマンス・モニター - スナップショット・モニターとイベン ト・モニターからデータを受け取ります。このユーティリティーについては、ここ では取り上げません。 メモリーが効果的に使用されるように、データベース・モニターに使用するメモリーの量 を設定することができます。データベース・マネージャー・フィールドのMONHEAPSZ パラメーターは、DB2STARTに割り当てられてモニターが行われる際に使用されるメモ リーの量を指定します。このパラメーターを0 (ゼロ)に設定すると、モニターは一切行わ れなくなります。 モニター・ツールを使用するには、SYSADM、SYSCTRL、SYSMAINTのいずれかの権限 が必要です。 モニターのレベル モニター・ユーティリティーは、システム内のさまざまなレベルで情報を収集します。 ● データベース・マネージャー - モニターは、インスタンスが開始されたときから停 止されたときまでの詳細情報を収集します。 ● ● データベース - このレベルのモニターは、最初の接続のとき(活動化)に始まり、最 後の接続が切断されるとき(非活動化)まで続きます。 アプリケーション - このレベルのモニターは、アプリケーションがデータベースに 接続したときに始まり、アプリケーションがデータベースとの接続を切断するまで 続きます。 このほか、特定の項目を以下のような機能グループに分割することによって、モニターの 詳細をさらに細分化することもできます。 SORTS LOCKS TABLE ACTIVITY BUFFERPOOL ACTIVITY UNIT OF WORK SQL STATEMENTS たとえば、長時間にわたるロック待機が観察された場合は、LOCKグループをモニターす ることによって問題を切り分けることができると考えられます。 モニターへのパス 以下のコマンドは、現在のモニター設定を追跡します。これにより、機能グループを使用 可能にしたり、既存のモニター・データを「クリア」したりできます。これらのコマンド は、DB2 CLPセッションから実行できます。 GET MONITOR SWITCHES 以前にリストされた機能グループの現在の状態を返します。オンになっているグループが ある場合、そのグループがモニターされます。 UPDATE MONITOR SWITCHES USING <switch name> ON | OFF 各機能グループの状態を調整できます。つまり、このコマンドを使用すると、どのグルー プでモニターを使用可能にするのかを指定できます。 RESET MONITOR ALL | FOR DATABASE <database alias> モニター要素をゼロにリセットして、新しくやり直すことができます スナップショット・モニター GET SNAPSHOTコマンドのパラメーターは、詳細さのレベルと、モニターのタイプを決 定します。このコマンドによって返される統計情報には、特定時点の情報と、最後にreset monitorコマンドを実行してからの累積的な情報とがあります。 累積的なカウンターの例としては、deadlocks detectedがあります。deadlocks detectedは、 最後にreset monitorコマンドが実行されてからのデッドロックの数を保持します。イベン ト固有のフィールドの例としては、lock list memory in useがあります。このフィールドに は、現在ロック・リストに使用されているバイト数が格納されます。このフィールドは、 スナップショットを取得するたびに、アプリケーションによる接続/切断およびロックの 取得/解放に伴って変化します。 get snapshotコマンドの詳細については、『System Monitoring and Tuning Guide』を参照し てください。 ここで例として、perfpdデータベースを使ってスナップショットを取得してみます。CLP セッションから、次のコマンドを実行します。 db2 db2 db2 db2 db2 connect to perfpd reset monitor all get snapshot for database on perfpd "select * from syscat.bufferpools" get snapshot for database on perfpd イベント・モニターの作成 DB2内のイベント・モニター機能には、高度な柔軟性も備わっています。まず最初に、イ ベント・モニターを作成します。 例として、perfpdデータベースのイベント・モニターを作成してみます。 db2 create event monitor evmonex1 for statements write to file './testfile' maxfiles 3 maxfilesize 1024 nonblocked append 上のコマンドによって、evmonex1というイベント・モニターが作成され、すべてのス テートメント・イベントがキャプチャーされます。最大で4MBのファイルが3つ作成され ます。 コマンド構文の詳細については、『DB2 System Monitor Guide and Reference』を参照して ください。 イベント・モニターのフォーマット create event monitorコマンドの出力ファイルは、判読不可能なフォーマットで書き込まれ ています。この出力ファイルを判読可能にするには、db2evmonというツールを実行する 必要があります。 db2evmon <database alias> <event monitor name> 練習のために、先ほど作成したイベント・モニターevmonex1の出力をフォーマットして みてください。 db2evmon perfpd evmonex1 オペレーティング・システム(OS)レベルのモニター ここでは、AIXで利用できるオペレーティング・システム・レベルのツールをいくつか紹 介します。これらのツールは、調査結果を確認するのに非常に有効です。また、潜在的な 問題を見つけることさえ可能です。 IOSTAT このユーティリティーを使用すると、入出力(I/O)の待ち時間を測定できます。一般 に、IOWAITが高くなるとパフォーマンスが低下します。例として、次のコマンドを実行 してみてください。 iostat 5 %iowait - ローカル入出力を待っている間にCPUがアイドル状態だった時間のパーセン テージ %idle - ローカル入出力を待っていない間にCPUがアイドル状態だった時間のパーセ ンテージ CPUに対して準備ができているプロセスがなく入出力を待っているプロセス が1つでもあれば、その時間は入出力待ちに属するものとされます。一般に、 入出力待ちが20∼25%以上の場合は、ディスク・ボトルネックを調査するこ とになります。 %tm_act - ディスクがビジー状態だった時間のパーセンテージ ビジー状態のドライブに対してアイドル状態のドライブがないかを確認します。利用の激 しいドライブからそれほど利用されていないドライブにデータを移動すると、ディスク・ ボトルネックを軽減するのに役立つ場合があります。この回の後半では、このツールをデ バッグ・ツールとして使用する方法を紹介します。 セクション4:バッファー・プール バッファー・プールとボトルネックの切り分けの概要 バッファー・プールの問題は、データベースのパフォーマンスにおける重要な要因になる 可能性があります。ここでは、まずバッファー・プールの基本的な概念を紹介し、その 後、最良実例に焦点を絞り、パフォーマンスの低下を招く可能性がある要因を特定しま す。 概要 ごく簡単に言うと、バッファー・プールは、データベースの「メモリー内」作業域として 機能します。サーバーは、すべてのトランザクション・アクティビティー(読み取り、書 き込み、更新、削除など)を、バッファー・プールを使って実行します。データは、アプ リケーションからの要求に基づいて、ディスクからバッファー・プールにコピーされま す。したがって、たとえば、表ABCから製品fooの販売数を選択するSQL照会を実行した 場合、DB2は、表ABCから製品fooのデータをバッファー・プールに抽出し始めます。わ かりやすく言うと、バッファー・プールは準備地点のようなものです。 データがバッファー・プールに移動するには、非同期入出力と同期入出力の2つの方法が あります。データ・ページは、db2agent (同期入出力の場合)またはプリフェッチャー(非同 期入出力の場合)によって、バッファー・プールに置かれます。ページがバッファー・ プールからディスクに書き込まれるときには、db2agent (同期入出力の場合)またはページ ・クリーナー(非同期入出力の場合)が関与します。 db2agentは、DB2プロセス・モデルの最も重要な要素です。これらのエージェントは、 データベース操作の処理を担当します。 プリフェッチャーは、アプリケーションの必要性を予測してデータ・ページをバッファー ・プールに(非同期で)読み込みます。UNIX(R)では、db2pfchrというプロセス名になりま す。これらのページは、ほとんどの場合、必要になる直前に読み込まれます(望ましい ケース)。しかし、プリフェッチャーは、使用されないページをバッファー・プールに読 み込んで、不要な入出力を発生させることもあります。たとえば、アプリケーションが表 の読み取りを開始したとします。この表の読み取り要求が検出されると、プリフェッチが 開始されます。しかし、アプリケーション・バッファーがいっぱいになったところで、ア プリケーションが読み取りを中止します。この間にも、多くの追加ページのプリフェッチ が行われています。この場合、使用されないページのために入出力が発生し、それらの ページによってバッファー・プールの一部が占有されることになります。 ページ・クリーナーは、バッファー・プールをモニターし、ページをディスクに非同期で 書き込みます。これらのDB2プロセスのプロセス名は、db2pgclnrです。ページ・クリー ナーには、以下の目的があります。 ● ● エージェントがバッファー・プール内で必ずフリー・ページを見つけられるように する。エージェントがバッファー・プール内でフリー・ページを見つけられない と、エージェント自身がページをクリーニングしなければならなくなるため、関連 するアプリケーションのパフォーマンスが低下します。 システム・クラッシュが発生した場合のデータベース・リカバリーを高速化する。 ディスクに書き込まれたページが多ければ多いほど、データベースをリカバリーす るために処理しなければならないログ・ファイル・レコードの数は少なくなりま す。 変更されたページはディスクに書き出されますが、すぐにバッファー・プールから 削除されるわけではなく、新しいページを読み込むためのスペースが必要になるま ではバッファー・プールに残ります。 DB2の並列処理機能を活用するには、db2agentsには照会の処理を任せ、プリフェッチャー と入出力クリーナーを最大限に使って入出力作業を実行します。こうしてそれぞれのタイ プのプロセスの専門能力を活用することにより、最大限のスループットを引き出すことが できます。 バッファー・プールの最良実例 専用データベース・サーバーでは、一般的な目安として、メイン・メモリーの75パーセン トをデータベースのバッファー・プールに割り当てます。バッファー・プールの大きさが 十分であれば、システムの入出力要求を減らすことができます。 DB2照会オプティマイザーは、アクセス・プランを生成する際に、バッファー・プールの サイズを考慮に入れます。バッファー・プールのサイズを変更した場合は、変更を反映す るために静的パッケージをバインドし直すことを検討してください。 すべてのバッファー・プールのサイズも考慮に入れる必要があります。メモリーの容量が 小さいシステムや、メモリーの大半を使用する別のアプリケーションを実行しているシス テムでは、ページ・スワッピングが大量に発生する可能性があります。ページ・スワッピ ングは、アクセスするデータ・ページを保持するためのメモリーが不足していると発生し ます。ページ・スワッピングが発生すると、他のページのスペースを確保するために、 ページが一時ディスク・スペースに書き込まれます。 入出力クリーナーは、データベースが置かれている物理デバイスの数より1つか2つ多くな るように構成する必要があります。 バッファー・プールのモニター - ヒット率 バッファー・プールの目的は、データ・ページをディスクから読み取る代わりに、メモ リー内にデータ・ページのコピーを保持することです。したがって、バッファー・プール の効率は、要求されたページがどのくらいの頻度で既にバッファー・プール内にあったか を調べることで測定できます。このヒット率が、バッファー・プールの効率の尺度になり ます。ヒット率を計算するための一般的な式は、次のようになります。 (1-((pool_data_physical_reads+pool_index_physical_reads)/ (pool_data_logical_reads+pool_index_logical_reads))) * 100 この値が100パーセントに近づくほどディスク入出力の頻度が低くなり、データの読み取 りのオーバーヘッドが小さくなります。この式は、バッファー・プールにキャッシュされ るすべてのページ(索引およびデータ)を考慮に入れています。「物理/論理」の関係さえ維 持されていれば、索引の詳細またはデータ・ページの詳細のみを含むように単純化するこ ともできます。 これらのフィールドは、データベース、表スペース、バッファー・プールの各機能レベル で調べることができます。 以下のサンプル値を使って、バッファー・プールのヒット率を算出してみてください。 Buffer Buffer Buffer Buffer pool pool pool pool data logical reads data physical reads index logical reads index physical reads = = = = 701865 50842 444859 24174 上の値に対するバッファー・プールのヒット率は、約93.4です。この値は100にきわめて 近いため、このバッファー・プールはかなり効率的に実行されていることになります。 バッファー・プールのヒット率を毎日または毎週のペースで追跡すると、バッファー・ プールの効率の履歴を記録していくことができます。この記録は、データベースでパ フォーマンスの問題が報告された場合に、評価の基準として使用できます。 ヒット率がかなり低いバッファー・プールが見つかった場合、そのバッファー・プールは ほとんど使用されていない可能性があります。これを修正するには、次のような方法があ ります。 ● ● データと索引を異なる2つのバッファー・プールに分割し、別々にモニターする。 1つのバッファー・プールを使用し、索引のヒット率がそれ以上もう上がらなくな るまでサイズを大きくする。索引のバッファー・プールのヒット率は、次の式で計 算できます。 (1 - ((pool_index_p_reads) / (pool_index_l_reads))) * 100% 多くの場合、1つ目の方法の方が有効ですが、索引とデータを別の表スペースに置く必要 があるため、既存のデータベースでは利用できない場合もあります。また、チューニング しなければならないバッファー・プールが2つになるため、チューニング作業がより難し くなります。メモリーが限られている場合にはとりわけ困難な作業になります。 セクション5:ロッキング - ロック待機、ロック・タイムアウト、ロック・エスカレー ション、デッドロック ロック待機とロック・タイムアウト オブジェクトに対する既存のロックと互換性がないロックを要求しているアプリケーショ ンや、まだ満たされていないロック要求は、ロック要求の保留キューに配置されます。 ロック要求は、タイムアウト期間を超えるかデッドロックであることが判明するまで、待 機中のアプリケーションのために保持されます。 データベース・パラメーターLOCKTIMEOUTでは、ロックが利用可能になるのをアプリ ケーションが待つ時間を設定できます(単位は秒)。このタイムアウト期間を超えると、待 機中のアプリケーションはSQL0911 R.C. 68エラー・メッセージを受け取り、アプリケー ションの作業単位はデータベース・マネージャーによって自動的にロールバックされま す。 LOCKTIMEOUTのデフォルト値は-1で、ロック・タイムアウトが使用不可になっていま す。したがって、ロックを待っているアプリケーションは、ロックが解放されるまで無期 限にロックを待ち続けます。トランザクション環境では、この値をまず30秒に設定してみ ることをお勧めします。その後、必要に応じてチューニングを行い、より適切な値を見つ けます。 ロック・エスカレーション ロック・エスカレーションは、以下の2つのシナリオで発生します。 1. ある1つのアプリケーションが、許可されているより多くの数のロックを要求した 場合。 2. アプリケーションのロック要求によって、システム上のデータベース・ロックの最 大数を超えた場合。 いずれの場合も、データベース・マネージャーは、表ロックを取得して既存の行ロックを 解放することにより、ロッキングに割り当てられたメモリーを解放しようとします。この とき目標となるのは、別のアプリケーションのためのロック・メモリーを確保することで す。 以下の2つのデータベース・パラメーターは、直接ロック・エスカレーションに影響しま す。 locklist - ロック記憶域に割り当てられる4kページの総数(メモリー・モデルを参照) maxlocks - 1つのアプリケーションが使用できるlocklistのパーセンテージ これらの値のバランスを取るには、チューニングとモニターが必要になります。ロッキン グのパターンや、アプリケーションがロック・メモリーを使用する方法は、ワークロード や照会の動作による影響を受けます。 デッドロック デッドロックは、競合するロック要求によってアプリケーションが作業単位を完了でき ず、そのロック要求はその作業単位が完了するまで解決できない、という場合に発生しま す。デッドロックは、DB2デッドロック検出機能によって処理されます。デッドロックが 検出されると、デッドロック検出機能は、どちらか一方を選んで自動的にロールバック し、SQL0911 R.C. 2を発行します。一方がロールバックされることによってロックの競合 が取り除かれるため、もう一方のアプリケーションは処理を継続できます。 DB2デッドロック検出機能がデッドロックをチェックする頻度は、DLCHKTIME (1000 ∼600000ミリ秒の範囲)で制御できます。この値を高く設定すると、デッドロックの チェックまでの時間が増大しますが、デッドロックの検出のオーバーヘッドはなくなりま す。この場合、アプリケーションが長時間にわたってデッドロック状態のままになる可能 性があります。デッドロックのチェックまでの時間に小さな値を設定すると、デッドロッ クをより早く検出できますが、チェックのためにさらなるオーバーヘッドが発生します。 ロック待機とロック・タイムアウトの追跡 ロック待機とロック・タイムアウトは、スナップショット・モニターの出力で簡単に追跡 できます(スナップショット・モニターの実行方法については、既に学びました)。対象と なるフィールドは、Number of Lock Timeoutsです。以下はスナップショット出力の抜粋で す。 Locks held by application Lock waits since connect Time application waited on locks (ms) Deadlocks detected Lock escalations Exclusive lock escalations Number of Lock Timeouts since connected Total time UOW waited on locks (ms) = = = = = = = = 46 12 96443 0 0 0 5 Not Collected この例では、アプリケーションがデータベースに接続している間に、ロック・タイムアウ トが5回発生しています。また、ロックが解放されるのをアプリケーションが96443ミリ秒 待っていることもわかります。次の式で平均待ち時間を計算できます。 (Time application waited for Locks / Lock waits since connect) 上の例では、各ロック待機の平均待ち時間は8036ミリ秒になります。この値が異常なしと 見なされる環境もあれば(意思決定支援システムなど)、待ち時間が長すぎると見なされる 環境もあります(オンライン・トランザクション処理など)。当然ながら、ユーザーのニー ズやビジネスの要件を基準に判断する必要があります。 通常の操作レベルに比べてロック・タイムアウトが多すぎる場合は、極端に長い期間にわ たってロックを保持しているアプリケーションがある可能性があります。また、ロック・ タイムアウトの数は極端に少ないのに、照会の応答に時間がかかっている場合は、ロック ・タイムアウトのパラメーターの設定値が高すぎる可能性があります。 ロック・タイムアウトとロック待機の原因を特定するために、次の例を試してみてくださ い。 例を単純にして、お使いの環境に不要なワークロードを持ち込まないようにするために、 ここでは、自動コミットを使用不可にして、長時間実行される照会をシミュレートしま す。 1. DB2のコマンド・ウィンドウを2つ開き、両方のウィンドウからperfdbに接続しま す。 2. 1つ目のウィンドウで、自動コミットを使用不可にします。 a. db2 update command options using c off 3. 自動コミットがオフになっていることを確認します。 a. db2 list command options b. 自動コミットがオフになっていることを確認 4. 1つ目のウィンドウから、次の照会を実行します。 a. db2 "insert into rbahra.table1 values (1,1,1)" 5. 2つ目のウィンドウから、次の照会を実行します。 a. db2 "select * from rbahra.table1" 6. スナップショットを取得します。 a. db2 get snapshot for applications on perfdb 7. スナップショットを表示します。 8. 両方のウィンドウでコミットを実行します。 a. db2 commit 9. 自動コミットを使用可能にします。 "Application Status"フィールドを見つけます。一方のアプリケーションが"UOW waiting"、 もう一方が"Lock Wait"になっています。上の例の場合は、SELECTを実行しているアプリ ケーションがロックを要求していますが、そのロックは、insertを実行しているアプリ ケーションによって保持されています。この例では、コミットするまでロックを解放しな いようにアプリケーションを強制しましたが、長時間経つとロックが解放される場合で も、原則は同じです。この単純な例は、スナップショットを使って"Lock Wait"状態のア プリケーションを見つける方法を示しています。この後は、アプリケーションが待ってい るロックの特定と、ロックが解放されない理由の解明に焦点を絞ることになります。その 際に焦点となる領域は、アプリケーションのコミットの頻度、使用されているロッキング のセマンティクス(分離レベル)、アプリケーションの作業単位のサイズなどです(ただし、 この単純化した例では、何度も述べているように、実際は強制的に問題を発生させていま す)。 デッドロックの追跡 スナップショットは、発生したデッドロックの数に対する貴重な洞察を与えてくれます。 この数が異常に多い場合は、イベント・モニターを使ってデッドロック・イベントをより 詳しく追跡することを検討する必要があります。以下のイベント・モニターの出力から は、2つのアプリケーションがデッドロックになっていることがわかります。 499) Deadlocked Connection ... Appl Id: *LOCAL.db2inst1.020921174012 Appl Seq number: 0001 Appl Id of connection holding the lock: *LOCAL.db2inst1.020921174009 Seq. no. of connection holding the lock: 0001 Lock wait start time: 09-21-2002 17:36:28.722587 Requesting lock as part of escalation: FALSE Deadlock detection time: 09-21-2002 17:37:43.379432 Table of lock waited on : SALES Schema of lock waited on : TRACKINGDB Tablespace of lock waited on : TRACKSPACE Type of lock: Row Mode of lock: X Mode application requested on lock: X Node lock occured on: 0 Lock object name: 1298696 Application Handle: 7 500) Deadlocked Connection ... Appl Id: *LOCAL.db2inst1.020921174009 Appl Seq number: 0001 Appl Id of connection holding the lock: *LOCAL.db2inst1.020921174012 Seq. no. of connection holding the lock: 0001 Lock wait start time: 09-21-2002 17:37:12.756160 Requesting lock as part of escalation: FALSE Deadlock detection time: 09-21-2002 17:37:43.379432 Table of lock waited on : INVENTORY Schema of lock waited on : TRACKINGDB Tablespace of lock waited on : INVNTSPACE Type of lock: Row Mode of lock: X Mode application requested on lock: X Node lock occured on: 0 Lock object name: 1298695 Application Handle: 187 上の出力からわかるように、両方のアプリケーションが排他ロックを要求および保持して います。この例は、デッドロック・イベントとそこに含まれているアプリケーションの両 方を特定する方法を示しています。この問題は、アプリケーションのロック・モード( データをロックする度合い)を調整することによって修正できる可能性があります。この 度合いのことを、分離レベルと呼びます。さまざまな分離レベルと、それらが並行性に与 える影響については、『DB2 Administration Guide』を参照してください。 セクション6:ソート 背景 照会では、しばしばソート操作が必要になります。たとえば、次のような場合がそうで す。 1. ソート順を満たす索引がない場合 2. 索引は存在するが、ソートの方が効率的と判定された場合 3. 索引が作成中の場合(構成パラメーターindexsortがyesに設定されていることを前提 とする) パフォーマンスの改善を目的とする場合、ソートでまず焦点となる領域は、パイプ・ソー トと非パイプ・ソートの区別です。ソート済みのデータのリストを一時表に格納しなくて もソートを実行できる場合、そのソートはパイプ・ソートと呼ばれます。非パイプ・ソー トは、結果を返すのに一時表を必要とします。パイプ・ソートは、処理および入出力の オーバーヘッドが少ないため、常に非パイプ・ソートよりパフォーマンスが高くなりま す。 ソートに影響を与えるパラメーターは以下の2つです。 ● ● ソート・ヒープ・サイズ(sortheap) - 各ソートに使用されるメモリーの量。 ソート・ヒープのしきい値(sortheapthres) - ソートに使用できるメモリーの総量。専 用ソートに使用されるメモリーの総量がこの制限を超えた場合は、その後のソート に割り当てられるメモリーの量が少なくなります。共有ソートに使用されるメモ リーの総量がこの制限を超えた場合、共有ソートはそれ以降実行できません。 データベース構成パラメーターの詳細については、『DB2 Administration Guide』を参照し てください。 最良実例 まず、sortheapをソート・メモリーの平均サイズに設定します。このサイズは、スナップ ショット・モニターで、使用されたソート・ヒープ・メモリーの最大サイズを見つけるこ とによって確認できます。 sheapthresについては、sortheapの値に並行ソートの平均数をかけた値から始めます。並行 ソート数についても、スナップショット・モニターで確認できます。 ソート操作のカウント スナップショット・モニターやイベント・モニターの出力のTotal Sortsフィールドは、実 行されたソートの数を観察するのにとても便利です。このパラメーターは、データベース ・レベルとアプリケーション・レベルで追跡されるため、まず特定のアプリケーションが 多数のソートを実行していないかどうかを調べ、アプリケーションを特定できたら、今度 はそのアプリケーションに焦点を絞り、ほかより多くのソートを実行している照会を見つ けるというような、柔軟な調査が可能になります。以下に例を示します。 1. perftutに接続します。 2. モニターをリセットします。 a. db2 reset monitor all 3. スナップショットを取得します。 a. db2 get snapshot for applications on perftut > sortstep3.out 4. 次の照会を実行します。 a. db2 "select * from rbahra.sales" > out.txt 5. スナップショットを取得します。 a. db2 get snapshot for applications on perftut > sortstep5.out 6. 次の照会を実行します。 a. db2 "select * from rbahra.sales order by sales" > out.txt 7. スナップショットを取得します。 a. db2 get snapshot for applications on perftut > sortstep7.out 8. 次の照会を実行します。 a. db2 "select * from rbahra.sales order by sales" > out.txt 9. スナップショットを取得します。 a. db2 get snapshot for applications on perftut > sortstep9.out 10. 次の照会を実行します。 a. db2 "select * from rbahra.sales" > out.txt 11. スナップショットを取得します。 a. db2 get snapshot for applications on perftut > sortstep11.out 上の一連の照会は、データベースに対して照会を実行するアプリケーションを表していま す。スナップショットを使ってTotal Sortsの統計を追跡すると、ソートを使用している照 会と使用していない照会があることがわかります。Total Sortsフィールドが変化しない場 合、ソートは行われていません。変化している場合は、前の照会がソートを実行したこと がわかります。この例では、sortstep3.outが示すソートの総数はゼロになります。これ は、モニターのデータをリセットしたからです。Total Sortsの値は、sortstep5.outで は0、sortstep7.outでは1、sortstep9.outでは2、sortstep11.outでは2になります。ここから、ス テップ4で実行された照会はソートを実行しなかったこと、ステップ6と8の照会は1つの ソートを実行したこと、ステップ10の照会はソートを実行しなかったことがわかります。 この後は、ソートを取り除く方法を見つける作業に移ります(オプティマイザーのセク ションを参照)。 ソート・オーバーフロー ソート・オーバーフローは、Sort Overflowsフィールドで追跡できます。Sort Overflows フィールドは、アプリケーション・レベルとデータベース・レベルで利用できます。これ は、ソート総数の追跡を拡張したものにすぎません。ソート・オーバーフローの数が多く なると、照会の実行時間が大幅に増大します。 例を使って、オーバーフローしたソートの詳細をキャプチャーしてみます。 1. perftutに接続します。 2. モニターをリセットします。 a. db2 reset monitor all 3. 次の照会を実行します。 a. db2 "select * from rbahra.sales order by sales" > out.txt 4. アプリケーション・スナップショットを取得します。 a. b2 get snapshot for applications on perftut > sortsnap.out 5. 出力ファイルsortsnap.outを表示します。 Sort Overflowsフィールドに注目します。ゼロ以外の値になっているはずです。ここか ら、上の例の照会で実行されたソートが、ディスクにスピルしたことがわかりま す。Total Sort Timeフィールドからは、ソートにかかった時間がわかります。Total Sortsに 対するSort Overflowsの比率が非常に高い場合は、ソート・ヒープに焦点を絞って、ソー ト操作で利用できるメモリーを増やしてみる必要があります。 セクション7:入出力の問題の追跡と指標 入出力の問題の追跡と指標 ロッキング、バッファー・プールのヒット率、ソートの度合いなどの項目で特に問題が見 つからない場合は、入出力サブシステムの調査が必要な可能性があります。これに は、iostatが役に立ちます。以下はIOSTATの出力の例です。 tty: Disks: hdisk1 hdisk3 hdisk0 hdisk2 hdisk6 hdisk7 hdisk8 tin 0.4 tout 38.6 avg-cpu: % tm_act Kbps 0.1 1.2 0.1 1.3 1.7 10.6 0.0 0.0 43.5 27.9 1.3 0.2 5.7 39.4 33.9 0.2 % user 5.5 % sys 6.5 % idle 2.8 tps Kb_read Kb_wrtn 0.2 2029368 10049580 0.1 5349500 7472208 1.8 20896559 84776678 0.0 0 0 1122139024 1256198880 0.3 17206032 39197748 2314800744 2124324356 % iowait 85.1 上の出力のtm_act列から、8つのデバイスのうち2つが極度のビジー状態になっていること がわかります。%tm_actの値が35%より大きい場合は、入出力がボトルネックとなってい る可能性があります。このような状況が見られる場合は、表スペースを複数のデバイスに 分割することをお勧めします。実際に、このユーティリティーは簡単かつすばやく使用で きるため、パフォーマンスの問題の報告を受けたら、まずiostatの出力を収集することを 検討してください。 セクション8:アクセス・プランと照会オプティマイザー DB2照会オプティマイザーの概要 DB2には、非常に強力な照会最適化アルゴリズムが備わっています。このコスト・ベース のアルゴリズムは、データベースに対して照会を実行するための最もコストの低い方法を 特定しようとします。照会のための最適なアクセス・プランを探す際には、データベース 構成、データベースの物理的なレイアウト、表の関係、データ分布などの項目がすべて考 慮されます。 データベースに対して照会を実行する際に発生する主要なイベントには、以下のようなも のがあります。 ● ● ● 照会グラフ・モデルの生成 - 照会を分解して表、述部、オブジェクト間の関係など の項目に切り分けます。ユーザーが判読できるSQLから、より処理しやすいDB2内 部の構造への変換を開始することが目標です。 照会の書き直しフェーズ - 元の照会を、より「DB2フレンドリー」なレイアウトに 分解します。表の関係や冗長な述部などの項目が特定され、照会のパフォーマンス を改善するために利用されます。 照会の最適化 - 一連のテストを通じて、特定のアクセス操作を実行する際のコスト 効率を調べます。関係のある操作(表スキャンと索引スキャンなど)や、さまざまな 結合手法が検討され、コストを割り当てられて、よりコストの低い操作が選択され ます。 アクセス戦略 オプティマイザーによって最も効率的なアクセス・プランが生成される際には、以下のよ うなさまざまな項目が考慮されます。 ● ● ● ● ● ● ● ● ● 入出力装置の特性 SQLステートメント ソート・ヒープ カタログ統計 アプリケーションのワークロード 索引の定義 CPUの性能 バッファー・プールと、関連するメモリーのサイズ ロッキングと並行性の可能性 システムを効果的に設計およびチューニングすることが、アクセス・プランを改善し、照 会の実行時間を短縮するための鍵となります。 最適化レベル SQL照会がDB2によってコンパイルされるときには、多くの最適化手法が使用されます。 使用する最適化手法が増えると、コンパイル時間が長くなり、場合によっては、使用する システム・リソースも増えます。環境の性質によっては、照会の最適化に適用される手法 の数を制限する必要があります。これは、構成パラメーターDFT_QUERYOPTを使って調 整できます。最適化レベルは、以下の値に設定できます。 0 - 最小限の最適化を使用 1 - 以前のバージョンのDB2とほぼ同等の最適化と、以前のバージョンでは使用されてい なかったその他の手法を使用 2 - 単純化された結合手法を使用する以外は最適化レベル5の機能を使用 3 - DB2 for MVSと同様の、中程度の最適化を実行 5 - ヒューリスティック・ルールありで大量の最適化を使用(デフォルト設定) 7 - ヒューリスティック・ルールなしで大量の最適化を使用 9 - 利用可能なすべての手法を使用 アプリケーション内で実行されるステートメントが比較的単純であることがわかっている 場合(結合がほとんどない場合など)は、高速化のための最適化にあまり多くのリソースを 使用するのは避けた方がよいでしょう。というのも、使用した最適化レベルが低い場合と 高い場合とで、実行時の差がごくわずかになる可能性があるからです。一方、非常に複雑 な照会が実行される場合には、最適化レベルを高くすることが推奨されます。これによ り、よりコスト効率が高いと予想されるアクセス・パスが検討されるようになります。 統計情報の更新 DB2オプティマイザーは、システム・カタログに格納されているデータベースの統計情報 に基づいて判断を下します。このため、統計情報がデータベースに対して常に最新の状態 になるようにすることをお勧めします。 DB2ユーティリティーのRUNSTATSを使用すると、表の統計情報を更新できます。この ユーティリティーのコマンド行構文については、『DB2 Command Reference』を参照して ください。 アクセス・プランの表示 オプティマイザーが選択したプランを表示することができます。最初に、次のようにデー タベースのEXPLAIN表を作成する必要があります。 db2 connect to perfpd db2 -tvf $HOME/sqllib/misc/EXPLAIN.DDL これにより、EXPLAIN表が作成されます。アクセス・プランのキャプチャーが要求され ると、オプティマイザーがこの表にデータを追加します。db2exfmtツールを使用すると、 この情報を抽出し、オプティマイザーが使用するアクセス・プランのコピーを生成できま す。ここで、次の<query name>照会のアクセス・プランを生成してみます。 db2 connect to perfpd db2 set current explain mode explain db2 "select * from rbahra.sales" db2 set current explain mode no db2exfmt -d perfpd -g TIC -w -1 -s % -n % -o db2exfmt.out db2exfmt.outの内容を表示すると、select照会を実行したときにDB2が使用したアクセス・ プランのコピーを見ることができます。次に、各セクションが何を表しているのかを見て みます。 アクセス・プランの構造 db2exfmtの出力には、データベースと照会に関するたくさんの情報が示されています。最 初のセクションには、構成の詳細情報が含まれています。 Database Context: ---------------Parallelism: CPU Speed: Comm Speed: Buffer Pool size: Sort Heap size: Database Heap size: Lock List size: Maximum Lock List: Average Applications: Locks Available: Intra-Partition Parallelism 1.054902e-06 0 210000 776 4096 1076 6 50 7295 このセクションは、照会が実行されたときに使用されていたバッファー・プールやソート ・ヒープなどの設定を把握するのに便利です。次の主要セクションには、パッケージの詳 細情報が含まれています。 Package Context: --------------SQL Type: Optimization Level: Blocking: Isolation Level: ---------------- STATEMENT 1 QUERYNO: QUERYTAG: Statement Type: Updatable: Deletable: Dynamic 5 Block All Cursors Cursor Stability SECTION 201 ---------------1 CLP Select No No Query Degree: 1 セクション番号や、照会が実行された場所(QUERYTAG)が示されています。これは、イ ベント・モニターの出力を取得して、イベント・モニターのセクションとアクセス・プラ ンのセクションを照らし合わせる際にとても便利です。これにより、モニタリング・ツー ルで確認した特定の照会およびそれに関連するイベントを、より細かく追跡することがで きます。 次のセクションには、元の照会とその最適化されたバージョンが示されています。 Original Statement: -----------------select count(distinct items) from perfdb.sales where items in (select id from perfdb.description) Optimized Statement: ------------------SELECT Q4.$C0 FROM (SELECT COUNT(Q3.$C0) FROM (SELECT DISTINCT Q2.ITEMS FROM PERFDB.DESCRIPTION AS Q1, PERFDB.SALES AS Q2 WHERE (Q2.ITEMS = Q1.ID)) AS Q3) AS Q4 書き直された照会からは、DB2が照会をどのように解釈したのか、オブジェクトの間にど のような関係を見つけることができたのか、などがわかります。次のセクションには、ア クセス・プランの断片が示されています。 RETURN ( 1) Cost I/O ￨ 1 GRPBY ( 2) 449.438 14.2765 ￨ 15 LMTQ ( 3) <- groupby to handle the distinct <- table queue due to SMP requirements 449.434 14.2765 ￨ 383.332 MSJOIN ( 5) 448.972 14.2765 /---+---¥ <- Merge Scan Join for Q2.ITEMS=Q1.ID 残りの2つのセクションには、アクセス・プラン演算子の詳細情報と、照会で使用される オブジェクトの詳細情報が含まれています。 アクセス・プラン演算子 アクセス・プランをより深く理解するには、まず、以下の基本構成要素を理解する必要が あります。 Cardinality <plan OPERATOR> Cost I/O Cost カーディナリティーは、DB2が演算子の出力として見積もった行数を表します。コスト は、この操作とその前の操作のCPUコストを表し、入出力コストは、システムの入出力サ ブシステムに対する演算子のコストを表します。前に生成したプランを例に取ると、次の ようになります。 383.332 MSJOIN ( 5) 448.972 14.2765 /---+---¥ <- Cardinality <- Plan Operator <- Cost <- I/O Cost プラン演算子には、たとえば次のようなものがあります。 SORT - ソートが行われたことを示します。 NLJN - DB2によって使用される、ネストされたループ結合手法を表します。 TBSCAN - 述部の適用またはデータの検索のためにオプティマイザーが表ス キャンの実行を選択したことを示します。 ISCAN - 索引スキャン。表スキャンと同様ですが、索引に対して適用されま す。 カーディナリティーの追跡(正確さを判定する方法) アクセス・プランを調べていると、ほんの数行しか返されないと示されているのに気付く ことがよくあります。これらが実際にどの程度正確なのかを確認するには、カーディナリ ティーの見積もりを調べて評価する必要があります。以下に例を示します。 100 FETCH /-------------+-----------¥ 100 perfdb ISCAN Table1 ￨ 1000 Index: perfdb Index1 1000 上のプランの断片からは、FETCH操作で返されるのが100行であると予想されていること がわかります。100行より多い(あるいは少ない、原則は同じ)行が返されると思われる場 合は、「本当のカーディナリティー」を特定して、推定値と比較することができます。そ のためには、まず、ISCANで適用された述部を切り分ける必要があります。これらは、ア クセス・プランの演算子の詳細情報のセクションで見つけることができます(以下の例 は、重要な部分のみを示すために切り詰められています)。 3) IXSCAN: (Index Scan) Cumulative Total Cost: Cumulative CPU Cost: Cumulative I/O Cost: Cumulative Re-Total Cost: Cumulative Re-CPU Cost: Cumulative Re-I/O Cost: Cumulative First Row Cost: Estimated Bufferpool Buffers: Predicates: ---------2) Start Key Predicate Relational Operator: Subquery Input Required: Filter Factor: 30.9743 149356 1 4.15582 103895 0 30.9054 2 Equal (=) No 0.1 Predicate Text: -------------(Q2.column1 = 5) ここで適用されている述部を使って、カーディナリティーの見積もりの妥当性をテストす るためのSQLステートメントを作成できます。 db2 select count(*) from table1 where column1=5 上の照会の出力によって生成される値が推定値と大きく異なる場合は、統計情報が古く なっている可能性があります。その場合は、RUNSTATSを実行する必要があります。 次に、次の例を試してみてください。 db2 connect to perftut db2 set current explain mode explain db2 "select * from rbahra.org where deptnumb=20" db2 set current explain mode no db2exfmt -d perftut -1 -g TIC -o cardexf1.out 結果となるアクセス・プランには、カーディナリティーの見積もりが40のRETURNが含ま れているはずです。本当のカーディナリティーを特定するためのselectステートメントを 作成してみてください。ヒント: 演算子の詳細情報でTBSCAN 2の詳細情報を確認しま す。 作成したselectステートメントを実行すると、本当の行数は1であることがわかりま す。rbahra.orgの統計情報を更新し、もう一度テストを実行してください。今度は正しい カーディナリティーが表示されるはずです。 ディスクにスピルしたソートの追跡 照会のボトルネックを切り分けるもう1つの便利な手法として、ソートがスピルしたかど うかを判別する手法があります。これは、カーディナリティーを検証するための手法を拡 張したものです。まず、演算子のコストの変化を調べます。以下に例を示します。 演算子SORT(16)から演算子TBSCAN(15)の間に、コストが6.14e6から6.87e6になっている のがわかります。ソートの直後に続く操作に対するこのコストの急激な増加は、ソートが ディスクにスピルして、オーバーフロー行を処理するために一時表が作成されたことを示 しています。スピルすると予想されたページ数の見積もりを調べるには、プランの演算子 の詳細情報のセクションに移動します。 見積もられたバッファー・プール・バッファーから、163976ページがスピルすると予想さ れたことがわかります。これを修正するには、sortheapの値を増やすか索引を定義するこ とが考えられます。 次の例を試してみてください。 db2 connect to perftut db2 set current explain mode explain db2 "select * from rbahra.sales order by sales" db2exfmt -d perftut -1 -g TIC -o sortexf1.out 演算子SORT(3)とTBSCAN(2)に集中します。 124272 TBSCAN ( 2) 46558.2 4067 ￨ 124272 SORT ( 3) 40710.4 2670 上のTBSCAN(2)でコストが増大しています。演算子の詳細情報を確認すると、次のよう になっています。 2) TBSCAN: (Table Scan) Cumulative Total Cost: Cumulative CPU Cost: Cumulative I/O Cost: Cumulative Re-Total Cost: Cumulative Re-CPU Cost: Cumulative Re-I/O Cost: Cumulative First Row Cost: Estimated Bufferpool Buffers: 46558.2 8.30099e+08 4067 5890.99 2.98685e+08 1397 41142.4 1397 ここから、このソートがディスクにスピルすると予想されていることがわかります。これ を修正するには、SORTKEYの索引を定義するか、SORTHEAPを増やします。 SORTKEYは、SORTの演算子の詳細情報で確認できます。 SORTKEY : (Sort Key column) 1: SALES(A) SORTHEAPを2500ページに増やして、アクセス・プランを生成してみてください。その 後、ソートの上の表スキャンでスピルが見られるかどうかを確認します。場合によって は、SORTHEAPを増やすことができない場合もあります。その場合は、SORTKEYに基づ く索引の定義を検討する必要があります。 セクション9:チューニング操作 概要 ここでは、パフォーマンスの問題をデバッグするための戦略や手法をいくつか簡単に紹介 します。 環境の制御 これは、どちらかというとデータベースの設計の問題になりますが、データベースの役割 の理解を確実に深めるために重要なステップです。データベースを設計する際には、予想 されるアプリケーションのワークロード、照会の特性(頻繁に行われる単純なselectや、頻 度は少ないが極度に複雑なSQL操作など)、システムの将来の要求などを常に頭に入れて おく必要があります。 予想されるデータ(データの量および種類の両方)を正確にモデル化することは、照会のア クセス・プランを予測する上で重要な要因となります。既に説明したように、DB2照会オ プティマイザーは、統計情報を使って決定を下します。予想される統計パターンに従った データ・モデルを生成することは、どのようなタイプのアクセス・プランが生成されるか についての洞察につながります。 正確なデータのモデル化は、実行時のパフォーマンスのモニターにも拡張することができ ます。また、使用される可能性のあるアプリケーションを「現実に即した」環境でテスト することも可能になります。これにより、既存の問題点を特定できるだけでなく、将来的 に予想される問題を明るみに出すことさえ可能になります。 インクリメンタルな変更 高度にチューニングされた環境でパフォーマンスの問題に取り組む際には、変更を段階的 に、妥当な範囲内で行うようにすることを忘れないようにしてください。たとえば、アプ リケーションの並行性に問題が見つかった場合、maxlocksの値とlocktimeoutの値を同時に 調整することは必ずしも妥当とはいえません。まず1つのパラメーターのみを調整し、変 更による効果を測定して、それが有効かどうかを判定します。一度にたくさんのパラメー ターを変更しても、すばらしい結果になる場合もあれば、好ましくない結果になる場合も あります。どちらの場合も、どのパラメーターによって違いが現れたのかがわからなく なってしまいます。 データベースの追跡 記録を残しておくことが大切です。スナップショット、イベント・モニター、アクセス・ プランの古い出力を利用できるようにしておいてください。そうすれば、パフォーマンス の問題が発生した場合に、問題がなかったときの測定値を豊富に利用できます。問題が発 生したときにばらばらの断片をつなぎ合わせるより、並べて比較できた方がはるかに簡単 です。 セクション10:結論 まとめ ここでは、さまざまなトピックや手法を紹介しました。パフォーマンス問題のデバッグを 包括的に検討したものではありませんが、今後パフォーマンスの問題を調査するための準 備はできたはずです。これで、ユーザーの苦情の性質や動作の違いに関係なく、調査を始 めることができます。 詳細情報 ここで扱ったトピックの詳細については、『DB2 Administration Guide』と『System Monitoring Guide and Reference』を参照してください。