観測・診断図鑑 · 基礎から応用へ

pg_stat_statementsでクエリ傾向を読む

正規化されたSQLの回数と実行時間を同じ観測区間で比較し、全体の負担と一件の遅延を切り分けます。

この記事をロゲイアに質問する確認問題3問に進むこれまでの誤答を復習する

更新日

この記事の目次
  1. 現在の一件ではなく繰り返す仕事を見る
  2. 収集できる条件と集計の単位
  3. 同じ区間で二つの読み取りを保存する
  4. 差分で優先順位を計算する
  5. リセットや追い出しで連続性が切れる
  6. 平均から見えない条件と長い裾
  7. 変更後も同じ業務指標で確かめる
  8. この記事をロゲイアに質問する理解を確かめる

現在の一件ではなく繰り返す仕事を見る

pg_stat_statementsは、SQLの呼び出し回数や実行時間を累積して調べるPostgreSQLの拡張です。現在動いている一件を表示するpg_stat_activityとは違い、似た処理が繰り返された結果を見ます。「最も遅いSQL」を探す前に、一件の応答を短くしたいのか、全体の計算やI/Oの負担を減らしたいのかを決めます。平均が短くても百万回呼ぶ処理は全体を圧迫し得ます。一方、回数の少ない極端に遅い処理は利用者の期限を破るかもしれません。合計、平均、回数のどれを並べるかで選ばれる候補は変わります。数値の順位と業務上の優先順位を同じものとして扱わないことが出発点です。

収集できる条件と集計の単位

利用にはshared_preload_librariesへの読み込み、クエリ識別の有効化、対象データベースでの拡張作成などの条件があります。既存の環境でビューがない場合は、データがゼロなのではなく導入状態を確認します。監視のための設定変更と再起動は、運用上の変更として別に計画します。行の識別にはqueryidだけでなく、DB、ユーザー、トップレベルの文かどうかなどの集計条件が関係します。似たSQLでも別のユーザーの負荷をまとめたい場合と、分けたい場合があります。表示する代表SQLは一件の実行記録そのものではなく、正規化された処理を読むための手掛かりです。必要な列や権限は稼働バージョンの資料で確認します。

同じ区間で二つの読み取りを保存する

調査区間の開始と終了で、同じ集計キーについてcalls、total_exec_timeなどを時刻付きで保存します。全履歴を消すリセットより、既存の統計を残した差分が他の調査と共存しやすい方法です。観測SQLでは対象DBとユーザーの範囲をそろえ、出力を保存した時点も記録します。query文字列に機密情報が含まれ得るため、共有する記録の範囲は必要最小限にします。SQLの実行時間だけでなく、その区間のアプリ要求数、失敗数、CPUやI/Oもそろえれば、要求増加による合計時間の増加と、一件ごとの劣化を区別しやすくなります。接続先がプライマリかレプリカかも記録し、別ノードの統計を混ぜません。

sql
SELECT dbid, userid, queryid, toplevel, calls, total_exec_time,
       mean_exec_time, query
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY total_exec_time DESC LIMIT 20;

差分で優先順位を計算する

説明用にAのcallsが1000から1600、total_exec_timeが5000msから9200msへ増えたとします。区間の600回が使った合計は4200msで、平均は7msです。開始前を含む累積平均とは分けて計算します。同じ区間でBが20回、合計3000msなら平均150msです。Aは合計負担、Bは一件の遅延という別の観点で候補になります。この数字は計算法を示す仮定で実測ではありません。同時に動く処理の時間を足すため、合計が観測区間の壁時計時間を超えても直ちに誤りとは言えません。また実行時間はCPU時間ではなく、待機を含み得ます。差分がゼロ回なら平均をゼロと作らず、その区間には平均を計算する実行がないと示します。

リセットや追い出しで連続性が切れる

カウンタは常に同じ履歴の続きとは限りません。リセット、再起動に伴う状態変化、保持上限によるエントリの追い出しなどを確認します。途中でなくなった行が再び現れた場合や差分が負になった場合は、通常の区間平均として採用せず、連続性が失われた記録として扱います。pg_stat_statements_infoや統計の時刻情報、対象バージョンが提供する列を合わせて調べます。計画時間の追跡が無効な場合にゼロが出ても、計画が無料だったという結論にはなりません。計画と実行を同じ費用とせず、どの追跡が有効かを記録します。共有統計のリセットを比較のたびに行うと、他の運用者が必要とする履歴も失う場合があります。

平均から見えない条件と長い裾

同じ正規化SQLでも、値が異なれば選択率や実行計画の振る舞いが異なる場合があります。特定の顧客だけ大量の行を返す現象は、集計平均へ埋もれます。平均と最大だけからp95や個別要求の時系列を復元することはできません。候補を選んだ後に、代表的な条件と問題条件を分け、実行計画やアプリのトレースへつなぎます。EXPLAIN ANALYZEは文を実行するため、更新文や重い問い合わせは適切な検証環境で扱います。queryidは永久に同じ意味を持つ業務IDではありません。バージョン変更をまたぐ比較では、SQLや定義も合わせて確認します。集計で見つけ、個別の証拠で原因を絞るという役割分担を保ちます。

変更後も同じ業務指標で確かめる

索引やSQLを変更した後は、同じ業務の回数、入力分布、同時実行、観測区間で比較します。合計時間だけ減っても、要求を拒否した数が増えたなら改善と判断できません。平均が良くても重要な要求の期限を超える割合が増えれば目的を外します。読み取りを速くする索引が書き込みのWALや更新費用を増やす場合もあるため、対象以外の経路を確認します。最終記録には観測範囲、連続性の確認、選んだ候補、実行計画などの追加証拠、変更後の業務指標を残します。pg_stat_statementsは調査の入口であり、集計の一行だけで全遅延の機構や改善の因果まで証明する道具ではありません。

読了と問題の結果を、言語ごとにこのブラウザへ保存します。

コースの順番に沿って学ぶ

サーバーの中で起きていること · STEP 12

学習コースから次を選ぶ