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

## はじめに
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 = <your_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に基づいて計算されたハッシュ値。<br>`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エラーコードの記録（詳細は[公式ドキュメント](https://www.postgresql.org/docs/current/errcodes-appendix.html)を参照）                                          |
| **`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`**          |                        | バケットの状態<br>- `false`：アクティブ。バケットに統計情報が追加される可能性あり<br>- `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を活用することで、データベースのパフォーマンスを継続的に改善し、ビジネスニーズに応じた最適なデータベース管理を実現することが可能です。定期的にモニタリングを行い、得られたデータを基に改善策を講じることが重要です。