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

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

1. pg_statsinfoとは

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

表1 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のパラメーター

• 注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を作成すると、各データベース作成直後のスキーマ構成は以下になります。

サンプルデータの収集には、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 アラート設定テーブル

ヒント

アラート機能全体を無効化する場合は、表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 ログへ出力するメッセージレベルのパラメーター

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

• 注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日公開

こちらもおすすめ

• データベースシステムの監視 ～監視の概要～
• データベースシステムの監視 ～監視方法と監視例～

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