pg_statsinfoで統計情報を収集・蓄積する
PostgreSQLインサイド

PostgreSQLシステムを長期的に運用する上で、データベースのレスポンスやパフォーマンスに劣化が生じていないか定期的に監視し、必要に応じてシステムを調整・改善していくことは非常に重要です。PostgreSQLの周辺ツールの1つであるpg_statsinfoは、サーバーの統計情報や性能情報を収集・蓄積・表示することで、PostgreSQLの日々の処理傾向の把握、性能劣化などの兆候や問題発生時の原因把握に役立ちます。

1. pg_statsinfoとは

pg_statsinfoは、PostgreSQLサーバーの統計情報や稼働状況を一定の時間間隔で取得する機能、およびサーバーログを別のログに分配・蓄積する機能があります。また、収集した統計情報を確認してアラートを出す機能もあります。さらに、蓄積した情報を基にテキスト形式のレポートを出力するコマンドを提供します。pg_statsinfoが提供する機能を以下にまとめます。なお、本記事ではマニュアルと同様にpg_statsinfoで取得した統計情報をスナップショット、スナップショットの保存先のデータベースをリポジトリーDBと定義します。

表1 pg_statsinfoの機能

機能名 説明
スナップショットの取得 PostgreSQLのスナップショットを一定の間隔で取得し、リポジトリーDBに保存します。PostgreSQLの統計情報コレクターが収集するすべての情報、性能やOSのリソースに関する情報をスナップショットとして取得できます。スナップショットは各インスタンスのデータベースクラスタ単位で取得します。
サーバーログの分配 PostgreSQLが出力するサーバーログをCSVログ、テキストログ、syslogに分配して出力します。また、メッセージレベルや特定ユーザーによるログの出力制御などの加工ができます。
サーバーログの蓄積 PostgreSQLが出力するサーバーログを収集し、リポジトリーDBに蓄積します。また、メッセージレベルや特定ユーザーによるログの蓄積制御ができます。蓄積したログは蓄積ログと定義します。
アラート スナップショット取得時にリポジトリーDB内にアラート設定テーブルが生成され、そのテーブルの値を基に監視対象インスタンスの状態をチェックします。問題を検知した場合にアラートメッセージをテキストログに出力します。
自動メンテナンス 1日1回、任意の時刻に、古いスナップショットの削除、蓄積ログの削除、サーバーログのログファイルの整理を自動で実行します。
簡易レポートの出力 リポジトリーDBに保存されたスナップショットからテキスト形式でレポートを出力するコマンドを提供します。
運用管理コマンド pg_statsinfoの運用管理を行うためのコマンドを提供します。

pg_statsinfoは、監視対象のサーバーにエージェントをインストールして、PostgreSQLの設定ファイルの値やユーザーのコマンド実行により動作します。以下にpg_statsinfoの機能の仕組みを図解します。

図1 pg_statsinfoの機能概要

参考

  • リポジトリーDBは、監視対象インスタンスと同一インスタンスでも、別インスタンス、または別のサーバーでも設定可能です。また、1つのリポジトリーDBに複数の監視対象インスタンスのスナップショットを格納することもできます。
  • pg_statsinfoの仕様については、オープンソース・ソフトウェアに同梱されているマニュアルを参照してください。
  • pg_statsinfoは便利な機能ですが、注意点もあります。ご利用の前には必ず「4. pg_statsinfo利用時の注意点」をお読みください。
  • pg_statsinfoで収集したスナップショットは、別の周辺ツールであるpg_stats_reporterを利用してグラフィカルな形で解析・出力することがきます。

2. 設定の準備

pg_statsinfoを利用するには、まず、必要な情報がログファイルに出力されるよう、PostgreSQLの設定ファイルであるpostgresql.confにパラメーターを設定します。なお、pg_statsinfoはLinux上で動作します。今回は、pg_statsinfo 12.0をベースに説明します。

PostgreSQLおよびpg_statsinfoのインストールは完了し、監視対象DBとリポジトリーDBは同一インスタンスになるよう構成します。インスタンスのエンコーディングはUTF8です。

  1. postgresql.confのパラメーターを設定します。図1に赤色で示したpg_statsinfoのエージェントの機能はこのパラメーターの値によって動作しますので、事前に設計する必要があります。表2の設定例は、CSVログを出力する設定値とし、代表的なパラメーターを挙げて説明しています。デフォルト値およびその他についてはオープンソース・ソフトウェアに同梱されているマニュアルを参照してください。

表2 pg_statsinfoに必要なpostgresql.confのパラメーター

機能 パラメーター 設定例 説明
スナップショットの取得 shared_preload_libraries 'pg_statsinfo' サーバー起動時にプリロードされる共有ライブラリを指定する。
track_functions all 関数の呼び出しに関する統計情報を収集するにはplまたはallを指定する。
pg_statsinfo.snapshot_interval 30min スナップショットの取得間隔(分)を指定する。
pg_statsinfo.repository_server 'port=5432 dbname=repo user=postgres' リポジトリーDBへの接続文字列を指定する。
pg_statsinfo.target_server 'port=5432 dbname=mydb' 監視対象DBの接続文字列を指定する。
pg_statsinfo.excluded_dbnames 'template0, template1,postgres,repo' 監視対象から除外するデータベース名を指定する。
サーバーログの分配 logging_collector on ログの収集を指定する。強制的にonが設定される。
log_destination 'csvlog' ログの出力方法を指定する。強制的に'csvlog'が設定され、'stderr'は削除される。'syslog'をカンマ区切りで追加できる。
log_filename 'stats-%Y-%m-%d_%H%M%S.log' CSVログおよびテキストログのファイル名を指定する。
log_min_messages warning ログに出力するメッセージレベルを指定する。(注1
log_checkpoints on onを指定することで、チェックポイントの実行をログに残す。
log_autovacuum_min_duration 0 自動バキューム状況で、指定された時間(ミリ秒)以上かかったログを残す。
0:すべて
-1:無効
lc_messages 'ja_JP.UTF-8' メッセージが表示される言語を設定。エンコーディングの値と統一。
pg_statsinfo.textlog_min_messages error テキストログへ出力するメッセージレベルを指定する。無効はdisable。(注1
pg_statsinfo.textlog_filename 'pg_statsinfo.log' テキストログのファイル名を指定する。空文字はエラー。
pg_statsinfo.textlog_line_prefix '%t %p %c-%l %x %q(%u, %d, %r, %a) ' テキストログの各行の先頭に追加される文字列を指定する。
pg_statsinfo.syslog_min_messages disable syslogへ出力するメッセージレベルを指定する。無効はdisable。(注1
サーバーログの蓄積 pg_statsinfo.repolog_min_messages disable 蓄積ログへ出力するメッセージレベルを指定する。無効はdisable。(注1)(注2
アラート pg_statsinfo.enable_alert on アラート機能の有効/無効を指定する。
・on:アラート機能有効
・off:アラート機能無効
自動メンテナンス pg_statsinfo.enable_maintenance 'on' 自動メンテナンス機能の設定を以下より選択する。1)から3)はカンマ区切りで複数指定できる。
1)'snapshot':スナップショット削除を実行
2)'repolog':蓄積ログ削除を実行
3)'log':ログファイル整理コマンドを実行
4)'on':1)から3)をすべて実行
5)'off':自動メンテナンス無効
pg_statsinfo.maintenance_time '00:02:00' 自動メンテナンスの実行時刻(時:分:秒)を指定する。実行は1日に1回。
pg_statsinfo.repository_keepday 7 スナップショットの保持期間(日)を指定する。
pg_statsinfo.repolog_keepday 7 蓄積ログの保持期間(日)を指定する。
  • 注1
    設定する値は他のパラメーターとの組合せが必要です。詳細については表4を参照してください。
  • 注2
    監視対象DBとリポジトリーDBが同一インスタンスの場合は、disable(無効)が推奨です。
  1. pg_hba.confを設定します。データベースへの接続ユーザーがlocalhostからのアクセスでパスワードの入力が不要になるよう設定します。認証方式は「ident」を推奨します。

    # TYPE   DATABASE     USER            CIDR-ADDRESS    METHOD  [for UNIX]
    local    all          postgres                        ident
  2. PostgreSQLを起動または再起動します。PostgreSQLの起動と連動してpg_statsinfoのエージェントが起動されます。

3. pg_statsinfoの使い方

実際にpg_statsinfoを動作させて、統計情報を収集・蓄積・確認する方法について順を追って見ていきましょう。また、アラート値の変更やサーバーログの加工方法も説明します。 リポジトリーDBとしてrepoを作成し、監視対象DBとしてmydbを作成すると、各データベース作成直後のスキーマ構成は以下になります。

データベース名 スキーマ名 補足
repo public,statsrepo 表2のpg_statsinfo.repository_serverパラメーターに「repo」をリポジトリーDBとして設定することで、pg_statsinfoがstatsrepoスキーマを自動生成
mydb public,statsinfo 表2のpg_statsinfo.target_serverパラメーターに「mydb」を監視対象DBとして設定することで、pg_statsinfoがstatsinfoスキーマを自動生成

サンプルデータの収集には、PostgreSQLに同梱されているベンチマークツール「pgbench」を利用します。ここでは、mydbというデータベース上で、50クライアント、1000トランザクションでpgbenchを実行してみます。

$ pgbench -i mydb
$ pgbench -c 50 -t 1000 mydb

3.1 スナップショットのレポート出力

pg_statsinfoが取得したスナップショットをレポートに出力して状況を確認します。スナップショットの情報は、pg_statsinfoのコマンドを使うことにより、リポジトリーDBに保存されたスナップショットから任意の期間のレポートを、テキスト形式で標準出力に表示します。

  1. スナップショットが取得できているかを確認します。

  2. 指定した日以降のスナップショットの概要を表示する場合、レポート種別に「-r summary」、開始日に「-B 2020-11-14」を指定します。

ヒント

pg_statsinfoコマンドの-rオプションの後に指定できるレポート種別IDは以下です。大文字小文字は区別なく、頭文字から最短一致で指定可能です。レポート種別IDとレポートの内容についてはオープンソース・ソフトウェアに同梱されているマニュアルを参照してください。以下は、レポート種別IDとして「Summary」を小文字で最短で指定した例です。

例)$ pg_statsinfo -r su

  • Summary:スナップショットの概要
  • Alert:アラートの出力情報
  • DatabaseStatistics:データベースの統計情報
  • InstanceActivity:トランザクションログ(WAL)に関する情報
  • OSResourceUsage:CPU、IO、メモリー使用量などの情報
  • DiskUsage:ディスク使用量
  • LongTransactions:トランザクションに関する情報
  • NotableTables:テーブルに関する情報
  • CheckpointActivity:チェックポイントに関する情報
  • AutovacuumActivity:自動バキュームに関する情報
  • QueryActivity:SQL文の問い合わせに関する情報
  • LockConflicts:ロック待ちに関する情報
  • ReplicationActivity:レプリケーションに関する情報
  • SettingParameters:パラメーターの設定値
  • SchemaInformation:テーブルとインデックスに関する情報
  • Profiles:処理名
  • All:上記の全情報

3.2 運用状態の監視

スナップショットの情報を使ってPostgreSQLの運用状態を確認してみます。

  1. スナップショットの「データベースの統計情報」を表示して、「Cache Hit Ratio(キャッシュヒット率)」を確認します。

    状況を見てチューニングします。「Cache Hit Ratio」の値が90%を下回っていたら、メモリー不足と推測できます。postgresql.confのshered_buffersパラメーターの値を追加するなど検討してください。メモリーのチューニングの詳細については、「データベースチューニング」の「2.2 メモリーに関するパラメーター」を参照してください。

  2. スナップショットの「ディスク使用量」を表示して「Remain(テーブルスペースのディスク空き容量)」を確認します。

    テーブルスペースのディスク空き容量が少なくなっていることが分かります。スナップショットで取得する情報の一部(1秒間のコミット数、テーブルスペースのディスク空き容量など)をアラート機能が監視対象としています。アラート機能を使って早めにアラームをあげることも可能です。

3.3 アラートメッセージの出力

pg_statsinfoは、スナップショット取得時に監視対象インスタンスの状態を定期的にチェックし、問題を検知した場合にアラートメッセージをテキストログに出力します。アラートメッセージは、メッセージレベルが「ALERT」で出力されます。なお、アラート機能で検出したアラートの内容は、リポジトリーDBにも蓄積されます。アラート機能による出力情報は性能ボトルネックを特定し、改善に役立てることができます。

3.3.1 アラート値(閾値)の確認

表2のpg_statsinfo.enable_alertパラメーターでアラート機能を有効(on)に設定した場合、初回のスナップショットが完了した監視対象インスタンスに対して自動的に有効になります。初期状態のアラート条件(閾値)はアラート設定テーブルの初期値が適用されます。アラート設定テーブルは、リポジトリーDB内に「statsrepo.alert」テーブルとして生成されます。テーブルのカラムを表3に示します。アラート条件(閾値)を変更する場合は、各カラムの値をUPDATEコマンドで変更します。各カラムの値を「-1」に設定すると、カラム単位にアラート機能を無効化できます。

表3 アラート設定テーブル

カラム名 デフォルト値 説明
instid   監視対象インスタンスID(変更不可)
rollback_tps 100 1秒間のロールバック数の閾値
commit_tps 1000 1秒間のコミット数の閾値
garbage_size -1 監視対象インスタンス中の不要領域のサイズの閾値(MB)
garbage_percent 30 監視対象インスタンスに占める不要領域の割合の閾値(%)
garbage_percent_table 30 各テーブルに占める不要領域の割合の閾値(%)
response_avg 10 クエリの平均レスポンス時間の閾値(秒)
response_worst 60 クエリの最長レスポンス時間の閾値(秒)
backend_max 100 テーブルの断片化率(correlation)の閾値(%)
correlation_percent 70 各テーブルの相関係数(correlation)(%)
disk_remain_percent 20 テーブルスペースのディスク空き容量の閾値(%)
loadavg_1min 7 過去1分間のロードアベレージの閾値
loadavg_5min 6 過去5分間のロードアベレージの閾値
loadavg_15min 5 過去15分間のロードアベレージの閾値
swap_size 1000000 スワップ使用量の閾値(KB)
rep_flush_delay 100 レプリケーションのマスタとスタンバイ間のWAL書き込み遅延量の閾値(MB)
rep_replay_delay 200 レプリケーションのスタンバイのリカバリー遅延量の閾値(MB)
enable_alert true アラート対象判定フラグ(true:有効、false:無効)

ヒント

アラート機能全体を無効化する場合は、表2のpg_statsinfo.enable_alertパラメーターに「off」を設定します。監視対象インスタンス単位でアラート機能を無効化する場合は、その監視対象インスタンスの表3のenable_alertカラムに「false」を設定します。

3.3.2 アラート値の変更

テーブルスペースのディスク空き容量のアラート値を変更して、テキストログにアラートメッセージを出力してみます。

  1. まず、監視対象インスタンスIDを確認します。

  2. アラート設定テーブルのディスク空き容量の閾値を確認します。

  3. 監視対象インスタンスID「1」のアラート設定テーブルのディスク空き容量の閾値の「20%」を「40%」に変更します。

  4. アラート設定テーブルのディスク空き容量の閾値が変更されたかを確認します。

  5. スナップショットの取得間隔に合わせて「Alert」のレポートが出力されているかを確認します。

  6. テキストログに「ALERT」が出力されているかを確認します。

3.4 サーバーログの加工

PostgreSQLでは大量のサーバーログの中から必要な情報に絞って参照したい場合があります。pg_statsinfoはPostgreSQLが出力するサーバーログをフィルタリングしてpg_statsinfo独自のログに加工できます。pg_statsinfoを使うことで、CSVログ、テキストログ、syslogのそれぞれが出力するメッセージレベルを変更したり、特定のユーザーのログを出力制御することができます。また、テキストログのファイル名は表2のpg_statsinfo.textlog_filenameパラメーターで固定(デフォルトはpg_statsinfo.log)できるため、システム監視用のソフトウェアを使ってログ監視に利用できます。

  1. サーバーログに出力するメッセージレベルの変更は、表4に示す4つのパラメーターに設定します。

  2. 生成されているサーバーログを確認します。

    pg_statsinfoが加工して出力するログには以下があります。

    • テキストログ:PostgreSQLが出力する最新のCSVログの情報を元にpg_statsinfoが加工したログです。
    • 保存用テキストログ:上記のテキストログをlog_filenameパラメーターで定義された名前にリネームしたファイルです。ログのローテーションの際に生成されます。ログのローテーションについては、postgresql.confの値に従います。

3.5 運用に必要な作業

pg_statsinfoを使った監視運用時に必要な作業について説明します。

サーバーログの削除

自動メンテナンス機能を設定することで、不要になったスナップショットや蓄積ログは定時に削除されますが、ログファイル(CSVログとテキストログ)は圧縮アーカーブされるだけで、削除されません。不要になったログファイルは、手動で定期的に削除してください。

監視対象インスタンス情報の削除

不要になった監視対象インスタンスの情報を削除する場合は、リポジトリーDBを直に操作します。なお、インスタンス情報を削除すると、関連するすべてのスナップショットが削除されるので、注意してください。以下の例では監視対象インスタンスIDは「1」とします。

注意

リポジトリーDBの管理する情報を操作する場合は、リポジトリーDBのテーブル構成を知る必要があります。リポジトリーDBのテーブルの詳細については、オープンソース・ソフトウェアに同梱されているマニュアルを参照してください。

ログのローテーション

postgresql.confに指定したログ保持に関するパラメーターと異なる任意のタイミングでログをローテーションさせたい場合は、監視対象インスタンスで以下を実行します。

異常終了時の対処

pg_statsinfoのみが異常終了した場合、PostgreSQLのインスタンスには影響ありませんが、pg_statsinfoの機能は停止したままになります。pg_statsinfoを再起動するには、PostgreSQLのインスタンスを再起動します。なお、pg_statsinfoが異常終了してから再起動するまでの間に出力されたサーバーログは、再起動時にサーバーログの分配機能のみ作動して分配されます。

4. pg_statsinfo利用時の注意点

ここでは、実際にpg_statsinfoを利用する際に発生する可能性のある課題とその対策について紹介します。

複数の監視対象インスタンスを1つのリポジトリーDBで管理する場合

複数の監視対象インスタンスのスナップショットを同一のリポジトリーDBに蓄積する構成では、自動メンテナンス設定のスナップショット保持期間の設定に注意が必要です。複数の監視対象インスタンスで自動メンテナンスのスナップショット削除を有効とする場合、すべてのインスタンスに対して、最も期間の短いスナップショット保持期間が有効となります。

  • 例)
    インスタンス1:pg_statsinfo.repository_keepday = 7
    インスタンス2:pg_statsinfo.repository_keepday = 5 <-- インスタンス1と2のスナップショット保持期間としてこちらが採用される

スナップショットのサイズ

スナップショットのサイズは、監視対象インスタンス内のオブジェクト数、スナップショットの取得間隔およびスナップショットの保持期間に依存して増加します。そのため、スナップショットの取得は、それらのパラメーター値とリポジトリーDBのディスク容量を考慮して設定する必要があります。なお、スナップショットのサイズ概算については「pg_statsinfoのサイジング」を参照してください。

サーバーログへ出力するメッセージレベルのパラメーター

サーバーログの分配機能に関する以下のパラメーターは相互に影響しあうので注意が必要です。log_min_messagesパラメーターの設定値がpg_statsinfoで出力されるメッセージレベルの基準となります。他の3つのパラメーターの設定値は、log_min_messagesパラメーターに設定した値と同じ、またはそれよりもログへの出力情報が少ない値(表4の右側)を設定する必要があります。

表4 ログへ出力するメッセージレベルのパラメーター

No. postgresql.confのパラメーター 設定値(注3
1 log_min_messages(注4 debug5~1 info notice warning error log        
2 pg_statsinfo.textlog_min_messages debug info notice warning error log fatal panic alert disable
3 pg_statsinfo.syslog_min_messages debug info notice warning error log fatal panic alert disable
4 pg_statsinfo.repolog_min_messages debug info notice warning error log fatal panic alert disable

下線はデフォルト。赤字は本記事の設定値。

  • 注3
    右側がよりメッセージ深刻度レベルが高く、ログへの出力情報が少なくなります。
  • 注4
    pg_statsinfoと連携する場合、「fatal」と「panic」は指定できません。

エンコーディングの扱い

監視対象インスタンスでは、エンコーディングとlc_messagesパラメータ―の値を統一する必要があります。pg_statsinfoはPostgreSQLがサポートするエンコーディングやメッセージ・ロケールに対応していますが、別々の値を設定することはできません。

その他、HA構成や複数の監視対象インスタンス構成での注意点などは、オープンソース・ソフトウェアに同梱されているマニュアルを参照してください。

ここまで説明してきましたが、pg_statsinfoは比較的簡単な手順で利用できることがおわかりいただけたと思います。「PostgreSQLの拡張機能でよく使う周辺OSSの一覧」では、監視の分類としてpg_statsinfoやpgBadgerなどを紹介しています。pgBadgerと比較すると、スナップショットの対応範囲が狭いですが、システムポリシー上、データベース内に処理やオブジェクトを追加してのパフォーマンス分析が難しいケースにおいて利用可能です。監視の用途に合わせて、ご利用を検討してください。pgBadgerとpg_statsinfoの機能比較については「pgBadgerでログファイルを解析し、統計レポートを作成する」を参照してください。

参考

FUJITSU Software Enterprise Postgresでは、バージョン10からpg_statsinfoを同梱しており、インストールは不要で、FUJITSU Software Enterprise Postgresが動作するLinuxのバージョンをサポートしています。FUJITSU Software Enterprise Postgresに同梱しているpg_statsinfoは、富士通の24時間365日保守サポートにより、PostgreSQL本体およびpg_statsinfoを含む周辺ツールのご質問、トラブル対応およびバグ修正にも迅速に対応しますので、ご利用を検討ください。

2021年2月12日公開

オンデマンド(動画)セミナー

    • PostgreSQLに関連するセミナー動画を公開中。いつでもセミナーをご覧いただけます。
      • 【事例解説】運送業務改革をもたらす次世代の運送業界向けDXプラットフォームの構築
      • ハイブリッドクラウドに最適なOSSベースのデータベースご紹介

本コンテンツに関するお問い合わせ

お電話でのお問い合わせ

Webでのお問い合わせ

当社はセキュリティ保護の観点からSSL技術を使用しております。

ページの先頭へ