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

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

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

SQLプロファイラによって監視される各データベースについて、次のことを行う必要があります。

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

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

    $libdir/sql-profiler

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

    $libdir/sql-profiler.dll

  2. データベースでSQLプロファイラーが使用する関数を作成します。 SQLプロファイラのインストールプログラムは、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プロファイラーは、特定のSQLワークロードをキャプチャして表示し、SQLトレースで分析します。キャプチャしたSQLトレースをすぐに開始してレビューすることも、キャプチャしたトレースを保存して後でレビューすることもできます。 SQLプロファイラを使用して、最大15個の名前付きトレースを作成および保存できます。メニューオプションを使用して、トレースを作成および管理します。

トレースの作成

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

[トレースオプション]タブ

[トレースオプション]タブ

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

  • [名前]フィールドにトレースの名前を入力します。
  • [ User filterフィールドをクリックして、クエリをトレースに含めるロールを指定します。必要に応じて、[すべて選択]の横のボックスをオンにして、すべてのロールからのクエリを含めます。
  • [ Database filterフィールドをクリックして、トレースするデータベースを指定します。必要に応じて、[すべて選択]の横のボックスをオンにして、すべてのデータベースに対するクエリを含めます。
  • 指定trace size in the Maximum Trace File Sizeフィールド。 SQLプロファイラーは、指定されたサイズにほぼ達するとトレースを終了します。
  • 「 Run Nowフィールドで「はい」を指定して、「作成」ボタンを選択したときにトレースを開始します。 [いいえ]を選択して、[スケジュール]タブのフィールドを有効にします。
[トレーススケジュールの作成]タブ

[トレーススケジュールの作成]タブ

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

  • Start timeフィールドを使用して、トレースの開始時刻を指定します。
  • End timeフィールドを使用して、トレースの終了時間を指定します。
  • Repeat? Yesを指定しRepeat?指定された時間に毎日トレースを繰り返す必要があることを示すフィールド。 [定期ジョブオプション]タブのフィールドを有効にするには、[いいえ]を選択します。
トレースの定期的なジョブオプションタブの作成

トレースの定期的なジョブオプションタブの作成

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

  • クリックしますWeek daysトレースが実行されている曜日を選択するには、フィールド。
  • [ Month daysフィールドをクリックして、トレースを実行するMonth daysを選択します。
  • [ Monthsフィールドをクリックして、トレースを実行する月を選択します。

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

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

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

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

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

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

既存のトレースを開く

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

既存のトレースを開く

既存のトレースを開く

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

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

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

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

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

  • [ Typeドロップダウンリストボックスを使用して、フィルタールールを適用するトレースフィールドを指定します。
  • [ Conditionドロップダウンリストボックスを使用して、トレースをフィルタリングするときにSQLプロファイラが値に適用する演算子のタイプを指定します。
    • 指定した値を含むイベントMatches toフィルタリングするには、「 Matches to選択します。
    • 指定した値を含まないイベントをフィルタリングするにDoes not match選択Does not match 。
    • 選択Is equal to値]フィールドで指定した文字列に正確に一致が含まれているイベントをフィルタリングします。
    • [次Is not equal選択して、[値]フィールドで指定された文字列と完全に一致しないイベントをフィルタリングします。
    • [ Starts with選択して、[値]フィールドで指定された文字列で始まるイベントをフィルタリングします。
    • 選択しDoes not start with Valueフィールドで指定された文字列で始まっていないイベントをフィルタリングします。
    • 選択Less than値]フィールドで指定した数よりも少ない数値を持つフィルタイベントへ。
    • 選択してGreater than値]フィールドで指定した数よりも大きい数値を持つイベントをフィルタリングします。
    • 選択してLess than or equal to値]フィールドで指定した数以下の数値を持つイベントをフィルタリングします。
    • 選択Greater than or equal to Valueフィールドで指定した数以上の数値を持つイベントをフィルタリングします。
  • [ Valueフィールドを使用して、SQLプロファイラーが検索する文字列、数値、または正規表現を指定します。

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

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

トレースの削除

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

[トレースの削除…]ダイアログ

[トレースの削除…]ダイアログ

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

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

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

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

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

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

  • [ Statusフィールドには、現在のトレースのステータスが一覧表示されます。
  • Enabled?トレースが有効な場合、スイッチはYesを表示します。無効になっている場合はいいえ。
  • [ 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