pg_wait_samplingでPostgreSQLの待機イベント統計を取得する

# pg_wait_sampling

pg_wait_samplingは待機イベントの統計を収集するための拡張機能です。
Oracleであれば、statspackやAWRレポート等でデータベース全体の待機イベントの統計を確認するのは容易です。PostgreSQLでは特定プロセスで発生している待機イベントを確認するビューは提供されますが、データベース全体で発生した待機イベントを確認する機能はありません。
pg_wait_samplingを利用することで、データベース全体の待機イベントの統計を容易に確認することができるようになります。

# pg_wait_samplingが収集する統計情報

pg_wait_samplingは待機イベントの履歴とプロファイルという2種類の統計を収集します。

- 待機イベントの履歴

各プロセスで発生した待機イベント、発生時刻、クエリーIDの履歴がメモリ内に保存されます。
デフォルトで最新の5000イベントが保存されますが、これだとすぐに古いイベントが表示されなくなるので大きい値を設定した方が良いかもしれません。ただし、メモリなので大量に保存するのではなく、別に永続化し履歴を保存するような使い方を想定しているようです。

関係するパラメータは以下の2つです。

| パラメータ名                  | 説明  | デフォルト値 |
|---------------------------------|---|---------------|
| pg_wait_sampling.history_size   | 待機イベントの履歴をメモリ内に保存する数。  | 5000          |
| pg_wait_sampling.history_period | 履歴のサンプリングの間隔（ミリ秒）  |  10           |


- 待機プロファイル

プロセス、待機イベント、クエリー毎のイベント発生回数をメモリー内に保存しています。
発生回数は蓄積されており、リセットするための関数が用意されています。
待機イベントの履歴だと件数が多いので、こちらを定期的にバックアップを取って特定の時間帯の待機イベントを調査するような使い方が良いかもしれません。


関係するパラメータは以下の3つです。

| パラメータ名                     | 説明 | デフォルト値 |
|----------------------------------|------|--------------|
| pg_wait_sampling.profile_period  | プロファイルのサンプリングの間隔（ミリ秒）     |  10          |
| pg_wait_sampling.profile_pid     | プロセスID毎にプロファイルするかどうか。デフォルトはtrueで待機イベントはプロセスIDに分けてカウントされます。  | true         |
| pg_wait_sampling.profile_queries | クエリー毎にプロファイルするかどうか。デフォルトはfalseでビューのqueryidは"0"になります。     | false        |

pg_stat_statementsと組み合わせてクエリごとの統計情報も提供できます。



# 待機イベントの一覧（参考）

PostgreSQLの待機イベントは以下の資料に記載があります。

[表28.4 wait_eventの説明(PostgreSQL 11.5文書 28.2. 統計情報コレクタ)](
https://www.postgresql.jp/document/11/html/monitoring-stats.html)


# pg_wait_samplingのインストール

まずはpg_wait_samplingをインストールしてみます。
モジュールを[pg_wait_sampling](https://github.com/postgrespro/pg_wait_sampling)からダウンロードします。
今回はPostgreSQL12に対応しているv1.1.1をインストールしました。


```
# tar xvzf pg_wait_sampling-1.1.1.tar.gz
# cd pg_wait_sampling-1.1.1
# make USE_PGXS=1
# sudo make USE_PGXS=1 install
# make USE_PGXS=1 installcheck # contrib_regressionデータベースが作成しインストールのチェックが行われているみたいですが、実行しなくてもよいはずです。
```

使用するためにはpostgresql.confを以下のように変更します。変更後、PostgreSQLを再起動します。

```
shared_preload_libraries = 'pg_wait_sampling'
```

モジュールを使用するためには、以下を実行します。

```
psql postgres -c "CREATE EXTENSION pg_wait_sampling;"
```


# pg_wait_samplingが提供するビュー

pg_wait_samplingは以下の3つのビューを提供します。

- pg_wait_sampling_currentビュー

現在発生している待機イベントに関する情報を保存するビューです。

- pg_wait_sampling_historyビュー

発生した待機ベントの履歴を保存するビューです。

- pg_wait_sampling_profileビュー

待機イベントのプロファイルを保存するビューです。

ビューの構造は以下のようになっています。詳細は[pg_wait_sampling](https://github.com/postgrespro/pg_wait_sampling)を参照してください。

pg_wait_sampling_currentビュー

- pid
- event_type
- event
- queryid

pg_wait_sampling_historyビュー

- pid
- ts (サンプルのタイムスタンプ)
- event_type
- event
- queryid

pg_wait_sampling_profileビュー

- pid
- event_type
- event
- queryid
- count


待機イベントのプロファイルをリセットする場合は以下の関数を実行します。

```
# select pg_wait_sampling_reset_profile();
```



# SQL

待機イベントの発生回数をイベント種類ごとに集計するSQL。
発生回数は前回リセットしたときからの累積値なので、一旦リセットしてから実行して集計したり、一定期間ごとに自動で保存しておくなどの使い方が良さそう。

```sql
select
  event_type,
  event,
  sum(count) as sum_event
from
  pg_wait_sampling_profile
where
  event is not null
group by
  event_type, event
order by
  sum_event desc;
```

実行結果は以下のとおり。

```
 event_type |        event         |  sum   
------------+----------------------+--------
 Activity   | LogicalLauncherMain  | 114388
 Activity   | WalWriterMain        | 114375
 Activity   | AutoVacuumMain       | 114356
 Activity   | CheckpointerMain     | 114252
 Activity   | BgWriterHibernate    | 100715
 Client     | ClientRead           |  56117
 Activity   | BgWriterMain         |  13669
 IO         | DataFileRead         |      7
 IPC        | MessageQueueInternal |      1
 IO         | WALSync              |      1
 IO         | DataFileSync         |      1
(11 rows)
```

直近5000件（デフォルト）の待機イベントをクエリー毎に集計するSQL。

```sql
select
  queryid, event_type, event, count(event) as count_event
from
  pg_wait_sampling_history
group by
  queryid, event_type, event
order by
  count_event desc;
```

実行結果は以下のとおり。

```
       queryid       | event_type |        event        | count_event 
---------------------+------------+---------------------+-------------
                   0 | Client     | ClientRead          |        1684
 4925838102956557191 | Lock       | transactionid       |         680
 7495463744623052073 | Lock       | transactionid       |         495
                   0 | Activity   | WalWriterMain       |         371
                   0 | Activity   | CheckpointerMain    |         371
                   0 | Activity   | AutoVacuumMain      |         371
                   0 | Activity   | LogicalLauncherMain |         371
                   0 | Activity   | BgWriterHibernate   |         256
 7495463744623052073 | Lock       | tuple               |         134
                   0 | Activity   | BgWriterMain        |         115
                   0 | IO         | WALSync             |          95
 4925838102956557191 | Lock       | tuple               |          50
 4925838102956557191 | LWLock     | buffer_content      |           3
 4925838102956557191 | LWLock     | lock_manager        |           3
                   0 | IO         | WALWrite            |           1
(15 rows)
```


# 参考

- [pg_wait_sampling](https://github.com/postgrespro/pg_wait_sampling)
- [表28.4 wait_eventの説明(PostgreSQL 11.5文書 28.2. 統計情報コレクタ)](
https://www.postgresql.jp/document/11/html/monitoring-stats.html)
- [2018年度WG3活動報告書 性能トラブル調査編](https://pgecons-sec-tech.github.io/tech-report/html_wg3_performance/wg3_performance.html#1fd82ef935d8a282a893171ad2b49cf1)