【PostgreSQL】pg_stat_databaseによるDB運用状況の確認

2023年12月22日 2023年12月16日

pg_stat_databaseは、一行ごとにデータベース情報がまとめられた統計情報コレクタの一種です。
データベース単位でまとめられているため、大雑把な印象は拭えませんが、テーブルファイルのパスを特定するために必要なdatidや、デッドロックの検知回数を表すdeadlocksなどはメンテナンスに役に立ちます。

postgres=# select * from pg_stat_database;
 datid |  datname  | numbackends | xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fetched | tup_inserted | tup_updated | tup_deleted | conflicts | temp_files | temp_bytes | deadlocks | blk_read_time | blk_write_time |          stats_reset
-------+-----------+-------------+-------------+---------------+-----------+----------+--------------+-------------+--------------+-------------+-------------+-----------+------------+------------+-----------+---------------+----------------+-------------------------------
 12348 | postgres  |           1 |         119 |             2 |    191569 |     7061 |     20051175 |        2895 |           13 |           0 |           0 |         0 |          0 |          0 |         0 |             0 |              0 | 2020-07-05 16:47:19.515797+09
 16384 | testdb    |           0 |           0 |             0 |         0 |        0 |            0 |           0 |            0 |           0 |           0 |         0 |          0 |          0 |         0 |             0 |              0 |
     1 | template1 |           0 |           0 |             0 |         0 |        0 |            0 |           0 |            0 |           0 |           0 |         0 |          0 |          0 |         0 |             0 |              0 |
 12347 | template0 |           0 |           0 |             0 |         0 |        0 |            0 |           0 |            0 |           0 |           0 |         0 |          0 |          0 |         0 |             0 |              0 |
(4 行)

1 OIDによるテーブルファイル格納ディレクトリの特定
2 データベースごとのトランザクション処理数
3 デッドロックの検知回数の確認
4 蓄積した統計情報のリセット

OIDによるテーブルファイル格納ディレクトリの特定

PostgreSQLはテーブルの情報をテーブルファイルに書き出して保持しています。
「$PGDATA/base」以下にデータベースごとにディレクトリが分かれており、その直下にテーブルファイルが格納されるのですが、Linux上で「$PGDATA/base」を参照すると以下のようなディレクトリが配備されています。

[postgres@localhost ~]$ ls -l $PGDATA/base
合計 48
drwx------. 2 postgres postgres 8192 12月  8  2019 1
drwx------. 2 postgres postgres 8192 12月  8  2019 12347
drwx------. 2 postgres postgres 8192  7月  5 17:10 12348
drwx------. 2 postgres postgres 8192  7月  5 16:36 16384
drwx------. 2 postgres postgres    6  6月 20 23:57 pgsql_tmp

数字名のディレクトリが並びます。
ディレクトリの名称がどのデータベースを指すか不明のため、この状態では調べたいデータベースのテーブル情報が、どれに含まれているのか判断することができません。

そこで確認するのが「pg_stat_database」です。

postgres=# select datid,datname from pg_stat_database;
 datid |  datname
-------+-----------
 12348 | postgres
 16384 | testdb
     1 | template1
 12347 | template0
(4 行)

「datid」はデータベースを識別するためのOIDです。
このOIDがそのまま「$PGDATA/base」以下のディレクトリ名と一致します。例えば「testdb」の「datid」は"16384"なので、実ファイルの格納ディレクトリは 「$PGDATA/base/16384」となります。

データベースごとのトランザクション処理数

パラメータ	内容
xact_commit	該当データベースでトランザクションがCOMMITされた回数です。 BEGINされていない状態の場合、SQLは都度COMMIT扱いになるので、SQLが正常終了するたびにカウントアップします。 BEGINされている状態の場合、トランザクション内で何度SQLを実行したとしても、COMMITしたタイミングでカウントアップ（1回）します。
xact_rollback	該当データベースでトランザクションがROLLBACKされた回数です。 BEGINとの関係は「xact_commit」と同じです。ただしSQLが構文エラーなどで異常終了した場合BEGINされているかどうかに関わらず、即座に「xact_rollback」がカウントアップします。

デッドロックの検知回数の確認

postgres=# select datid,datname,deadlocks from pg_stat_database;
 datid |  datname  | deadlocks
-------+-----------+-----------
 12348 | postgres  |         1
 16384 | testdb    |         0
     1 | template1 |         0
 12347 | template0 |         0
(4 行)

deadlocksによってデッドロックの検知回数が確認できます。
本来デッドロックは回避できるようアプリケーションは構築されるはずなので、この値が1以上となっているデータベースは注意が必要です。
惜しむらくはどのテーブルでデッドロックが起こったかなど、原因究明をするための情報はpg_stat_databaseに存在しないことです。
そのため根本的なデッドロックの解決は他の手段による調査が必要になります。

蓄積した統計情報のリセット

何かのきっかけで今までの統計情報を削除したいことがあるかもしれません。
統計情報は手動でリセットすることができ、「SELECT pg_stat_reset();」と実行すれば、ログイン中のデータベースに蓄積している統計情報をリセットします。
※リセットにはスーパーユーザー権限が必要となります

リセットされた場合、自動的にstats_reset（最終統計情報リセット日時）が更新されます。