Azure Database for PostgreSQL フレキシブル サーバーのクエリ ストア

クエリ ストアは、時間の経過と同時にクエリのパフォーマンスを追跡する方法を提供する、Azure Database for PostgreSQLフレキシブル サーバーの機能です。 クエリ ストアを使用すると、実行時間が最も長く、リソースを集中的に消費するクエリをすばやく検索できるため、パフォーマンスの問題のトラブルシューティングが簡単になります。 クエリ ストアは、クエリおよびランタイム統計の履歴を自動的に取り込んで保持するため、後で確認できます。 時間別にデータがスライスされるため、一時的な使用パターンを確認できます。 すべてのユーザー、データベース、クエリのデータは、Azure Database for PostgreSQL フレキシブル サーバーの azure_sys という名前のデータベースに格納されます。

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

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

Important

パフォーマンスの問題が発生するため、Burstable 価格レベルでクエリ ストアを有効にしないでください。

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

  1. Azure ポータルにサインインし、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 つのストアで構成されます。

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

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

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

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

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

Azure Database for PostgreSQL フレキシブル サーバーは、クエリ ストア データを azure_sys データベースに格納します。 次のクエリは、クエリ ストアに記録されたクエリに関する情報を返します。

SELECT * FROM  query_store.qs_view;

そして、このクエリは、待機統計に関する情報を返します。

SELECT * FROM  query_store.pgms_wait_sampling_view;

待機クエリの検索

待機イベントの種類では、類似性に基づいてさまざまな待機イベントがバケットにグループ化されます。 クエリ ストアは、待機イベントの種類、特定の待機イベント名、および対象のクエリを提供します。 この待機情報をクエリ ランタイム統計と関連付けると、クエリ のパフォーマンス特性の影響をより深く理解できます。

クエリ ストアの待機統計を使用して、ワークロードに関するより多くの分析情報を得る方法の例を次に示します。

観測 アクション
ロック待機が長い 影響を受けているクエリのクエリ テキストを確認し、ターゲット エンティティを識別します。 クエリ ストア頻繁に実行され、期間が長く、同じエンティティを変更している他のクエリを探します。 これらのクエリを特定した後で、コンカレンシーを向上させるためにアプリケーション ロジックを変更するか、より制限の低い分離レベルを使用します。
バッファー IO 待機が長い クエリ ストア内で物理読み取り回数が多いクエリを検索します。 IO 待機が多いクエリと一致する場合は、 自律チューニング 機能を有効にして、クエリの物理読み取りの数を減らす可能性のあるインデックスを作成することをお勧めできるかどうかを確認することを検討してください。
メモリ待機が多い クエリ ストア内で、メモリを最も消費しているクエリを探します。 これらのクエリは、影響を受けるクエリの進行状況をさらに遅らせる可能性があります。

構成オプション

クエリ ストアを有効にすると、集計ウィンドウにデータが保存されます。 これらのウィンドウの長さは 、pg_qs.interval_length_minutes パラメーターによって決まります。既定値は 15 分です。 各ウィンドウについて、クエリ ストアには最大 500 個の個別のクエリが格納されます。 各クエリの一意性を区別する属性は、 user_id (クエリを実行するユーザーの識別子)、 db_id (クエリが実行されるコンテキスト内のデータベースの識別子)、および query_id (実行されるクエリを一意に識別する整数値) です。 構成された間隔で個別のクエリの数が 500 に達した場合、クエリ ストアは記録されたクエリの 5% 割り当てを解除して、さらに余裕を持たされます。 最初に割り当て解除されたクエリは、実行回数が最も少なかったクエリです。

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

Parameter 説明 デフォルト 範囲
pg_qs.interval_length_minutes クエリ ストアの取り込みサイクル間隔 (分単位)。 データ永続化の頻度を定義します。 15 1 - 30
pg_qs.max_captured_queries 各キャプチャ間隔中に記録されたすべてのクエリからクエリ ストアに保持されるクエリの最大数。 500 100 - 500
pg_qs.max_plan_size クエリ プラン テキストからクエリ ストアに保存される最大バイト数。 長いプランは切り捨てられます。 7500 100 - 10000
pg_qs.max_query_text_length クエリ ストアで保存できる最大クエリ長。 長いクエリは切り捨てられます。 6000 100 - 10000
pg_qs.parameters_capture_mode クエリの位置パラメーターを取り込むかどうかとそのタイミング。 capture_parameterless_only capture_parameterless_only、capture_first_sample
pg_qs.query_capture_mode 追跡するステートメント。 none none、 top、 all
pg_qs.retention_period_in_days クエリ ストアの保持期間 (日数)。 古いデータは自動的に削除されます。 7 1 - 30
pg_qs.store_query_plans クエリ ストアでクエリ プランを保存するかどうかを指定します。 off on、off
pg_qs.track_utility クエリ ストアでユーティリティ コマンドを追跡する必要があるかどうかを指定します。 on on、off

注

pg_qs.max_query_text_length パラメーターの値を変更した場合、変更を行う前にクエリがキャプチャしたすべてのクエリのテキストは、引き続き同じquery_idとsql_query_textを使用します。 この動作は、新しい値が有効にならないという印象を与える可能性がありますが、クエリ ストアが以前に記録しなかったクエリでは、クエリ テキストで新しく構成された最大長が使用されていることがわかります。 この動作は仕様であり、 ビューと関数で説明されています。 query_store.qs_reset を実行すると、クエリ ストアがこれまで記録していたすべての情報 (クエリ ID ごとにキャプチャされたテキストを含む) が削除されます。 これらのクエリのいずれかが再度実行されると、新しく構成された最大長がキャプチャされるテキストに適用されます。

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

Parameter 説明 デフォルト 範囲
pgms_wait_sampling.history_period 待機イベントをサンプリングする頻度 (ミリ秒単位)。 100 1 - 600000
pgms_wait_sampling.query_capture_mode pgms_wait_sampling 拡張機能が追跡する必要があるステートメント。 none none、all

注

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 を使用します。

ビューと関数

クエリ ストアによって記録された情報に対してクエリを実行し、query_store データベースのazure_sys スキーマで使用できるビューと関数を使用して削除できます。 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";

前のクエリはすべて同じクエリ 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;

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

最後に、次のクエリは、前のバッチ内のクエリのクエリ 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) ごとに異なる行を返します。

名前 タイプ References 説明
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) 代表的なステートメントのテキスト。 同じ構造の異なるクエリが一緒にクラスター化されます。 このテキストは、クラスター内の最初のクエリのテキストです。 クエリ テキストの最大長の既定値は 6,000 であり、クエリ ストア パラメーター 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 倍精度 クエリの合計実行時間 (ミリ秒)。
min_time 倍精度 クエリの最小実行時間 (ミリ秒)。
max_time 倍精度 クエリの最大実行時間 (ミリ秒)。
mean_time 倍精度 クエリの平均実行時間 (ミリ秒)。
stddev_time 倍精度 クエリ実行時間の標準偏差 (ミリ秒)。
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 倍精度 ステートメントによってブロックの読み取りに費やされた時間の合計 (ミリ秒単位) (track_io_timing が有効になっている場合。それ以外の場合は 0)。
blk_write_time 倍精度 ステートメントによってブロックの書き込みに費やされた時間の合計 (ミリ秒単位) (track_io_timing が有効になっている場合。それ以外の場合は 0)。
is_system_query ブーリアン user_id = 10 (azuresu) のロールがクエリを実行したかどうかを判断します。 そのユーザーはスーパーユーザー特権を持ち、コントロール プレーン操作の実行に使用されます。 このサービスはマネージド PaaS サービスなので、Microsoft だけがそのスーパーユーザー ロールの一部になります。
query_type SMS 送信 クエリによって表される操作の種類。 使用できる値は、unknown、select、update、insert、delete、merge、utility、nothing、undefined です。
search_path SMS 送信 クエリが取り込まれた時点で設定されていた search_path 値。
query_parameters SMS 送信 パラメーター化されたクエリの位置指定パラメーターに渡される値を持つ JSON オブジェクトのテキスト表現。 この列は、2 つのケースでのみ値が設定されます: 1) パラメーター化されていないクエリの場合。 2) パラメーター化されたクエリの場合、pg_qs.parameters_capture_mode が capture_first_sample に設定され、かつクエリ ストアが実行時にクエリのパラメーターの値をフェッチできる場合。
parameters_capture_status SMS 送信 クエリによって表される操作の種類。 使用可能な値は succeeded (クエリがパラメーター化されなかったか、パラメーター化されたクエリであり、値が正常にキャプチャされました)、 disabled (クエリはパラメーター化されましたが、 pg_qs.parameters_capture_mode が capture_parameterless_only に設定されたため、パラメーターはキャプチャされませんでした)、 too_long_to_capture (クエリはパラメーター化されましたが、このビューの query_parameters 列に表示される結果の JSON の長さがクエリ ストアが永続化されるまでに過度に長いと見なされたため、パラメーターはキャプチャされませんでした)。 too_many_to_capture (クエリはパラメーター化されましたが、パラメーターの合計数が保持されるため、パラメーターはキャプチャされませんでした)、 serialization_failed (クエリはパラメーター化されましたが、パラメーターとして渡された値の少なくとも 1 つをテキストにシリアル化できませんでした)。

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) ごとに異なる行を返します。

名前 タイプ References 説明
start_time timestamp クエリは時間枠別に集計されます。 パラメーター pg_qs.interval_length_minutes は、これらのウィンドウの期間を定義します (既定値は 15 分)。 この列は、このエントリが記録された時間枠の開始時刻に対応しています。
end_time timestamp このエントリの時間枠に対応する終了時刻。
user_id oid pg_authid.oid ステートメントを実行したユーザーのオブジェクト識別子。
db_id oid pg_database.oid ステートメントが実行されたデータベースのオブジェクト識別子。
query_id bigint ステートメントの解析ツリーから計算される内部ハッシュ コード。
event_type SMS 送信 バックエンドによって待機されているイベントの種類。
event SMS 送信 バックエンドが現在待機中の場合、待機イベントの名前。
calls 整数 同じイベントが取り込まれた回数。

注

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

query_store.query_plans_view

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

名前 タイプ References 説明
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 によって生成されたものと同じ出力。

Functions

query_store.qs_reset

この関数は、クエリ ストアが収集するすべての統計を破棄します。 既にディスク上のテーブルに永続化されている、閉じた時間枠の統計が破棄されます。 また、メモリ内にのみ存在する現在の時間枠の統計も破棄されます。 この関数を実行できるのは、サーバー管理者ロール (azure_pg_admin) のメンバーだけです。

query_store.staging_data_reset

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

読み取り専用モード

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

読み取りレプリカがあるサーバーでクエリ ストアを有効にしても、 どの読み取りレプリカ でもクエリ ストアは自動的に有効になりません。 いずれかの読み取りレプリカで有効にした場合でも、クエリ ストアは読み取りレプリカで実行されたクエリを記録しません。 読み取りレプリカは、プライマリに昇格されるまでは読み取り専用モードで動作します。

関連コンテンツ

  • Last updated on