サイト内の現在位置

PostgreSQL

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

図1 機能全体図

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 = 30min            # 自動スナップショット取得間隔
pg_statsinfo.enable_maintenance = 'on'         # 自動メンテナンス
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)に保存します。自動で取得する方法と手動で取得する方法があります。

図2 統計情報の取得
自動取得

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

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

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

ⅱ) 簡易レポートの出力

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

図3 簡易レポートの出力

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

$ 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内にアラートログを出力します。日々の運用において必要に応じてログを確認することで、異常をいち早く発見することができます。

図4 アラート機能

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

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

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

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

$ psql -x -c "SELECT * FROM statsrepo.alert"
-[ RECORD 1 ]---------+--------
instid                | 1                              #監視対象インスタンス(ここは変更しない)
rollback_tps          | 100
commit_tps            | 1000
garbage_size          | -1
garbage_percent       | 30
garbage_percent_table | 30
response_avg          | 10
response_worst        | 60
backend_max           | 100
correlation_percent   | 70
disk_remain_percent   | 20
loadavg_1min          | 7
loadavg_5min          | 6
loadavg_15min         | 5
swap_size             | 1000000
rep_flush_delay       | 100
rep_replay_delay      | 200
enable_alert          | t                        #falseにすると全項目無効となる

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

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

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

$ pg_statsinfo -S ''

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

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

サンプルシナリオ

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

表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ツールのサポートに対応します。詳細はこちらをご覧ください。