SQLプロファイリングと分析

ほとんどのRDBMS専門家は、非効率的なSQLコードがほとんどのデータベースパフォーマンスの問題の主な原因であることに同意しています。 DBAと開発者にとっての課題は、大規模で複雑なシステムで実行が不十分なSQLコードを見つけ、そのコードを最適化してパフォーマンスを向上させることです。

SQLプロファイラコンポーネントを使用すると、データベースのスーパーユーザーは実行速度の悪いSQLコードを見つけて最適化できます。 Microsoft SQL ServerのProfilerのユーザーは、PEMのSQL Profilerの操作と機能が非常に似ていることに気付くでしょう。 SQLプロファイラーは、各Advanced Serverインスタンスと共にインストールされます。 PostgreSQLを使用している場合、SQLプロファイラーインストーラーをダウンロードし、プロファイリングする各管理対象データベースインスタンスにSQLプロファイラー製品をインストールする必要があります。

SQL Profilerによって監視される各データベースについて、以下を行う必要があります。

  1. postgresql.conf ファイルを編集します; shared_preload_libraries構成パラメーターにSQLプロファイラーライブラリを含める必要があります。

    Linuxインストールの場合、パラメーター値には以下を含める必要があります。

    $libdir/sql-profiler

    Windowsでは、パラメーター値には次のものが含まれます。

    $libdir/sql-profiler.dll

  2. データベースでSQL Profilerが使用する関数を作成します。 SQL Profilerインストールプログラムは、LinuxシステムのメインPostgreSQLインストールディレクトリの share/postgresql/contrib サブディレクトリにSQLスクリプト（sql-profiler.sqlという名前）を配置します。 Windowsシステムでは、このスクリプトは share サブディレクトリにあります。サーバーをPEMに登録するときに指定したメンテナンスデータベースでこのスクリプトを呼び出す必要があります。

  3. 変更を有効にするために、サーバーを停止して再起動します。

注:SQLプロファイラーを構成する前にPEMクライアントでPEMサーバーに接続している場合は、サーバーとの接続を切断して再接続し、SQLプロファイラー機能を有効にする必要があります。 SQL Profilerプラグインのインストールと構成の詳細については、次のEnterpriseDB Webサイトから入手できるPEMインストールガイドを参照してください。

http://enterprisedb.com/products-services-training/products/documentation

新しいSQLトレースの作成

SQL Profilerは、分析のために特定のSQLワークロードをキャプチャし、SQLトレースで表示します。キャプチャしたSQLトレースをすぐに開始してレビューすることも、キャプチャしたトレースを保存して後でレビューすることもできます。 SQLプロファイラを使用して、最大15個の名前付きトレースを作成および保存できます。メニューオプションを使用して、トレースを作成および管理します。

トレースを作成する

Create trace... ダイアログを使用して、SQLプロファイラーがインストールおよび設定されているデータベースのSQLトレースを定義できます。インストールおよび構成されています。ダイアログにアクセスするには、PEMクライアントツリーコントロールでデータベースの名前を強調表示します。 [管理]メニューから[SQLプロファイラー]プルアッドメニューに移動し、[トレースの作成...]を選択します。

The Trace options tab

The Trace options tab

Trace options タブのフィールドを使用して、新しいトレースに関する詳細を指定します。

  • [名前]フィールドにトレースの名前を入力します。

  • User filter フィールドをクリックして、クエリがトレースに含まれるロールを指定します。必要に応じて、[すべて選択]の横のボックスをオンにして、すべてのロールからのクエリを含めます。

  • Database filter フィールドをクリックして、トレースするデータベースを指定します。必要に応じて、[すべて選択]の横のボックスをオンにして、すべてのデータベースに対するクエリを含めます。

  • trace size in the Maximum Trace File Size フィールドを指定します; SQLプロファイラーは、指定されたサイズにほぼ達するとトレースを終了します。

  • Run Now ボタンを選択したときにトレースを開始するには、 Run Now フィールドに Run Now を指定します。 [いいえ]を選択して、[スケジュール]タブのフィールドを有効にします。

The Create trace Schedule tab

The Create trace Schedule tab

Schedule タブのフィールドを使用して、新しいトレースのスケジュールの詳細を指定します。

  • Start time フィールドを使用して、トレースの開始時間を指定します。

  • トレースの終了時間を指定するには、 End time フィールドを使用します。

  • Repeat? フィールドにYesを指定すると、トレースが毎日指定された時間に繰り返されることを示します。 [定期ジョブオプション]タブのフィールドを有効にするには、[いいえ]を選択します。

The Create trace Periodic job options tab

The Create trace Periodic job options tab

Periodic job options タブのフィールドは、繰り返しトレースに関するスケジュールの詳細を指定します。 [日]セクションのフィールドを使用して、ジョブを実行する日を指定します。

  • Week days フィールドをクリックして、トレースを実行する曜日を選択します。

  • Month days フィールドをクリックして、トレースを実行する月の日を選択します。

  • Months フィールドをクリックして、トレースを実行する月を選択します。

Times セクションのフィールドを使用して、トレース実行のタイムスケジュールを指定します。

  • Hours フィールドをクリックして、トレースを実行する時間を選択します。

  • Minutes フィールドをクリックして、トレースを実行する時間を選択します。

Create trace... ダイアログを完了したら、 Create をクリックして、新しく定義されたトレースを開始するか、後でトレースをスケジュールします。

The SQL Profiler tab, displaying the trace results

トレース結果を表示するSQLプロファイラタブ

トレースをすぐに実行することを選択すると、トレース結果がPEMクライアントに表示されます。

既存のトレースを開く

前のトレースを表示するには、PEMクライアントツリーコントロールでプロファイルされたデータベースの名前を強調表示します。 Management メニューからSQLプロファイラーのプルアサイドメニューに移動し、 Open trace.... を選択します。 SQL Profiler toolbar メニューを使用してトレースを開くこともできます。 Open trace... オプションを選択します。 [トレースを開く...]ダイアログが開きます。

Opening an existing trace

既存のトレースを開く

トレースリストのエントリを強調表示し、[開く]をクリックして、選択したトレースを開きます。選択したトレースが[SQLプロファイラ]タブで開きます。

トレースのフィルタリング

フィルターは（1つ以上の）ルールの名前付きセットであり、それぞれがトレースビューからイベントを非表示にできます。トレースにフィルターを適用すると、非表示のイベントはトレースから削除されず、表示から除外されるだけです。

フィルターアイコンをクリックして Trace Filter ダイアログを開き、フィルターを定義するルール（またはルールセット）を作成します。各ルールは、イベントを呼び出したロールのID、またはイベント中に呼び出されたクエリタイプに基づいて、現在のトレース内のイベントを選別します。

既存のフィルターを開くには、 Open ボタンを選択します。新しいフィルターを定義するには、 Add (+) アイコンをクリックして、 Open タブに表示されているテーブルに行を追加し、ルールの詳細を指定します。

  • Type ドロップダウンリストボックスを使用して、フィルタールールを適用するトレースフィールドを指定します。

  • Condition ドロップダウンリストボックスを使用して、SQLプロファイラーが値をトレースのフィルター処理に適用する演算子の種類を指定します。

    • 指定した値を含む Matches to フィルターイベントを選択します。

    • 指定された値を含まないイベントをフィルタリングするには、 Does not match を選択します。

    • Is equal to フィールドで指定された文字列と完全に一致するイベントをフィルタリングするには、 Is equal to を選択します。

    • Is not equal を選択して、 Is not equal フィールドで指定された文字列と完全に一致しないイベントをフィルタリングします。

    • Starts with フィールドで指定された文字列で始まるイベントをフィルタリングするには、 Starts with を選択します。

    • Does not start with フィールドで指定された文字列で始まらないイベントをフィルタリングするには、 Does not start with を選択します。

    • Less than フィールドで指定された数値より小さい数値を持つイベントをフィルタリングするには、 Less than を選択します。

    • Greater than フィールドで指定された数値より大きい数値を持つイベントをフィルタリングするには、 Greater than を選択します。

    • Less than or equal to フィールドで指定された数値以下の数値を持つイベントをフィルタリングするには、 Less than or equal to を選択します。

    • Greater than or equal to フィールドで指定された数値以上の数値を持つイベントをフィルタリングするには、 Greater than or equal to を選択します。

  • Value フィールドを使用して、SQLプロファイラーが検索する文字列、数値、または正規表現を指定します。

ルールの定義が終了したら、追加（+）アイコンをクリックして、フィルターに別のルールを追加します。フィルターからルールを削除するには、ルールを強調表示して[削除]アイコンをクリックします。

Save ボタンをクリックして、フィルターを適用せずにフィルター定義をファイルに保存します;フィルターを適用するには、 OK をクリックします。 Cancel を選択してダイアログを終了し、フィルターへの変更を破棄します。

トレースを削除する

トレースを削除するには、PEMクライアントツリーコントロールでプロファイルされたデータベースの名前を強調表示します。 Management メニューからSQLプロファイラーのプルアサイドメニューに移動し、 Delete trace(s).... を選択します。SQLプロファイラーのツールバーメニューを使用してトレースを削除することもできます。 Delete trace(s)... オプションを選択します。 Delete traces ダイアログが開きます。

The Delete traces… dialog

The Delete traces… dialog

トレース名の左側にあるアイコンをクリックして、1つ以上のトレースを削除対象としてマークし、 Delete をクリックします。 PEMクライアントは、選択したトレースが削除されたことを確認します。

スケジュールされたトレースの表示

スケジュールされたトレースのリストを表示するには、PEMクライアントツリーコントロールでプロファイルされたデータベースの名前を強調表示します。 Management メニューからSQLプロファイラのプルアサイドメニューに移動し、 Scheduled traces... を選択します。SQLプロファイラのツールバーメニューを使用してリストに移動することもできます。 Scheduled traces... オプションを選択します。

Reviewing scheduled traces

スケジュールされたトレースの確認

Scheduled traces... ダイアログには、実行を待機しているトレースのリストが表示されます。トレース名の左側にある編集ボタンをクリックして、トレースに関する詳細情報にアクセスします。

  • Status フィールドは、現在のトレースのステータスをリストします。

  • トレースが有効な場合、 Enabled? スイッチは Enabled? を表示します。無効になっている場合はいいえ。

  • Name フィールドには、トレースの名前が表示されます。

  • Agent フィールドには、トレースの実行を担当するエージェントの名前が表示されます。

  • Last run フィールドには、トレースの最後の実行の日付と時刻が表示されます。

  • Next run フィールドには、次にスケジュールされたトレースの日付と時刻が表示されます。

  • Created フィールドには、トレースが定義された日時が表示されます。

インデックスアドバイザーの使用

Index AdvisorはAdvanced Server 9.0以降で配布されます。インデックスアドバイザーは、SQLプロファイラーと連携して、収集されたSQLステートメントを調べ、基になるテーブルのインデックス作成の推奨事項を作成して、SQL応答時間を改善します。インデックスアドバイザは、スーパーユーザーによって呼び出されるすべてのDML（INSERT、UPDATE、DELETE）およびSELECTステートメントで機能します。

インデックスアドバイザーからの診断出力には以下が含まれます。

  • 推奨されるインデックスから予測されるパフォーマンスの利点

  • 推奨されるインデックスの予測サイズ

  • 推奨インデックスの作成に使用できるDDLステートメント

インデックスアドバイザーを使用する前に、以下を行う必要があります。

  1. index_advisorライブラリをshared_preload_librariesパラメーターに追加して、各Advanced Serverホストの postgresql.conf ファイルを変更します。

  2. Index Advisor contrib モジュールをインストールします。モジュールをインストールするには、psqlクライアントまたはPEMクエリツールを使用してデータベースに接続し、次のコマンドを呼び出します。

    /i <complete_path>/share/contrib/index_advisor.sql

  3. サーバーを再起動して、変更を有効にします。

インデックスアドバイザは、SQLプロファイラによってキャプチャされたトレースデータに基づいてインデックスの推奨事項を作成できます。 SQL Profiler Trace Data ペインで1つ以上のクエリを強調表示し、 Index Advisor ツールバーボタンをクリックします（または[表示]メニューから[インデックスアドバイザー]を選択します）。 Index Advisorの詳細な使用情報については、EDB Postgres Advanced Server Guideをご覧ください。

注:インデックスアドバイザーは、非スーパーユーザーによって呼び出されたステートメントを分析できません。非スーパーユーザーによって呼び出されたステートメントを分析しようとすると、サーバーログに次のエラーが含まれます。

ERROR:access to library /"index_advisor/" is not allowed

インデックスアドバイザーの構成と使用の詳細については、次のEnterpriseDBから入手可能なEDB Postgres Advanced Server Guideを参照してください。

https://www.enterprisedb.com/resources/product-documentation