クエリ ストアでパフォーマンスを監視する

  • [アーティクル]

適用対象: Azure Database for PostgreSQL - フレキシブル サーバー

Azure Database for PostgreSQL フレキシブル サーバーのクエリ ストア機能では、一定期間にわたってクエリ パフォーマンスを追跡する手段が提供されます。 クエリ ストアを使用すると、実行時間が最長のクエリおよびリソースを最も消費しているクエリを迅速に特定できるため、パフォーマンスのトラブルシューティングが簡単になります。 クエリ ストアでは、クエリおよびランタイム統計の履歴が自動的にキャプチャされて保持されるので、それらを確認できます。 時間別にデータがスライスされるため、一時的な使用パターンを確認できます。 すべてのユーザー、データベース、クエリに関するデータが Azure Database for PostgreSQL フレキシブル サーバー インスタンス内の azure_sys という名前のデータベースに保存されます。

重要

azure_sys データベースまたはそのスキーマを変更しないでください。 そうすると、クエリ ストアおよび関連するパフォーマンス機能が正しく機能しません。

クエリ ストアを有効にする

クエリ ストアは、追加料金なしですべてのリージョンで利用できます。 これはオプトイン機能であるため、サーバーでは既定では有効になっていません。 クエリ ストアは、特定のサーバー上のすべてのデータベースに対してグローバルに有効または無効にでき、データベースごとにオンまたはオフにすることはできません。

重要

パフォーマンスに影響を及ぼすため、Burstable 価格レベルでクエリ ストアを有効にしないでください。

Azure portal でクエリ ストアを有効にする

  1. Azure portal にサインインし、Azure Database for PostgreSQL フレキシブル サーバー インスタンスを選択します。
  2. メニューの [設定] セクションで、[サーバー パラメータ] を選択します。
  3. pg_qs.query_capture_mode パラメーターを検索します。
  4. 最上位レベルのクエリや入れ子になったクエリ (関数またはプロシージャ内で実行されるもの) を追跡するかどうかに応じて、値を TOP または ALL に設定し、[保存] を選択します。 azure_sys データベースに保持するデータの最初のバッチには、最大で 20 分ほどかかります。

クエリ ストア待機サンプリングを有効にする

  1. pgms_wait_sampling.query_capture_mode パラメーターを検索します。
  2. 値を ALL に設定して保存します。

クエリ ストア内の情報

クエリ ストアは、次の 2 つのストアで構成されます。

  1. クエリ実行の統計情報を保持するためのランタイム統計ストア。
  2. 待機統計情報を保持するための待機統計ストア。

クエリ ストアを使用するための一般的なシナリオは次のとおりです。

  • 指定された時間枠内にクエリが実行された回数を確認する。
  • 大きなデルタを確認するために時間枠間でクエリの平均実行回数を比較する。
  • 過去数時間に実行時間が最も長かったクエリを識別する。
  • リソースを待機している上位 N 件のクエリを特定する。
  • 特定のクエリの待機の性質を理解する。

領域の使用量を最小限に抑えるために、ランタイム統計ストア内のランタイム実行統計は、固定の構成可能な時間枠で集計されます。 これらのストア内の情報は、ビューを使用して参照できます。

クエリ ストア情報へのアクセス

クエリ ストアのデータは、Azure Database for PostgreSQL フレキシブル サーバー インスタンスの azure_sys データベースに保存されます。 次のクエリでは、クエリ ストア内のクエリに関する情報が返されます。

SELECT * FROM  query_store.qs_view;

また、次のクエリは待機統計に関するものです。

SELECT * FROM  query_store.pgms_wait_sampling_view;

待機クエリの検索

待機イベントの種類では、類似性によってさまざまな待機イベントがバケットに結合されます。 クエリ ストアでは、待機イベントの種類、特定の待機イベント名、対象のクエリが提供されます。 この待機情報をクエリのランタイム統計に関連付けられることは、クエリのパフォーマンス特性に何が寄与しているかをより深く理解できることを意味します。

クエリ ストア内の待機統計を使用してワークロードの詳細な分析情報を得る方法の例を次にいくつか示します。

観測 操作
ロック待機が長い 影響を受けているクエリのクエリ テキストを確認し、ターゲット エンティティを識別します。 同じエンティティを変更する他のクエリのクエリ ストアで、頻繁に実行されているクエリ、実行時間が長いクエリ、あるいはその両方を探します。 これらのクエリを特定した後で、コンカレンシーを向上させるためにアプリケーション ロジックを変更するか、より制限の低い分離レベルを使用します。
バッファー IO 待機が長い クエリ ストア内で物理読み取り回数が多いクエリを検索します。 それらと IO 待機が長いクエリが一致する場合は、スキャンではなくシークを実行するために、基になるエンティティへのインデックスの導入を検討します。 これにより、クエリの IO オーバーヘッドが最小限に抑えられます。 ポータル上でサーバーの [パフォーマンスの推奨事項] を調べて、このサーバーに対してクエリを最適化するインデックスの推奨事項があるかどうかを確認します。
メモリ待機が多い クエリ ストア内で、メモリを最も消費しているクエリを探します。 おそらくこれらのクエリによって、影響を受けているクエリの進行がさらに遅れています。 ポータル上でサーバーの [パフォーマンスの推奨事項] を調べて、これらのクエリを最適化するインデックスの推奨事項があるかどうかを確認します。

構成オプション

クエリ ストアを有効にすると、pg_qs.interval_length_minutes サーバー パラメータによって決定される長さの集計ウィンドウにデータが保存されます (既定値は 15 分)。 各ウィンドウに対して、ウィンドウごとに最大 500 個の個別の (個別の userid、dbid、および queryid を持つ) クエリが保存されます。 1 つの期間中での個別のクエリの数が 500 に達すると、その他のための空きを作るために使用率が低い 5% が割り当て解除されます。

次のオプションは、クエリ ストア パラメータを構成するために使用できます。

パラメーター 説明 [Default] Range
pg_qs.query_capture_mode 追跡対象のステートメントを設定します。 なし none、top、all
pg_qs.interval_length_minutes (*) pg_qs の query_store キャプチャ間隔を分単位で設定します。これはデータ永続化の頻度です。 15 1 - 30
pg_qs.store_query_plans pg_qs のクエリ プランの保存を有効または無効にします。 オフ on、off
pg_qs.max_plan_size pg_qs のクエリ プラン テキストとして保存される最大バイト数を設定します。これより長いプランは切り捨てられます。 7500 100 - 10k
pg_qs.max_query_text_length 保存できるクエリの最大長を設定します。長いクエリは切り詰められます。 6000 100 - 10K
pg_qs.retention_period_in_days pg_qs の保持期間ウィンドウを日数で設定します。この時間以降、データは削除されます。 7 1 - 30
pg_qs.track_utility ユーティリティ コマンドを pg_qs で追跡するかどうかを設定します。 on on、off

(*) その値の変更を有効にするにはサーバーを再起動する必要がある静的サーバー パラメータ。

特に待機統計には次のオプションが適用されます。

パラメーター 説明 [Default] Range
pgms_wait_sampling.query_capture_mode pgms_wait_sampling 拡張機能によって追跡されるステートメントを選択します。 なし none、all
Pgms_wait_sampling.history_period 待機イベントをサンプリングする頻度をミリ秒単位で設定します。 100 1-600000

Note

pg_qs.query_capture_mode は pgms_wait_sampling.query_capture_mode に優先します。 pg_qs.query_capture_mode が NONE の場合、pgms_wait_sampling.query_capture_mode の設定は効果がありません。

パラメーターの別の値を取得または設定するには、Azure portal を使用します。

ビューと関数

次のビューと関数を使用してクエリ ストアを表示および管理します。 PostgreSQL の public ロールに属するユーザーは、これらのビューを使用してクエリ ストア内のデータを表示できます。 これらのビューは、azure_sys データベース内でのみ使用できます。

クエリは、その構造を確認し、リテラル、定数、別名、大文字と小文字の違いなど、意味的に重要でないものをすべて無視することによって正規化されます。

2 つのクエリが意味的に同一のとき、同じ参照先の列とテーブルに対して異なる別名を使用する場合でも、同じ query_id で識別されます。 2 つのクエリで使用されるリテラル値のみが異なる場合は、同じ query_id でも識別されます。 同じ query_id で識別されたすべてのクエリで、sql_query_text は、クエリ ストアで記録アクティビティを開始してからか、関数 query_store.qs_reset が実行されたために永続化されたデータが最後に破棄されてから初めて実行されたクエリのものになります。

クエリの正規化のしくみ

この正規化のしくみを説明する例をいくつか次に示します。

次のステートメントでテーブルを作成するとします。

create table tableOne (columnOne int, columnTwo int);

クエリ ストアのデータ収集を有効にすると、1 人または複数のユーザーがこのとおりの順序で次のクエリを実行します。

select * from tableOne;
select columnOne, columnTwo from tableOne;
select columnOne as c1, columnTwo as c2 from tableOne as t1;
select columnOne as "column one", columnTwo as "column two" from tableOne as "table one";

前のすべてのクエリで同じ query_id を共有します。 また、クエリ ストアで保持されるテキストは、データ収集を有効にした後に実行された最初のクエリのものです。 したがって、select * from tableOne; になります。

次の一連のクエリは、正規化されると、前のクエリ セットと一致しません。WHERE 句によって意味的に異なるものになるためです。

select columnOne as c1, columnTwo as c2 from tableOne as t1 where columnOne = 1 and columnTwo = 1;
select * from tableOne where columnOne = -3 and columnTwo = -3;
select columnOne, columnTwo from tableOne where columnOne = '5' and columnTwo = '5';
select columnOne as "column one", columnTwo as "column two" from tableOne as "table one" where columnOne = 7 and columnTwo = 7;

ただし、この最後のセット内のすべてのクエリで同じ query_id を共有し、それらすべてを識別するために使用されるのは、バッチ select columnOne as c1, columnTwo as c2 from tableOne as t1 where columnOne = 1 and columnTwo = 1; の最初のクエリのテキストです。

最後に、前のバッチのものの query_id と一致しないクエリと、その理由を以下に示します。

クエリ:

select columnTwo as c2, columnOne as c1 from tableOne as t1 where columnOne = 1 and columnTwo = 1;

一致しない理由: 列の一覧では同じ 2 つの列 (columnOne と ColumnTwo) を参照しますが、参照される順序は逆になり、前のバッチの columnOne, ColumnTwo からこのクエリの ColumnTwo, columnOne になります。

クエリ:

select * from tableOne where columnTwo = 25 and columnOne = 25;

一致しない理由: WHERE 句で評価された式が参照される順序は逆になり、前のバッチの columnOne = ? and ColumnTwo = ? からこのクエリの ColumnTwo = ? and columnOne = ? になります。

クエリ:

select abs(columnOne), columnTwo from tableOne where columnOne = 12 and columnTwo = 21;

一致しない理由: 列リストの最初の式は columnOne ではなくなりますが、関数 abs は columnOne に対して評価され (abs(columnOne))、意味的に同等ではありません。

クエリ:

select columnOne as "column one", columnTwo as "column two" from tableOne as "table one" where columnOne = ceiling(16) and columnTwo = 16;

一致しない理由: WHERE 句の最初の式では、リテラルを使用して columnOne の等価性を評価しなくなりますが、関数 ceiling の結果はリテラルに対して評価され、意味的に同等ではありません。

ビュー

query_store.qs_view

このビューでは、クエリ ストアのサポート テーブルに既に保持されているすべてのデータを返します。 現在アクティブな時間枠のメモリ内に記録されているデータは、時間枠が終了するまで表示されず、メモリ内の揮発性データが収集され、ディスクに保存されているテーブルに保持されます。 このビューでは、個別のデータベース (db_id)、ユーザー (user_id)、クエリ (query_id) ごとに異なる行を返します。

名前 Type 参照 説明
runtime_stats_entry_id bigint runtime_stats_entries テーブルの ID。
user_id oid pg_authid.oid ステートメントを実行したユーザーの OID。
db_id oid pg_database.oid ステートメントが実行されたデータベースの OID。
query_id bigint ステートメントの解析ツリーから計算される内部ハッシュ コード。
query_sql_text varchar(10000) 代表的なステートメントのテキスト。 同じ構造を持つ複数の異なるクエリがまとめてクラスター化されます。このテキストは、クラスター内の最初のクエリのテキストです。 クエリ テキストの最大長の既定値は 6000 で、クエリ ストア パラメータ pg_qs.max_query_text_length を使用して変更できます。 クエリのテキストがこの最大値を超えると、最初の pg_qs.max_query_text_length 文字に切り詰められます。
plan_id bigint このクエリに対応するプランの ID。
start_time timestamp クエリは時間枠によって集計され、その期間はサーバー パラメータ pg_qs.interval_length_minutes によって定義されます (既定値は 15 分)。 これは、このエントリの時間枠に対応する開始時刻です。
end_time timestamp このエントリの時間枠に対応する終了時刻。
calls bigint この時間枠で実行されたクエリの実行回数。 並列クエリの場合、各実行の呼び出しの数は、クエリの実行を促進するバックエンド プロセスの 1 に加えて、実行ツリーの並列分岐の実行の共同作業を行うために起動された各バックエンド ワーカー プロセスの同数の他のユニットに対応することに注意してください。
total_time double precision クエリの合計実行時間 (ミリ秒)。
min_time double precision クエリの最小実行時間 (ミリ秒)。
max_time double precision クエリの最大実行時間 (ミリ秒)。
mean_time double precision クエリの平均実行時間 (ミリ秒)。
stddev_time double precision クエリ実行時間の標準偏差 (ミリ秒)。
rows bigint ステートメントによって取得または影響された行の合計数。 並列クエリの場合、各実行の行数は、クエリの実行を促進するバックエンド プロセスによってクライアントに返される行の数に加えて、実行ツリーの並列分岐の実行の共同作業を行うために起動された各バックエンド ワーカー プロセスが促進バックエンド プロセスに返すすべての行の合計に対応していることに注意してください。
shared_blks_hit bigint ステートメントによる共有ブロック キャッシュ ヒットの合計数。
shared_blks_read bigint ステートメントによって読み取られた共有ブロックの合計数。
shared_blks_dirtied bigint ステートメントによって使用された共有ブロックの合計数。
shared_blks_written bigint ステートメントによって書き込まれた共有ブロックの合計数。
local_blks_hit bigint ステートメントによるローカル ブロック キャッシュ ヒットの合計数。
local_blks_read bigint ステートメントによって読み取られたローカル ブロックの合計数。
local_blks_dirtied bigint ステートメントによって使用されたローカル ブロックの合計数。
local_blks_written bigint ステートメントによって書き込まれたローカル ブロックの合計数。
temp_blks_read bigint ステートメントによって読み取られた一時ブロックの合計数。
temp_blks_written bigint ステートメントによって書き込まれた一時ブロックの合計数。
blk_read_time double precision ステートメントによってブロックの読み取りに費やされた時間の合計 (ミリ秒単位) (track_io_timing が有効になっている場合。それ以外の場合は 0)。
blk_write_time double precision ステートメントによってブロックの書き込みに費やされた時間の合計 (ミリ秒単位) (track_io_timing が有効になっている場合。それ以外の場合は 0)。
is_system_query boolean スーパーユーザー特権を持ち、コントロール ウィンドウ操作の実行に使用される user_id = 10 (azuresu) を使用して、ロールによってクエリが実行されたかどうかを判断します。 このサービスはマネージド PaaS サービスなので、Microsoft だけがそのスーパーユーザー ロールの一部になります。
query_type text クエリによって表される操作の種類。 使用できる値は、unknown、select、update、insert、delete、merge、utility、nothing、undefined です。

query_store.query_texts_view

このビューでは、クエリ ストア内のクエリ テキスト データが返されます。 個別の query_sql_text ごとに 1 つの行があります。

名前 タイプ 説明
query_text_id bigint query_texts テーブルの ID
query_sql_text varchar(10000) 代表的なステートメントのテキスト。 同じ構造を持つ複数の異なるクエリがまとめてクラスター化されます。このテキストは、クラスター内の最初のクエリのテキストです。
query_type smallint クエリによって表される操作の種類。 PostgreSQL <= 14 のバージョンでは、使用可能な値は 0 (不明)、1 (選択)、2 (更新)、3 (挿入)、4 (削除)、5 (ユーティリティ)、6 (何もしない) です。 PostgreSQL >= 15 のバージョンでは、使用可能な値は 0 (不明)、1 (選択)、2 (更新)、3 (挿入)、4 (削除)、5 (マージ)、6 (ユーティリティ)、7 (何もしない) です。

query_store.pgms_wait_sampling_view

このビューでは、クエリ ストア内の待機イベント データが返されます。 このビューでは、個別のデータベース (db_id)、ユーザー (user_id)、クエリ (query_id)、イベント (event) ごとに異なる行を返します。

名前 Type 参照 説明
start_time timestamp クエリは時間枠によって集計され、その期間はサーバー パラメータ pg_qs.interval_length_minutes によって定義されます (既定値は 15 分)。 これは、このエントリの時間枠に対応する開始時刻です。
end_time timestamp このエントリの時間枠に対応する終了時刻。
user_id oid pg_authid.oid ステートメントを実行したユーザーの OID。
db_id oid pg_database.oid ステートメントが実行されたデータベースの OID。
query_id bigint ステートメントの解析ツリーから計算される内部ハッシュ コード。
event_type text バックエンドによって待機されているイベントの種類。
イベント text バックエンドによって現在待機されている場合に、待機イベントの名前。
calls integer 同じイベントがキャプチャされた回数。

Note

query_store.pgms_wait_sampling_view ビューの event_type および event 列で使用可能な値の一覧については、pg_stat_activity の公式ドキュメントを参照し、同じ名前の列を参照する情報を探します。

query_store.query_plans_view

このビューは、クエリの実行に使われたクエリ プランを返します。 データベース ID とクエリ ID ごとに 1 行があります。 ユーティリティ以外のクエリのクエリ プランのみが保存されます。

plan_id db_id query_id plan_text
plan_id bigint EXPLAIN によって生成された正規化済みクエリ プランのハッシュ値。 これは、プラン ノードの推定コストとバッファーの使用を除外するため、正規化済みと見なされます。
db_id oid pg_database.oid ステートメントが実行されたデータベースの OID。
query_id bigint ステートメントの解析ツリーから計算される内部ハッシュ コード。
plan_text varchar(10000) costs=false、buffers=false、format=text を指定したステートメントの実行プラン。 これは EXPLAIN で得られる出力と同じです。

関数

query_store.qs_reset

この関数では、クエリ ストアによってこれまでに収集されたすべての統計が破棄されます。 ディスク テーブルに保持されている既に終了した時間枠と、メモリ内にまだ保持されている現在の時間枠の両方についての統計が破棄されます。 この関数は、サーバー管理者ロール (azure_pg_admin) によってのみ実行できます。

query_store.staging_data_reset

この関数によって、クエリ ストアによってメモリ内に収集されたすべての統計 (つまり、クエリ ストアの収集されたデータの永続化をサポートするディスク テーブルにまだフラッシュされていないメモリ内のデータ) が破棄されます。 この関数は、サーバー管理者ロール (azure_pg_admin) によってのみ実行できます。

読み取り専用モード

Azure Database for PostgreSQL - フレキシブル サーバー インスタンスが読み取り専用モードである場合 (default_transaction_read_only パラメータが onに設定されている場合など)、または読み取り専用モードが、ストレージ容量に達したために自動的に有効にされた場合、クエリ ストアではデータをキャプチャしません。

関連するコンテンツ