pg_statsinfoを用いたデータベースの監視～前編～

本ページでは、pg_statsinfoのメリット、統計情報の取得や簡易レポートの出力、アラート機能などについてご紹介します。さらに、サンプルシナリオではデータベースの監視において確認すべきポイントについて解説します。
また、pg_statsinfoの概要についてはこちらで解説していますので合わせてご覧ください。

pg_statsinfoのメリット
pg_statsinfoの機能
pg_statsinfoの使い方
サンプルシナリオ

pg_statsinfoのメリット

pg_statsinfoのメリットについて説明します。
以下の表は、PostgreSQL本体の場合のみとpg_statsinfoを導入した場合とで統計情報の取得・表示について比較したものです。

表1 統計情報の取得・表示についての比較
比較項目	PostgreSQL本体のみ	pg_statsinfo導入
データベース本体の統計情報取得	○	○
データベース周辺の統計情報取得	×	○
時系列一覧表示	×※	○

※現在までの累積値のみ表示可能

pg_statsinfoを用いると、PostgreSQL本体のみの場合と比べてより細かい統計情報の取得が可能となります。この統計情報はデータベース性能のチューニングや問題発生時の解決のために役立ちます。
また、pg_statsinfoはテキストでの出力ですが、グラフィカルな表示が可能なpg_stats_reporterというツールもあります。

pg_statsinfoの機能

pg_statsinfoでは以下のことができます。
ⅰ) 統計情報の取得
ⅱ) 簡易レポートの出力
ⅲ) サーバーログの分配
ⅳ) サーバーログの蓄積
ⅴ) アラートログ機能
ⅵ) 自動メンテナンス
以下は各機能の全体図です。各項目の詳細は後述します。

pg_statsinfoの使い方

pg_statsinfoを使用するにあたり必要な初期設定について説明したあと、ⅰ)統計情報の取得、ⅱ)簡易レポートの出力、ⅴ)アラート機能について解説していきます。その他の機能についてはpg_statsinfoを用いたデータベースの監視～後編～で詳しく解説していますので合わせてご覧ください。

初期設定

1. postgresql.confの修正
設定ファイル(postgresql.conf)の末尾にpg_statsinfo用の設定を追加します。

# Add settings for extensions here

#---------------------------------------------------------------
# pg_statsinfo用の設定
#---------------------------------------------------------------
shared_preload_libraries = 'pg_statsinfo,pg_stat_statements'       # 事前ロード※
pg_statsinfo.snapshot_interval = 30d            # 自動スナップショット取得間隔
pg_statsinfo.enable_maintenance = 'off'         # 自動メンテナンス
pg_statsinfo.maintenance_time = '00:02:00'      # 自動メンテナンス実行時刻
pg_statsinfo.repolog_min_messages = disable     # ログ蓄積
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # ログファイル名
log_min_messages = 'log'                        # ログへの出力レベル
pg_statsinfo.syslog_min_messages = 'error'      # syslogへの出力レベル
pg_statsinfo.textlog_line_prefix = '%t %p %c-%l %x %q(%u, %d, %r, %a) '        # テキストログ各行の先頭の書式 (log_line_prefixと同じ形式で指定する)
pg_statsinfo.syslog_line_prefix = '%t %p %c-%l %x %q(%u, %d, %r, %a) ' # pg_statsinfoがsyslog経由でログを出力する際、各行の先頭の書式
track_functions = 'all'                         # ストアドプロシージャの呼び出しに関する統計情報を収集する
log_checkpoints = on                            # チェックポイント
log_autovacuum_min_duration = 0                 # 自動バキューム

※pg_stats_statementsも入れるとクエリの統計情報も取得できるようになります。

2. pg_statsinfo有効化確認
2-1 pg_statsinfoのプロセスの起動確認
PostgreSQLサーバの起動に伴い、pg_statsinfoのプロセスが起動していることを確認します。

$ ps -ef | grep postgres | grep pg_statsinfo
postgres 184588 184579 0 13:46 ?        00:00:00 postgres: pg_statsinfo launcher
postgres 184590 184588 2 13:46 ?        00:00:07 /usr/pgsql-14/bin/pg_statsinfod 184579
postgres 184655 184526 0 13:51 pts/0    00:00:00 grep --color=auto pg_statsinfo

2-2 pg_statsinfoのログ管理機能の動作確認
pg_statsinfoのログ管理機能が正常に動作しているか確認します。以下の3つのファイルがあれば確認完了です。

表2 ログ種類
種類	ファイル名	ログ出力元	補足
サーバーログ	postgresql-[サーバ起動時のタイムスタンプ].csv	PostgreSQL本体	起動直後、pg_statsinfoにより強制的にCSVログに切り替わる
textlog	pg_statsinfo.log	pg_statsinfo	サーバーログをもとにpg_statsinfoが編集する
コンソールログ	postgresql-[サーバ起動時のタイムスタンプ].log	PostgreSQL本体	-

$ ls -l /var/lib/pgsql/14/data/log
合計 24328
-rw-------. 1 postgres postgres 6739066 2月 8 13:52 pg_statsinfo.log
-rw-------. 1 postgres postgres 10388648 2月 8 13:52 postgresql-2023-02-08_134622.csv
-rw-------. 1 postgres postgres 317 2月 8 13:46 postgresql-2023-02-08_134622.log

3. pg_stat_statementsの登録
pg_stat_statementsを使用したいデータベース上でpg_stat_statementsをPostgreSQLに登録します。

$ psql
postgres=# CREATE EXTENSION pg_stat_statements;
postgres=# \dx

この操作はpg_stat_statementsを使用したいデータベースごとに行う必要があります。

4. PostgreSQLサーバ起動時のプリロードの設定確認
PostgreSQLサーバ起動時にpg_statsinfoとpg_stat_statmentsがプリロードされる設定になっていることを確認します。以下を実行し、pg_statsinfo、pg_stat_statmentsが表示されれば確認完了です。

$ psql
postgres=# show shared_preload_libraries;
shared_preload_libraries
---------------------------------
pg_statsinfo,pg_stat_statements
(1 行)

ⅰ) 統計情報の取得

監視対象(PostgreSQL本体とOSのリソース情報)の統計情報を取得してリポジトリDB(収集情報を格納するためのDB)に保存します。自動で取得する方法と手動で取得する方法があります。

自動取得

取得間隔を指定することで自動でスナップショットを取得することができます。

1. 設定ファイル(postgresql.conf)の修正

$ vi /var/lib/pgsql/14/data/postgresql.conf
#下記の通りパラメータを指定し上書き保存
pg_statsinfo.snapshot_interval = 30s # 自動スナップショットの取得間隔

上記の例では、動作確認のため30秒ごとに自動スナップショットを取得する設定に変更しています。

2. 設定の反映

$ pg_ctl reload
サーバーにシグナルを送信しました

以降の説明では省略しますが、設定ファイルを変更したあとはreloadを行ってください。
なお、pg_statsinfo.snapshot_intervalの設定値の変更は「$ pg_ctl reload」で反映されますが実際の反映のタイミングは変更前の設定値に依存します。

　(例) 1時間間隔で取得する設定から1分間隔で取得する設定に変える場合
　　 →12:01に上記の設定変更をしても、前回の自動取得＆設定値チェックは12:00で次は12:30となるのですぐには変わりません (11:30, 12:00, 12:30, 12:31, 12:32...での自動取得となる)

　　　変更前の設定値が大きい場合、実際の反映までに長時間かかる状況になります。
　　　その場合には必要に応じて「$ pg_ctl restart」で即時反映を行ってください。

3. 取得統計情報(スナップショット)の一覧表示

$ pg_statsinfo -l
----------------------------------------
Snapshot List
----------------------------------------
SnapshotID InstanceID Host                                  Port             Timestamp Comment               Execute Time      Size
-----------------------------------------------------------------------------------------------------------------------------------------
         1           1 localhost.localdomain                 5432   2023-02-08 14:11:30                            00:00:01   496 KiB
         2           1 localhost.localdomain                 5432   2023-02-08 14:12:00                            00:00:00   200 KiB
         3           1 localhost.localdomain                 5432   2023-02-08 14:12:30                            00:00:00   200 KiB

30秒ごとに自動スナップショットが取得されていることが確認できます。動作確認後は設定ファイルを元の設定に戻してください。

手動取得

コマンドラインオプションやSQLの実行により手動でスナップショットを取得することも可能です。

1. 設定ファイル(postgresql.conf)の修正

$ vi /var/lib/pgsql/14/data/postgresql.conf
#下記の通りパラメータを指定し上書き保存
pg_statsinfo.snapshot_interval = 30d # 自動スナップショットの取得間隔

ここでは動作確認のため、30日ごとに自動スナップショットを取得する設定に変更し、自動でのスナップショット取得を実質無効化しています。

2. 統計情報の取得
統計情報（スナップショット）はコマンドラインオプションまたはSQL文の実行により取得することができます。
二つの実行例を示します。
(1)コマンドラインオプションによる取得

$ pg_statsinfo -S '手動取得1 [コマンドライン]'

' ' 内はコメントです。コメント入力なしでスナップショットを取得したい場合は、「 $ pg_statsinfo -S '' 」を実行します。

(2)SQL文の実行による取得

$ psql -c "SELECT statsinfo.snapshot('手動取得2 [SQL文]')"

3. 取得統計情報(スナップショット)の一覧表示

$ pg_statsinfo -l
----------------------------------------
Snapshot List
----------------------------------------
SnapshotID InstanceID Host                                  Port             Timestamp Comment               Execute Time      Size
-----------------------------------------------------------------------------------------------------------------------------------------
         7           1 localhost.localdomain                 5432   2023-02-08 14:15:22 手動取得1 [コマンドライン]      00:00:00   216 KiB
         8           1 localhost.localdomain                 5432   2023-02-08 14:15:37 手動取得2 [SQL文]      00:00:00   208 KiB

手動で取得したスナップショットが一覧に表示されていることが確認できます。

ⅱ) 簡易レポートの出力

ⅰ)で取得した統計情報をもとに簡易レポートを標準出力に表示します。

簡易レポートを出力するコマンドの書式は以下です。

$ pg_statsinfo -r REPORTID [-i INSTANCEID] [-b SNAPID] [-e SNAPID] [-B DATE] [-E DATE] [-o FILENAME] [connection-options]

REPORTIDでは出力する統計情報のカテゴリを指定します。以下の表に一部の例を示します。

表3 レポート出力時に指定できるREPORTIDの例
REPORTID	出力カテゴリ	出力項目
All	全カテゴリ	全項目
Summary	レポートサマリ	データベースクラスタ固有ID、ホスト名など
Alert	アラート出力一覧	スナップショット取得日時、アラートメッセージ
DatabaseStatistics	DB全体の統計情報	データベース名、データベースサイズ [MiB]など

また、以下によく使うオプションを記載します。

表4 簡易レポートの出力の際よく使うオプション
オプション	説明
-r	(必須) レポート種別ID (出力カテゴリ)を指定する
-b	レポート出力の開始時点をスナップショットID で指定する
-e	レポート出力の終了時点をスナップショットID で指定する
-B	レポート出力の開始時刻を指定する
-E	レポート出力の終了時刻を指定する
-o	レポートの出力先ファイル名を指定する

実際のコマンドの例を示します。以下の例では全カテゴリのレポートを出力します。

$ pg_statsinfo -r All

以下の例ではスナップショットID 11～13 を対象としたサマリのみのレポートを指定したファイルに出力します。

$ pg_statsinfo -r Summary -b 11 -e 13 -o /var/lib/pgsql/pg_statsinfo_report_file

実際に出力したレポートとその見方についてはサンプルシナリオでご紹介します。

ⅴ) アラート機能

PostgreSQL本体およびOSリソース情報を監視し、ユーザーが指定した閾値を超えた項目について、textlog、syslog内にアラートログを出力します。日々の運用において必要に応じてログを確認することで、異常をいち早く発見することができます。

1. 設定ファイル(postgresql.conf)の修正

$ vi /var/lib/pgsql/14/data/postgresql.conf
#下記の通りパラメータを指定し上書き保存
pg_statsinfo.enable_alert = on # アラート機能の有効/無効を設定

2. アラート設定テーブルの内容変更

監視対象ごとの閾値は下記のアラート設定テーブルに設定します。アラート設定テーブルは監視対象のインスタンスごとに用意されています。「rollback_tps」～「enable_alert」の値を書き換えることによってアラート設定が変更されます。-1を指定すると該当項目のアラートは無効となります。

例として、不要領域サイズの閾値を変更するコマンドを示します。ここでは動作確認のため0に設定します。この設定では、不要領域が0MBを超えているとアラートが出力されます。

$ psql -c "UPDATE statsrepo.alert SET garbage_size = 0 WHERE instid = 1"
UPDATE 1

3. スナップショットの取得

$ pg_statsinfo -S ''

このタイミングでアラート項目のチェックが行われます。

4. アラートログの確認
textlog・syslog内にアラートログが出力されていることを確認します。

$ cat /var/lib/pgsql/14/data/log/pg_statsinfo.log | grep ALERT
2023-02-16 13:42:00 JST 283179 - (pg_statsinfod, , , pg_statsinfod) ALERT: pg_statsinfo:
dead tuple size exceeds threshold in snapshot '2023-02-16 13:42:00' --- 0.009 MiB (threshold = 0 MiB)
$ cat /var/log/messages | grep ALERT
Feb 16 13:42:00 localhost postgres[283179]: [1547-1] 2023-02-16 13:42:00 JST 283179 - (pg_statsinfod, , , pg_statsinfod) ALERT:
pg_statsinfo: dead tuple size exceeds threshold in snapshot '2023-02-16 13:42:00' --- 0.009 MiB (threshold = 0 MiB)

サンプルシナリオ

データベースの運用においては、性能問題の予防や、万が一問題が発生したときにも迅速な原因特定ができるよう、日々の稼働状況を監視しておくことが大切です。そのために確認すべき項目をサンプルシナリオとしてご紹介します。一部については実際の出力結果も交えて解説します。

$ pg_statsinfo -r SchemaInformation
---------------------------------------------
STATSINFO Report (host: host1, port: 5432)
---------------------------------------------

----------------------------------------
/* Schema Information */
----------------------------------------

/** Tables **/
-----------------------------------
Database          Schema            Table              Columns      Rows        Size   Size Incr Table Scans Index Scans
-----------------------------------------------------------------------------------------------------------------------------
postgres          public            pgbench_accounts         4   2999952     390 MiB       6 MiB            0      2077670
postgres          public            pgbench_branches         3        30       0 MiB       0 MiB       336219       702630
postgres          public            pgbench_history          6     77636       3 MiB       3 MiB           0            0
postgres          public            pgbench_tellers          4       300       0 MiB       0 MiB       319301       719534

/** Indexes **/
-----------------------------------
Database          Schema            Index             Table                   Size   Size Incr Index Scans Rows/Scan Disk Reads Cache Reads Index Key
-------------------------------------------------------------------------------------------------------------------------------------------------------------
postgres          public            pgbench_accounts_pkey pgbench_accounts      64 MiB       0 MiB      2077670      1.000      209415 6189459 aid
postgres          public            pgbench_branches_pkey pgbench_branches       0 MiB       0 MiB       702630      1.000          31       703216 bid
postgres          public            pgbench_tellers_pkey pgbench_tellers        0 MiB       0 MiB       719534      1.000          38      1429473 tid

表5 日々の運用で確認すべき項目
項目	説明
トランザクション量の把握	想定以上にトランザクション量が増えていないかを確認する
キャッシュヒット率	キャッシュヒット率を確認し、この値が低い場合は共有メモリの容量を大きくするなどの対策を行うことで、データベースの処理速度の低下を防ぐことができる
インデックスの有効性	インデックスが有効に使用されているかを確認し、そうでない場合はインデックス定義や実行するSQLの見直しを行うことで、処理性能の向上が期待できる
テーブルごとの変更行数	変更行数を確認することで、処理改善が必要なテーブルを見つけることができる
ロングトランザクション	ロングトランザクションはVACUUMによる不要領域の回収処理を妨げるため、内容と所要時間を確認し有効な対策を行うことでDBの肥大化を防ぐ
自動バキュームの有効性	自動バキュームで回収できなかった無効タプルの数の平均値を確認することでその有効性を判断する。未回収の無効タプルが多い場合には、autovacuum関係のパラメータを調整するなどの対策を行う
WALの情報	WAL出力によるチェックポイントが多発していないか、WALアーカイブの失敗が発生していないか、などを確認する

さらに、上記のうち「インデックスの有効性」について、実行結果も含めて解説します。

インデックスが有効となっている各テーブルにおいて、「Table Scans」<< 「Index Scans」となっていることを確認します。ここで、「Table Scans」はシーケンシャルスキャンの実行回数、「Index Scans」はインデックススキャンの実行回数を表しています。上記の状態になっていない場合は、インデックスの定義や実行しているSQLの見直しなどを行うことが望ましいです。また、インデックス統計表内で「Index Scans」が 0 のインデックスがあった(つまりインデックスが全く使用されていない)場合も見直しが必要です。

本ページでは、pg_statsinfoのメリット、統計情報の取得や簡易レポートの出力、アラート機能についてご紹介しました。その他のログやスナップショットを自動でメンテナンスする機能、ログを加工する機能についてはpg_statsinfoを用いたデータベースの監視～後編～で詳しく解説していますので合わせてご覧ください。

Advancedサポートサービスのご紹介

PostgreSQL保守Advancedサポートサービスでは、PostgreSQL技術者による24時間365日体制のサポートのもと、PostgreSQLの技術的な問い合わせや、障害調査、pg_statsinfoを含む指定のOSSツールのサポートに対応します。詳細はこちらをご覧ください。

資料請求・お問い合わせ

サイト内の現在位置

PostgreSQL