LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@arisiel00

PostgreSQL パフォーマンスモニタリング

Last updated at Posted at 2025-03-06

はじめに

PostgreSQLは、機能と拡張性の面で、多くの機能を提供する強力なオープンソースデータベースです。しかし、大規模アプリケーションを運営したり、性能問題を解決するには、効率的なモニタリングツールが必要です。pg_stat_monitorはこれらの要求を満たすために設計された拡張モジュールで、PostgreSQLの性能をより細かく分析し、管理できるようにします。

pg_stat_monitorとは

pg_stat_monitorはPostgreSQLのクエリ性能を実時間でモニタリングし、多様な統計データを提供するツールです。PostgreSQLの基本提供ビューpg_stat_statementsを拡張した形態で、より多くの機能やデータを収集できます。これにより、システム性能を最適化し、問題をより早く解決できます。

PG_STAT_MONITORの特徴

  • クエリ性能分析
    クエリの実行回数、実行時間、平均時間、エラー数など、詳細なクエリ性能データを収集します。

  • クエリのグループ化
    似たクエリを正規化してグループ化することで、統計の理解を簡略化します。たとえば、クエリのリテラル値をプレースホルダーに置き換えることで、同種のクエリをまとめます。

  • バケットベースの統計
    データ収集を時間ベースの「バケット」に分割し、リアルタイムのパフォーマンス分析を可能にします。この機能は、時間帯ごとの負荷の変化を視覚化する際に特に有用です。

  • 多様なフィルターとビュー
    ユーザー、データベース、アプリケーションなどの属性ごとにデータをフィルタリング可能。これにより、特定の領域やクエリに集中した分析ができます。

  • I/Oと待機時間のモニタリング
    クエリのI/O操作や待機イベントの詳細なデータを取得し、ボトルネックの診断に役立てます。

  • リソース消費の追跡
    CPU使用率、メモリ消費量、ディスクI/Oなどのリソース使用状況を追跡し、パフォーマンス最適化の基礎情報を提供します。

PG_STAT_MONITORの制約

  • PG_STAT_MONITORは、PostgreSQL 11以降のバージョンで使用できます
  • 大量の統計データを収集するため、ディスクスペースの消費が増加する可能性があります

pg_stat_monitorのInstall

pg_stat_monitor File Download
Extension Github URL : https://github.com/percona/pg_stat_monitor

#Postgresql15の場合

#File Download
git clone git://github.com/Percona/pg_stat_monitor.git

#Compile and install the extension
cd pg_stat_monitor
make USE_PGXS=1
make USE_PGXS=1 install

#Configuration
#pg_stat_monitor Extension適用
#postgresql.confを修正します。
#parameter shared_preload_libraries項目にpg_stat_monitor追加
...
shared_preload_libraries = 'pg_stat_monitor'
...

#Postgresql再起動
#このコマンドは各自環境に違いますので、自分の環境に合わせて再起動を行います。
systemctl restart postgresql


#Extension作成
postgres=# CREATE EXTENSION pg_stat_monitor ;
CREATE EXTENSION
postgres=# \dx
=# \dx
                                                                                                       List of installed extensions
       Name        x Version x   Schema   x                                                                                           Description
qqqqqqqqqqqqqqqqqqqqnqqqqqqqqqnqqqqqqqqqqqqnqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq
pg_stat_monitor    x 2.1     x public     x The pg_stat_monitor is a PostgreSQL Query Performance Monitoring tool, based on PostgreSQL contrib module pg_stat_statements. pg_stat_monitor provides aggregated statistics, client informatio …
                   x         x            x…n, plan details including plan, and histogram information.
pg_stat_statements x 1.10    x public     x track planning and execution statistics of all SQL statements executed
plpgsql            x 1.0     x pg_catalog x PL/pgSQL procedural language
(3 rows)

PG_STAT_MONITORの活用

  • クエリパフォーマンスの分析
    • bucket: 時間ベースのスライディングウィンドウを示し、特定の時間内のデータを集計します。
    • queryid: クエリの固有識別子で、同じクエリを識別するために使用されます。
    • total_exec_time: クエリの総実行時間で、パフォーマンスのボトルネックを特定するのに役立ちます。
SELECT
    bucket,
    queryid,
    substr(query, 1, 100) AS short_query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows
FROM
    pg_stat_monitor
ORDER BY
    total_exec_time DESC
LIMIT 10;
  • 頻繁に呼び出されるクエリの分析
    • calls: クエリの呼び出し回数で、頻繁に呼び出されるクエリを特定し、キャッシングやインデックス最適化を検討できます。
SELECT
    queryid,
    substr(query, 1, 100) AS short_query,
    calls,
    mean_exec_time
FROM
    pg_stat_monitor
ORDER BY
    calls DESC
LIMIT 10;
  • CPUおよびI/Oベースのパフォーマンス分析
    • sum(cpu_user_time + cpu_sys_time) AS total_cpu_time: sys & userで消費されたCPU時間を合計し、CPU使用量の多いクエリを特定します。
    • sum(blk_read_time + blk_write_time) AS total_io_time: ブロックの読み取りおよび書き込み時間を合計し、I/Oが多く発生するクエリを特定します
    • sum(calls) AS total_calls: クエリの総呼び出し回数を計算し、頻繁に呼び出されるクエリを特定します。
SELECT
    queryid,
    substr(query, 1, 100) AS short_query,
    sum(cpu_user_time + cpu_sys_time) AS total_cpu_time,
    sum(blk_read_time + blk_write_time) AS total_io_time,
    sum(calls) AS total_calls
FROM
    pg_stat_monitor
GROUP BY
    queryid, short_query
ORDER BY
    total_cpu_time DESC
LIMIT 10;
  • 特定クエリの時間帯別実行情報の分析

    • to_char(pgss.bucket_start_time, 'YYYY-MM-DD HH24') AS hour: bucket_start_timeをYYYY-MM-DD HH24形式に変換し、各時間帯のデータを集計します。

    • sum(pgss.calls) AS total_calls: 特定のクエリが各時間帯に呼び出された総回数を計算します。

    • sum(pgss.total_exec_time) AS total_exec_time: 特定のクエリが各時間帯に実行された総時間を計算します。

    • WHERE pgss.queryid = : 分析したい特定クエリのqueryidを基にデータをフィルタリングします。

    • GROUP BY hour: 時間帯ごとにデータをグループ化して集計します。

    • ORDER BY hour: 結果を時間帯順に並べ替え、分析しやすくします。

特定のクエリのqueryidを特定する

SELECT
    queryid,
    substr(query, 1, 100) AS short_query
FROM
    pg_stat_monitor
WHERE
    query LIKE 'xxxxxxxx%'  -- 特定のSQLパターン
LIMIT 1;

確認したqueryidをwhere条件に入れてクエリを確認します。

SELECT
    to_char(pgss.bucket_start_time, 'YYYY-MM-DD HH24') AS hour,
    sum(pgss.calls) AS total_calls,
    sum(pgss.total_exec_time) AS total_exec_time
FROM
    pg_stat_monitor AS pgss
WHERE
    pgss.queryid = <your_queryid>  -- 特定クエリのqueryid
GROUP BY
    hour
ORDER BY
    hour;

参考資料

pg_stat_monitor パラメータ設定と説明

パラメータ名 デフォルト値 説明 再起動が必要か
pg_stat_monitor.pgsm_max 256 PG_STAT_MONITORが使用する共有メモリの最大サイズ（MB単位） yes
pg_stat_monitor.pgsm_query_max_len 2048 SQLクエリの最大長を設定します。 yes
pg_stat_monitor.pgsm_track_utility yes SELECTやDML以外のコマンドを追跡するかどうかを設定します。 no
pg_stat_monitor.pgsm_normalized_query no クエリを正規化フォーマットで保存するかどうかを選択します。 no
pg_stat_monitor.pgsm_max_buckets 10 バケットの最大数を設定します。 yes
pg_stat_monitor.pgsm_bucket_time 60 バケットの保持時間（寿命）を設定します（秒単位）。 yes
pg_stat_monitor.pgsm_histogram_min 1 ヒストグラムに表示されるSQLの最小実行時間（ms単位）。 yes
pg_stat_monitor.pgsm_histogram_max 100000 ヒストグラムに表示されるSQLの最大実行時間（ms単位）。 yes
pg_stat_monitor.pgsm_histogram_buckets 20 ヒストグラムバケットの最大数を設定します。 yes
pg_stat_monitor.pgsm_query_shared_buffer 20 SQLの追跡に使用される共有メモリの最大サイズ（MB単位）。 yes
pg_stat_monitor.pgsm_enable_overflow on 共有メモリを超えた場合にSwapへ拡張するかどうかを制御します。 yes
pg_stat_monitor.pgsm_overflow_target 1 オーバーフロー対象を設定します。2.0.0バージョン以降は使用されていません（現在は2.0.1）。 yes
pg_stat_monitor.pgsm_enable_pgsm_query_id on pgsm_query_idを使用するかどうかを設定します。 yes
pg_stat_monitor.pgsm_enable_query_plan no SQL実行計画をモニタリングするかどうかを設定します（使用する場合はデータベース性能に影響あり）。 no
pg_stat_monitor.pgsm_track top 追跡対象のコマンドを指定します（top, all, none）。 no
pg_stat_monitor.pgsm_extract_comments no SQL内のコメントを抽出する機能を有効化するかどうかを設定します。 no
pg_stat_monitor.pgsm_track_planning no 実行計画統計を追跡するかどうかを設定します。 yes

PG_STAT_MONITOR と PG_STAT_STATEMENTS の列比較と説明

※PG_STAT_STATEMENTSの値がOの場合、PG_STAT_STATEMENTでも確認可能な項目になります。

列名 PG_STAT_STATEMENTS 説明
bucket クエリが属するバケット番号
bucket_start_time バケットの開始時間
userid O SQLを実行したユーザーID
username SQLを実行したユーザー名
dbid O SQLが実行されたデータベースID
datname SQLが実行されたデータベース名
client_ip SQLを実行したクライアントのIPアドレス
pgsm_query_id PG_STAT_MONITORのクエリID。正規化されたSQLに基づいて計算されたハッシュ値。
pg_stat_monitor.pgsm_enable_pgsm_query_id パラメータで制御
queryid O SQL ID
toplevel O トップレベルのSQLであるかを示す(TRUEは最上位レベルで実行されたことを意味する)
top_queryid 上位SQLのクエリIDを示す。デフォルトでは最上位レベルのSQLのみ収集される。再帰SQLの追跡にはpg_stat_monitor.pgsm_track = ALLの設定変更が必要。
query O 実行されたSQL文
comments SQLに関する説明
planid SQLの実行計画ID
query_plan SQLの実行計画（単純な実行計画のみ表示）。デフォルトでは収集されず、pg_stat_monitor.pgsm_enable_query_plan = yesの設定変更が必要。
top_query 上位SQL文を表示
application_name SQLを実行したアプリケーション名
relations SQLで参照されたテーブルリスト（schema.table_name）
cmd_type 実行されたSQLのタイプID（1: SELECT、2: UPDATE、3: INSERT、4: DELETE）
cmd_type_text 実行されたSQLのタイプ
elevel SQLエラーレベルの記録(WARNING、ERROR、LOG)
sqlcode SQLエラーコードの記録（詳細は公式ドキュメントを参照）
message SQLエラーメッセージの記録
calls O SQLが実行された回数
total_exec_time O SQLの実行にかかった総時間（ms単位）
min_exec_time O SQLの実行にかかった最小時間（ms単位）
max_exec_time O SQLの実行にかかった最大時間（ms単位）
mean_exec_time O SQLの実行にかかった平均時間（ms単位）
stddev_exec_time O SQL実行時間の標準偏差（ms単位）
rows O SQL実行時に検索された合計行数
shared_blks_hit O キャッシュから返された共有メモリブロックの合計数
shared_blks_read O キャッシュから返されなかった共有メモリブロックの合計数
shared_blks_dirtied O SQL実行によって書き込んだ共有メモリブロックの数
shared_blks_written O SQL実行中に同時にディスクに書き込まれた共有メモリブロック数
local_blks_hit O バックエンドがローカルと見なしたブロック数（一時テーブルに使用される）
local_blks_read O SQL実行中に読み取られたローカルブロックの合計数
local_blks_dirtied O SQL実行によって書き込んだローカルブロックの数
local_blks_written O SQL実行中に同時にディスクに書き込まれたローカルブロック数
temp_blks_read O ディスクから読み取られた一時ファイルの合計ブロック数
temp_blks_written O ディスクに書き込まれた一時ファイルの合計ブロック数
blk_read_time O ブロック読み取りの合計待機時間（ms単位）
blk_write_time O ブロック書き込みの合計待機時間（ms単位）
temp_blk_read_time O コマンドが読み取った合計一時ブロック数
temp_blk_write_time O コマンドによって書き込まれた合計一時ブロック数
resp_calls ヒストグラム呼び出し
cpu_user_time SQLを実行する際に消費されたCPU時間（ms単位）
cpu_sys_time カーネルコード実行時に消費されたCPU時間（ms単位）
wal_records O SQLによって生成されたWAL（Write-Ahead Logging）の合計数
wal_fpi O SQLで生成されたWAL FPI（Full Page Images）の合計数
wal_bytes O SQLによって使用されたWALのサイズ（バイト単位）
bucket_done バケットの状態
- false：アクティブ。バケットに統計情報が追加される可能性あり
- true：完了。バケットに統計情報が追加されない
plans O コマンドが計画された回数
total_plan_time O コマンドの計画にかかった総時間（ms単位）
min_plan_time O コマンドの計画にかかった最小時間（ms単位）
max_plan_time O コマンドの計画にかかった最大時間（ms単位）
mean_plan_time O コマンドの計画にかかった平均時間（ms単位）
stddev_plan_time O コマンド計画時間の標準偏差（ms単位）
jit_functions O コマンドによってJITコンパイルされた関数の総数

まとめ

pg_stat_monitorを活用することで、データベースのパフォーマンスを継続的に改善し、ビジネスニーズに応じた最適なデータベース管理を実現することが可能です。定期的にモニタリングを行い、得られたデータを基に改善策を講じることが重要です。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?