pg_statsinfo 2.2.0


  1. pg_statsinfoとは?
  2. 機能概要
    1. 統計情報の取得機能
    2. サーバログ処理機能
    3. アラート機能
    4. 簡易レポート機能
  3. インストール
    1. 動作環境
    2. Linux
  4. 使い方
    1. 起動と終了
    2. スナップショットの取得・削除
    3. ログファイルの出力について
    4. アラート機能の使い方
    5. 簡易レポート機能の使い方
    6. 設定ファイル
    7. 運用上必要となる作業
  5. アンインストール
  6. 使用上の注意と制約
  7. よくあるQ&A
  8. 詳細情報
    1. 複数の監視対象インスタンス
    2. ウォームスタンバイ
    3. 内部構成
  9. 関連項目

pg_statsinfoとは?

PostgreSQL サーバの利用統計情報を定期的に収集・蓄積することで、DB設計やPostgreSQLの運用(日々の処理傾向の把握、性能劣化などの兆候や問題発生時の原因の把握等)に役立つツールです。起動や終了、パラメータの設定は PostgreSQL と密に連携しており、手間をかけずに導入可能です。

機能概要

pg_statsinfo は、PostgreSQL サーバの統計情報や活動状況を一定の時間間隔毎に定期的に収集し蓄積する機能と、 PostgreSQLの出力するサーバログを解析することでSQLの性能情報を取得する機能やログ出力を加工する機能があります。 また、蓄積した情報を元にテキスト形式のレポートを出力するコマンドを提供します。

また、pg_statsinfo で収集した情報は pg_reporter を用いることでグラフィカルな形で解析・出力することがきます。(pg_reporterの出力例はこちら

pg_statsinfo の動作概要のイメージ図を以下に示します。



統計情報の取得機能

統計情報は一定の時間間隔で取得され、リポジトリ・データベース (以下 リポジトリDB) に保存されます。 統計情報は各インスタンスのデータベースクラスタ単位で取得できます。 リポジトリDBは、監視対象インスタンスと同一インスタンスのデータベースでも、別インスタンスでも設定可能です。 また、1つのリポジトリDBに対して複数の監視対象インスタンスの統計情報を格納することもできます。 なお 以降では pg_statsinfo で取得した統計情報を、スナップショットと定義します。

スナップショットの取得

スナップショットの取得方法は以下の通りです。

取得できる統計情報一覧

スナップショットとして以下の統計情報を収集します。

スナップショットのサイズは、DB内のオブジェクト数に依存しますが、概ね1回のスナップショットで1DBあたり600 - 800KBを消費します。 取得間隔が5分の場合、監視対象インスタンス一つあたり1日で90 - 120MBを消費します。

サーバログ処理機能

古いサーバログは自動的には削除されないため、ユーザ操作で定期的に削除して下さい。

アラート機能

監視対象インスタンスの状態を定期的 (スナップショット取得時) にチェックし、問題を検知した場合にアラートメッセージをサーバログに出力します。
アラートメッセージは、メッセージレベル"ALERT"で出力されます。

アラート機能で判定する項目は以下のとおりです。
監視対象インスタンス毎に、アラート機能の有効/無効とアラート条件(閾値)を設定することができます。

アラート機能の設定方法はこちらをご覧ください。

簡易レポート機能

リポジトリDBに保存されたスナップショットから任意の期間のレポートをテキスト形式で出力するコマンドを提供します。
また、レポートの出力以外に以下の操作を行うことができます。

簡易レポート機能が出力するレポートの項目についてはこちらをご覧ください。
なお、簡易レポート機能が出力するレポートの項目は pg_reporter と同等です。
グラフを用いたグラフィカルなレポートを出力したい場合は pg_reporter を使用してください。

簡易レポート機能の使用方法はこちらをご覧ください。

インストール

pg_statsinfo のインストール方法について説明します。各インストールパッケージはこちらからダウンロードして下さい。PostgreSQLをソースからインストールした場合は、configureオプションで--with-libxmlを付けてコンパイルをする必要があります。

動作環境

PostgreSQL
バージョン 8.3, 8.4, 9.0, 9.1
動作検証済みOS
RHEL 5.x, RHEL 6.x, CentOS 5.x, CentOS 6.x

Linux

RPM

以下はPostgreSQL8.4のRHEL5のx86_64用のrpmをインストールする例です。
$ su
# rpm -ivh pg_statsinfo-2.0.1-1.pg84.rhel5.x86_64.rpm

source

ソースコードからビルドするには、pgxs を使用します。 なお、pg_statsinfo の登録スクリプト (sql) を手動でインストールする必要はありません。 監視対象インスタンス、リポジトリDB共に、初回起動時に必要に応じて pg_statsinfo がスキーマを自動的にインストールします。

$ tar xzvf pg_statsinfo.tar.gz
$ cd pg_statsinfo
$ make USE_PGXS=1
$ su
# make USE_PGXS=1 install
pgxsを使用しない場合、contrib配下にpg_statsinfoのフォルダを配置し、make, make installを実施してください。

contrib/pg_statsinfo.sql、contrib/pg_statsrepo.sqlは自動的にインストールされるため、手動での実行は不要です。

設定ファイル

postgresql.confの設定

監視対象の PostgreSQL インスタンスを停止した状態で、postgresql.conf に以下の設定を行います。 この設定では、スナップショットの保存先は同一インスタンスの postgres データベースになります。 これら以外の設定については、「設定ファイル」を参照して下さい。

#最小設定
shared_preload_libraries = 'pg_statsinfo'       # 事前ロードを行う
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # ログファイル名を指定する
#推奨設定
shared_preload_libraries = 'pg_statsinfo'       # 事前ロードを行う
custom_variable_classes = 'pg_statsinfo'        # pg_statsinfoのパラメータ指定するために必要

pg_statsinfo.snapshot_interval = 30min          # スナップショットの取得間隔
pg_statsinfo.enable_maintenance = 'on'          # リポジトリDBの自動メンテナンス設定
pg_statsinfo.maintenance_time = '00:02:00'      # リポジトリDBの自動メンテナンス実行時刻設定
pg_statsinfo.repository_keepday = 7 	        # スナップショットの保持期間設定

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) '
   # pg_statsinfoがテキストログに出力する際、各行の先頭に追加される書式を指定する。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_statsinfo.long_lock_threashold = 30s        # ロック競合情報に記録する対象の条件(閾値)を指定する

pg_statsinfo は以下の設定を強制的に上書きすることに注意してください。

log_destination
'csvlog'が追加され、'stderr'は除去されます。
logging_collector
'on' に設定されます。

pg_hba.confの設定

PostgreSQL 起動ユーザでの localhost からのアクセスではパスワードの入力が不要になるよう設定します。 この際の認証には ident 方式を推奨します。 一般的によく利用される「OSユーザ名 = DB管理者名 = postgres」の場合には、pg_hba.conf に以下を追加します。 他の認証方式よりも優先するため、ファイルの最初のほうに書く必要があることに注意してください。 UNIX 環境では TYPE=local の ident 認証を使うのが手軽です。

# TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD [for UNIX]
local   all             postgres                                ident

クエリの統計情報の取得設定

なお、PostgreSQL のバージョン 8.4 以上を使っている場合、監視対象インスタンスの postgres データベースに pg_stat_statements をインストールすることで、クエリの統計情報もスナップショットとして収集できるようになります。
利用する場合には、postgresql.conf の shared_preload_libraries に pg_stat_statements を追加し、初回起動時に以下の手順で登録してください。

$ psql -d postgres -f $PGSHARE/contrib/pg_stat_statements.sql

以上でインストールは終了です。

使い方

pg_statsinfo の操作方法と各種設定について説明します。

起動と終了

起動は、通常通りPostgreSQLを起動するだけです。 PostgreSQL の起動と連動して pg_statsinfo も自動的に起動します。 pg_statsinfo を実行ファイル単体で起動することはできません。

$ pg_ctl start [OPTIONS]

終了も同様に、PostgresSQL サーバの終了に連動します。 smart 以外の終了モード (fast, immediate) ではエラーが出力される場合がありますが、正常な動作です。

$ pg_ctl stop -m smart [OPTIONS]

スナップショットの取得・削除

自動取得

スナップショットを一定の時間間隔で定期的に取得します。 postgresql.confに以下の設定を記述することで自動取得を実行できます。

例: スナップショットの取得間隔を30分に設定する。

pg_statsinfo.snapshot_interval = 30min 

手動取得

任意のタイミングでスナップショットを取得する場合は、監視対象のインスタンスが存在するDBクラスタのpostgresデータベースに対し、関数 statsinfo.snapshot(text DEFAULT NULL) を実行して下さい。
引数でスナップショット取得理由をコメントとして記録できます。

例: 手動でスナップショットを取得します。コメントとして文字列 'comment' を付与します。

$ psql -d postgres -c "SELECT statsinfo.snapshot('comment')"

自動削除

リポジトリDBの自動メンテナンス ( デフォルト ON ) を行うと、1日1回任意の時刻に古いスナップショットを自動的に削除することができます。 自動メンテナンスの実行時刻、スナップショットの保持期間は、設定パラメータで指定します。

postgresql.confに以下の設定を記述することで自動削除を実行できます。

例:毎日0時2分に7日の保持期間を過ぎたスナップショットを自動削除する。

pg_statsinfo.enable_maintenance = 'on'          # リポジトリDBの自動メンテナンス設定
pg_statsinfo.maintenance_time = '00:02:00'      # リポジトリDBの自動メンテナンス実行時刻設定
pg_statsinfo.repository_keepday = 7 	        # スナップショットの保持期間設定

(注1)自動メンテナンスを行わない場合、スナップショットは手動で削除しない限りリポジトリDBに残り続けます。 不要となった古いスナップショットは定期的に削除して下さい。

(注2)複数監視対象インスタンス‐単一リポジトリDBの構成で、各監視対象インスタンスのスナップショットの保持期間を互いに異なる期間に設定した場合、自動メンテナンス実行時には最も短く設定した期間のスナップショットが保持されます。 例えば、各監視対象インスタンスの postgresql.conf に以下のようにスナップショット保持期間 (pg_statsinfo.repository_keepday) の設定を記述した場合、自動メンテナンス実行時のスナップショット保持期間として反映されるのは監視対象インスタンス3の設定となります。

<監視対象インスタンス1>
pg_statsinfo.enable_maintenance = 'on' 
pg_statsinfo.repository_keepday = 7
<監視対象インスタンス2>
pg_statsinfo.enable_maintenance = 'on' 
pg_statsinfo.repository_keepday = 5
<監視対象インスタンス3>
pg_statsinfo.enable_maintenance = 'on' 
pg_statsinfo.repository_keepday = 3

手動削除

スナップショットの手動削除は監視対象インスタンスに対して、関数 statsinfo.maintenance(timestamptz) を実行して下さい。 引数で指定した時刻より古いスナップショットが削除されます。

例: 取得日時が 2011-02-01 07:00:00 より古いスナップショットを削除します。

$ psql -d postgres -c "SELECT statsinfo.maintenance('2010-02-01 07:00:00'::timestamptz);"

ログファイルの出力について

pg_statsinfo にはログを加工する機能があります。 以下では、出力されるログの種類を説明します。

postgresql.log

本ログは、pg_statsinfo がフィルタリングして出力した最新のログです。 フィルタリングレベル(pg_statsinfo.textlog_min_messages)を指定していない場合は、warning 以上のログレベルのみが出力されます。 pg_statsinfo が出力するログは、1日間隔でログローテートされますので、本ログは本日の最新のログということになります。 postgresql.conf に pg_statsinfo.textlog_filename を指定していた場合は、postgresql.log ではなく、指定されたファイル名で出力されます。

postgresql-2011-12-01_000000.log

本ログは、上記で述べた postgresq.log がローテートされたログになります。 起動時には、下記で述べる csv ログファイルと拡張子以外同一の名前の空のファイルが作成されますが、正常な動作です。

postgresql-2011-12-01_000000.csv

本ログは、PostgreSQL が出力する生のログになります。 pg_statsinfo は、本ログに出力された情報を基に、加工したログ出力を行いますが、 本ログに関しては全く加工を行いません。

アラート機能の使い方

アラート機能は初回スナップショットが完了した監視対象インスタンスに対して自動的に有効になります。
初期状態のアラート条件(閾値)は、アラート設定テーブルのデフォルト値が適用されます。

アラート設定の変更

監視対象インスタンス毎に、アラート機能の有効/無効およびアラート条件(閾値)を変更することができます。
アラート設定を変更するには、アラート設定テーブルから変更したいアラート項目のカラムの値を変更してください。
なお、アラート設定の変更を行うには、アラート設定を変更する監視対象インスタンスの初回スナップショットが完了している必要があることに注意してください。

アラート設定を変更する際、監視対象インスタンスを選択するためにカラム「instid」を使用します。
当該カラムには監視対象インスタンス毎に割り当てられた識別子 (監視対象インスタンスID) を指定します。
監視対象インスタンスIDは、簡易レポート機能のスナップショットサイズ表示モードを実行することで確認できます。

設定例: 秒間ロールバック数の閾値を「3000」に変更する場合
# UPDATE statsrepo.alert SET commit_tps = 3000 WHERE instid = <変更対象の監視対象インスタンスID>
設定例: アラート機能を無効にする場合
# UPDATE statsrepo.alert SET enable_alert = false WHERE instid = <変更対象の監視対象インスタンスID>

アラート設定テーブル

アラート設定テーブルとはアラート機能の有効/無効および各種アラート条件(閾値)の設定を保存するテーブルです。
アラート設定テーブルは、リポジトリDBの「statsrepo.alert」テーブルです。
アラート設定テーブルのスキーマ構成は以下のとおりです。

カラム名 データ型 デフォルト値 説明
instid bigint 監視対象インスタンスID
rollback_tps bigint 100 秒間のロールバック数
commit_tps bigint 1000 秒間のコミット数
garbage_size bigint 20000 監視インスタンス中の不要領域のサイズ(MB)
garbage_percent integer 30 監視インスタンスに占める不要領域の割合(%)
garbage_percent_table integer 30 各テーブルに占める不要領域の割合(%)
response_avg bigint 10 クエリの平均レスポンス時間(秒)
response_worst bigint 60 クエリの最長レスポンス時間(秒)
enable_alert boolean true アラート対象判定フラグ(TRUE: 有効、FALSE: 無効)

アラート設定テーブルのタプルを削除しないでください。
タプルを削除した場合、削除したタプルに該当する監視対象インスタンスの設定ができなくなります。

簡易レポート機能の使い方

レポート生成モード

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

以下にコマンド例を示します。
以下のコマンド例では、ホスト名 localhost 上のポート 5432 で稼動しているリポジトリDBに対して、postgres データベースに、postgres ユーザで接続し、以下の条件のレポートを出力します。

$ pg_statsinfo -h localhost -d postgres -p 5432 -U postgres -r All
-r, --report=REPORTID
レポート種別IDを指定します。
レポート種別IDに指定できる値は以下のとおりです。
レポート種別IDとレポートの内容についての対応はこちらをご覧ください。
  • Summary
  • DatabaseStatistics
  • InstanceActivity
  • OSResourceUsage
  • DiskUsage
  • LongTransactions
  • NotableTables
  • CheckpointActivity
  • AutovacuumActivity
  • QueryActivity
  • LockActivity
  • ReplicationActivity
  • SettingParameters
  • SchemaInformation
  • Profiles
  • All
レポート種別IDは、頭文字からの最短一致での指定を許容し、大文字と小文字を区別しません。
-i, --instid=INSTANCEID
レポート対象とする監視対象インスタンスの識別子を指定します。
本オプションは省略可能です。省略時は全ての監視対象インスタンスがレポート対象です。
-b, --beginid=SNAPID
レポート範囲の開始点とするスナップショットをスナップショットIDで指定します。
本オプションは省略可能です。省略時は最初のスナップショットを開始点とします。
本オプションと「--B, --begindate」または「-E, --enddate」を同時に指定することはできません。
-e, --endid=SNAPID
レポート範囲の終了点とするスナップショットをスナップショットIDで指定します。
本オプションは省略可能です。省略時は最後のスナップショットを終了点とします。
本オプションと「--B, --begindate」または「-E, --enddate」を同時に指定することはできません。
-B, --begindate=DATE
レポート範囲の開始点とするスナップショットを日時 (YYYY-MM-DD HH:MI:SS形式) を指定します。
本オプションは省略可能です。省略時は最初のスナップショットを開始点とします。
本オプションと「--b, --beginid」または「-e, --endid」を同時に指定することはできません。
-E, --enddate=DATE
レポート範囲の終了点とするスナップショットを日時 (YYYY-MM-DD HH:MI:SS形式) を指定します。
本オプションは省略可能です。省略時は最後のスナップショットを終了点とします。
本オプションと「--b, --beginid」または「-e, --endid」を同時に指定することはできません。
-o, --output=FILENAME
レポートの出力先ファイル名を指定します。
本オプションは省略可能です。省略時はレポートを標準出力に出力します。
指定したファイルが既に存在する場合は上書きします。

スナップショット一覧表示モード

$ pg_statsinfo -l [-i INSTANCEID] [connection-options]

以下にコマンド例を示します。
以下のコマンド例では、ホスト名 localhost 上のポート 5432 で稼動しているリポジトリDBに対して、postgres データベースに、postgres ユーザで接続し、当該リポジトリDBに蓄積されているスナップショットの一覧を表示します。

$ pg_statsinfo -h localhost -d postgres -p 5432 -U postgres -l
-l, --list
本オプションが指定された場合はスナップショット一覧の表示を行います。
-i, --instid=INSTANCEID
スナップショット一覧を表示する監視対象インスタンスの識別子を指定します。
本オプションは省略可能です。省略時は全ての監視対象インスタンスのスナップショット一覧を表示します。

スナップショットサイズ表示モード

$ pg_statsinfo -s [connection-options]

以下にコマンド例を示します。
以下のコマンド例では、ホスト名 localhost 上のポート 5432 で稼動しているリポジトリDBに対して、postgres データベースに、postgres ユーザで接続し、当該リポジトリDBに蓄積されているスナップショットのサイズを表示します。

$ pg_statsinfo -h localhost -d postgres -p 5432 -U postgres -s
-s, --size
本オプションが指定された場合はスナップショットサイズの表示を行います。

スナップショット取得モード

$ pg_statsinfo -S COMMENT [connection-options]

以下にコマンド例を示します。
以下のコマンド例では、ホスト名 localhost 上のポート 5432 で稼動している監視対象インスタンスに対して、postgres データベースに、postgres ユーザで接続し、'COMMENT'をコメントとして付与したスナップショットを取得します。

$ pg_statsinfo -h localhost -d postgres -p 5432 -U postgres -S 'COMMENT'
-S, --snapshot=COMMENT
本オプションが指定された場合はスナップショットの取得を行います。
オプション引数にはスナップショットに付与するコメントを指定します。

スナップショット削除モード

$ pg_statsinfo -D SNAPID [connection-options]

以下にコマンド例を示します。
以下のコマンド例では、ホスト名 localhost 上のポート 5432 で稼動しているリポジトリDBに対して、postgres データベースに、postgres ユーザで接続し、当該リポジトリDBに蓄積されているスナップショットIDが 123 のスナップショットを削除します。

$ pg_statsinfo -h localhost -d postgres -p 5432 -U postgres -D 123
-D, --delete=SNAPID
本オプションが指定された場合はスナップショットの削除を行います。
オプション引数には削除対象のスナップショットのスナップショットIDを指定します。

共通オプション (connection-options)

データベース接続に関するオプションです。
スナップショット取得モードを実行する場合は監視対象インスタンス、それ以外はリポジトリDBへの接続情報を指定します。

-d, --dbname=DBNAME
接続するデータベース名を指定します。
本オプションが指定されていない場合は環境変数「PGDATABASE」の値が使用されます。
環境変数も設定されていない場合は、接続時に指定したユーザ名が使用されます。
-h, --host=HOSTNAME
サーバが稼働しているマシンのホスト名を指定します。
ホスト名がスラッシュから始まる場合、Unixドメインソケット用のディレクトリとして使用されます。
-p, --port=PORT
サーバが接続を監視するTCPポートもしくはUnixドメインソケットファイルの拡張子を指定します。
-U, --username=USERNAME
接続するユーザ名を指定します。
-w, --no-password
パスワードの入力を促しません。サーバがパスワード認証を必要とし、かつ、.pgpassファイルなどの他の方法が利用できない場合、接続試行は失敗します。
-W, --password
データベースに接続する前に、強制的にパスワード入力を促します。

設定ファイル

pg_statsinfo は設定ファイルとして監視対象インスタンスの postgresql.conf を使用します。 設定ファイルに記載した内容は、インスタンス起動時とリロード時 (pg_ctl reload) に読み込まれ、動作に反映されます。

必須設定項目

pg_statsinfo を利用するために必須のパラメータは以下です。 パラメータを後から変更するためには PostgreSQL インスタンスの再起動が必要な設定もあります。

設定項目 設定値 説明
shared_preload_libraries 'pg_statsinfo' 事前読込み用のライブラリの指定。 pg_stat_statements を併用する場合は 'pg_statsinfo, pg_stat_statements' のようにカンマ区切りで指定します。
log_filename 'postgresql-%Y-%m-%d_%H%M%S.log' CSVログおよびテキストログのファイル名。デフォルトから変更する場合でも、%Y, %m, %d, %H, %M, %S がこの順に全て表れる形式でなければなりません。
track_counts on データベースの活動に関する統計情報の収集設定
track_activities on セッションで実行中のコマンドに関する情報の収集設
log_min_messages debug5 ~ log ログへ出力するメッセージレベル。 'log', pg_statsinfo.syslog_min_messages, pg_statsinfo.textlog_min_messages のいずれの設定よりも、より多くを出力するレベルを設定する必要があります。
log_timezone unknown, gmt, utc これ以外の値には対応していません。
log_destination 'csvlog' 必須 / 'syslog', 'eventlog' を追加可能 他の値であっても、pg_statsinfo 起動時に強制的に 'csvlog' が追加され、'stderr' は除去されます。
logging_collector on 他の値であっても、pg_statsinfo 起動時に強制的にこの値に設定されます。

追加設定項目

pg_statsinfo を利用するために確認が推奨されるパラメータは以下です。 パラメータを後から変更するためには、再読み込み (pg_ctl reload) のみが必要です。

また、PostgreSQL 8.3 で利用する場合には、custom_variable_classes の設定値は 'statsinfo'、独自パラメータ 'pg_statsinfo.*' は 'statsinfo.*' と読み替えてください。

設定項目 デフォルト値 説明
track_functions none 関数の呼び出しに関する統計情報の収集設定。 統計情報を収集するためには pl または all を設定します。
log_checkpoints off チェックポイント状況のサーバログ出力。on を推奨します。
log_autovacuum_min_duration -1 自動バキューム状況のサーバログ出力。0 ~ 1min を推奨します。
log_directory 'pg_log' CSVログおよびテキストログの出力先ディレクトリ
log_rotation_age 1d ログローテート設定 (期間によるログファイル切替)
log_rotation_size 10MB ログローテート設定 (容量によるログファイル切替)
syslog_facility 'LOCAL0' syslog の facility 指定
syslog_ident 'postgres' syslog の indent文字列指定
custom_variable_classes (空文字) 独自変数クラス名の設定。
"pg_statsinfo." から始まるパラメータを設定するためには、'pg_statsinfo' を指定する必要があります。
pg_statsinfo.textlog_min_messages warning テキストログへ出力する最小メッセージレベル (*1)
pg_statsinfo.syslog_min_messages disable syslog へ出力する最小メッセージレベル (*1)。
pg_statsinfo.textlog_filename 'postgresql.log' 最新のテキストログファイル名。空文字はエラー。
pg_statsinfo.textlog_line_prefix '%t %p ' テキストログの各行の先頭に追加される書式 (*2)
pg_statsinfo.syslog_line_prefix '%t %p ' syslog の各行の先頭に追加される書式 (*2). syslog がデフォルトで付与する時刻とプロセスIDは、pg_statsinfo のものに置き換わってしまうため、元の値を記録するために %t や %p が必要なことに注意してください。
pg_statsinfo.textlog_permission 0600 テキストログファイルのパーミッション指定
pg_statsinfo.sampling_interval 5s サンプリングの実行間隔 (*3)
pg_statsinfo.snapshot_interval 5min スナップショットの取得間隔 (*3)
pg_statsinfo.excluded_dbnames 'template0, template1' 監視対象から除外するデータベース名
pg_statsinfo.excluded_schemas 'pg_catalog, pg_toast, information_schema' 監視対象から除外するスキーマ名
pg_statsinfo.repository_server 'dbname=postgres' リポジトリDBへの接続文字列 (*4)。パスワードの入力待ちは避ける。
pg_statsinfo.adjust_log_level off サーバログのメッセージレベル変更設定
pg_statsinfo.adjust_log_info - メッセージレベルを INFO に変更したい SQLSTATE 指定 (*5)
pg_statsinfo.adjust_log_notice - メッセージレベルを NOTICE に変更したい SQLSTATE 指定 (*5)
pg_statsinfo.adjust_log_warning - メッセージレベルを WARNING に変更したい SQLSTATE 指定 (*5)
pg_statsinfo.adjust_log_error - メッセージレベルを ERROR に変更したい SQLSTATE 指定 (*5)
pg_statsinfo.adjust_log_log - メッセージレベルを LOG に変更したい SQLSTATE 指定 (*5)
pg_statsinfo.adjust_log_fatal - メッセージレベルを FATAL に変更したい SQLSTATE 指定 (*5)
pg_statsinfo.enable_maintenance on リポジトリDBの自動メンテナンス設定
pg_statsinfo.maintenance_time '00:02:00' リポジトリDBの自動メンテナンス実行時刻設定
pg_statsinfo.repository_keepday 7 スナップショットの保持期間設定
pg_statsinfo.long_lock_threashold 30s ロック競合情報の収集対象とする条件(閾値)。スナップショットの時点で発生しているロック競合の内、ロック待ちの経過時間(秒)が閾値を越えているものが収集対象となります。
*1 : メッセージレベル
以下の値が指定でき、そのレベルと、それより上位のレベルのメッセージが記録されます。 全く記録しない場合には disable を指定します。 独自に disable, alert レベルが追加されていることと、debug を区別しないことを除き、log_min_messages と同じ優先順位です。
disable > alert > panic > fatal > log > error > warning > notice > info > debug
*2 : 書式指定
設定パラメータ log_line_prefix と同じ形式で指定します。 log_line_prefix の値そのものは無視されることに注意して下さい。
*3 : 時間指定
単位として d(日)、h(時)、min(分)、s(秒) を指定できます。 指定無しの場合は秒単位とみなします。
PostgreSQL 8.3時間を指定する場合は、秒数のみしか指定できませんのでご注意ください。(例:3minとしたい場合は180と指定する)
*4 : 接続文字列
例えば 'hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres' といったlibpq形式の接続情報文字列です。 詳細はデータベース接続制御関数のPQconnectdbを参照して下さい。 この他、libpq が使用する環境変数の影響を受けますので、「環境変数」も参照して下さい。
リポジトリDBに接続する際にパスワードの入力待ちにならないようにする必要があります。 パスワード認証が必要な場合には、PostgreSQL インスタンス起動ユーザに .pgpass を設定し、パスワードの入力を自動化してください。 なお、.pgpassを用いる場合、リポジトリDBへの接続文字列のホスト名は"host=xxxx"で指定して下さい。
*5 : SQLSTATE 指定
複数のログのメッセージレベルを変更したい場合には、SQLSTATE をカンマ区切りで指定します。
例えば、SQLSTATE が '42P01' と '42P02' のメッセージレベルを INFO に変更したい場合、pg_statsinfo.adjust_log_info = '42P01,42P02' と指定します。

設定値のリロード

PostgreSQLサーバを終了させることなく postgresql.conf の設定を反映させたい場合は、PostgresSQLのリロードコマンドを実行します。

$ pg_ctl reload

運用上必要となる作業

ユーザが行う必要のある操作と運用上必要となる作業について説明します。

サーバログの削除

CSVログ、テキストログは手動で削除しない限り残り続けます。 不要となった古いログファイルは定期的に削除して下さい。

ログローテート

任意のタイミングでログをローテートさせたい場合は、監視対象インスタンスにて以下を実行して下さい。

$ psql -d postgres -c "SELECT pg_rotate_logfile()"

異常終了時の対処

pg_statsinfo のみが異常終了しても、PostgreSQL インスタンスには影響はありません。 しかし、pg_statsinfo の機能は停止したままになってしまうため、PostgreSQL インスタンスを再起動するか、以下のコマンドで pg_statsinfo を再起動して下さい。

$ psql -d postgres -c "SELECT statsinfo.restart()"

ただし、pg_statsinfo のみが異常終了から再起動までの間にサーバログがローテートされていた場合、最新のサーバログを除いては解析されません。 pg_statsinfo の停止中に出力されたサーバログについては、直接CSVログファイルを閲覧して下さい。

アンインストール

pg_statsinfo をアンインストールするには、PostgreSQL インスタンスの再起動が必要です。 postgresql.conf の shared_preload_libraries から pg_statsinfo を取り除き、全ての pg_statsinfo.* パラメータを削除した後に、PostgreSQL を再起動して下さい。

その後、監視対象インスタンスにインストールされた pg_statsinfo が使用するオブジェクトを削除します。監視対象インスタンスの postgres データベースに対し $PGSHARE/contrib/uninstall_pg_statsinfo.sql を実行して下さい。

$ psql -d postgres -f $PGSHARE/contrib/uninstall_pg_statsinfo.sql 

取得済みのスナップショットも不要な場合には、リポジトリDBに接続し、$PGSHARE/contrib/uninstall_pg_statsrepo.sql を実行して下さい。 この操作ではリポジトリDBに蓄積した全てのスナップショットを削除しますので、リポジトリDBを共有している場合には特に注意して下さい。

$ psql -d <repository> -f $PGSHARE/contrib/uninstall_pg_statsrepo.sql 

使用上の注意と制約

pg_statsinfo を使用する際には、以下の使用上の注意と制約があります。

監視対象インスタンスではエンコーディングと lc_messages の統一が必要
pg_statsinfo は PostgreSQL がサポートするエンコーディングやメッセージ・ロケールに対応していますが、混在させることはできません。 PostgreSQL が出力するサーバログにログが混在してしまうため、解析に失敗します。
log_filename の制限
pg_statsinfo では、以下を満たす設定値を期待しています。
  • サーバが再起動するたびに、新しいサーバログに切り替わる。(秒精度など、十分高い時間精度のファイル名が利用されている)
  • サーバログの名前は新旧に応じて昇順の名前になる。
そのため、設定値を変更する場合には、変更前の全てのログファイルを削除してください。 ログファイルの新旧を名前に基づいて判断しているため、ソート順が変化するような設定変更時に動作不良を起こす可能性があります。
pg_statsinfo.textlog_filename の制限
固定ファイル名のテキストログは必須です。使わない設定にはできません。
log_timezone は unknown, gmt, utc のみ
これら以外のタイムゾーンには対応していません。 PostgreSQL はタイムゾーンを独自に管理しており、外部プログラムからは利用できないためです。
fast 及び immediate シャットダウン時のエラーメッセージ
PostgreSQL サーバのシャットダウンの際、停止モードの指定が smart モード以外の場合は接続エラーが発生する場合があります。 これは pg_statsinfo のデータベース切断を待たずにサーバがシャットダウンされるためです。 終了時にエラーメッセージが出力されても問題はありません。 また、smart シャットダウンであればエラーは発生しません。
シャットダウン・チェックポイントは収集できない
シャットダウン時のチェックポイントログはリポジトリDBに保存されません。 同居構成の場合には既にリポジトリDBが終了してしまっているためです。
リポジトリDBの自動メンテナンスの制限
複数の監視対象インスタンスのスナップショットを同一のリポジトリDBに蓄積する構成では、自動メンテナンスを実行する pg_statsinfo を1つにする必要があります。 これは、インスタンス毎のメンテナンス設定で自動メンテナンスがそれぞれ実行されてしまうためです。
監視対象インスタンス毎にリポジトリDBを用意する構成では、1つにする必要はありません。
スナップショット取得とテーブル削除の同時実行
スナップショット取得とテーブル削除が同時に要求されると、タイミングによってはスナップショット取得でエラーが発生する可能性があります。 エラーが発生した場合、スナップショット取得はリトライされます。
複数の監視インスタンスの設定について
各インスタンス毎に、データベースシステム識別子 or OSホスト名 or Port番号のいずれかが異なっていない場合、複数インスタンスとして認識できません。

よくあるQ&A

Q1. pg_statsinfo が正常に動作しているかの確認方法を教えてください。

pg_statsinfo が取得した情報は、statsrepo スキーマに格納されています。 コマンドライン上で以下の SQL を実行し、確認することができます。

$psql -d postgres -c "SELECT statsinfo.snapshot('test')"
$psql -d postgres -c "SELECT * FROM statsrepo.snapshot WHERE COMMENT = 'test'"

2回目のコマンドで、テスト実行した snapshot の情報が確認されれば、正常に動作しています。

Q2. 取得したスナップショットはどのように使えばいいの?

pg_statsinfo の統計情報の取得機能は、その時点の統計情報をスナップショットとして定期的に取得する機能のみになります。 取得した統計情報から、有益な情報を見たい場合は 簡易レポート機能 を利用するか、pg_repoter をお使いください。

Q3. 自動メンテナンス機能が動作しません。

自動メンテナンスの設定が有効になっていないか、リポジトリDBの statsrepo スキーマの版がリポジトリDBの PostgreSQL のバージョンと一致していないことが考えれられます。 以下の事項を点検してください。

自動メンテナンスの設定が、postgresql.conf に適切に記述されているか
postgresql.conf に以下の設定項目が存在するか確認してください。
  • pg_statsinfo.enable_maintenance = 'on'
リポジトリDBの statsrepo スキーマのバージョンが正しいか
リポジトリDBの statsrepo スキーマに del_snapshot2 関数が存在するか以下の SQL をリポジトリDBで実行して確認してください。
# SELECT n.nspname, p.proname FROM pg_proc p LEFT JOIN pg_namespace n ON p.pronamespace = n.oid WHERE p.proname = 'del_snapshot2' AND n.nspname = 'statsrepo';
del_snapshot2 関数が存在しない場合は PostgreSQL 8.3用の statsrepo スキーマです。存在する場合は PostgreSQL 8.4以降用の statsrepo スキーマです。
statsrepo スキーマのバージョンがリポジトリDBの PostgreSQL のバージョンと一致していない場合は、現在の statsrepo スキーマを破棄し、その後 PostgreSQL を再起動してください。

Q4. log_statement=all と設定して運用した場合、pg_statsinfo が実行するSQLが大量に出力されてしまいログの可視性が下がります。pg_statsinfo のログを出力しない方法は?

下記の運用手順によって対処が可能です。

1. 監視対象DBに pg_statsinfo 専用のユーザを作成する
# CREATE ROLE statsinfo WITH LOGIN SUPERUSER;

2. pg_statsinfo 専用のユーザに log_statement = 'none' を設定する
# ALTER ROLE statsinfo SET log_statement = 'none'

3. 環境変数 PGUSER に pg_statsinfo 専用のユーザを設定する
$ export PGUSER=statsinfo

4. データベースを再起動する
$ pg_ctl restart

Q5. アラート機能が正常に動作しているかの確認方法を教えてください。

アラート機能を有効に設定、かつ秒間コミット数のアラート条件(閾値)を「0」に設定した状態でスナップショットを取得してください。
スナップショットの取得時に、サーバログにアラートメッセージが出力されていればアラート機能が正常に動作しています。

アラート機能を有効、かつ秒間のコミット数のアラート条件(閾値)を「0」に設定するには以下の SQL をリポジトリDBに対して実行します。

# UPDATE statsrepo.alert SET enable_alert = true, commit_tps = 0;

アラート機能が正常に動作したことを確認した後は、秒間のコミット数のアラート条件(閾値)を元に戻してください。

Q6. 古いバージョン(2.x)からアップグレードする方法を教えてください。

古いバージョンをアンインストールした後、新しいバージョンをインストールしてください。
また、古いバージョンで取得済みのスナップショットは新しいバージョンでは利用できません。
アンインストール の手順に従い、リポジトリDBのスナップショットを全て削除してください。

上記の操作を行った後、監視対象インスタンスを再起動してください。

Q7. 簡易レポート機能のレポート生成モードを実行したところ、レポートが表示されません。

リポジトリDBに格納されているスナップショットの件数が2件未満である可能性があります。
レポートの作成には2件以上のスナップショットが必要となりますので、スナップショットの取得が2回実行されるのを待ってから簡易レポート機能のレポート生成モードを実行してください。

Q8. 簡易レポート機能のレポート生成モードを実行したところ、一部のレポート項目が表示されません。

Query Activity (Functions)
設定ファイルの「track_functions」が「none」に設定されている可能性があります。
この場合、当該レポート項目に必要な情報がスナップショットに含まれません。
当該レポート項目を表示するには「track_functions」を「none」以外に設定してください。
Query Activity (Statements)
pg_stat_statements がインストールされていない可能性があります。
この場合、当該レポート項目に必要な情報がスナップショットに含まれません。
当該レポート項目を表示するには pg_stat_statements をインストールしてください。
Autovacuum Activity
設定ファイルの「log_autovacuum_min_duration」が「-1」に設定されている可能性があります。
この場合、当該レポート項目に必要な情報がスナップショットに含まれません。
当該レポート項目を表示するには「log_autovacuum_min_duration」を「-1」以外に設定してください。
Checkpoint Activity
設定ファイルの「log_checkpoints」が「off」に設定されている可能性があります。
この場合、当該レポート項目に必要な情報がスナップショットに含まれません。
当該レポート項目を表示するには「log_checkpoints」を「on」に設定してください。
Long Transactions
レポート対象となる該当が存在しない可能性があります。
Notable Tables
レポート対象となる該当が存在しない可能性があります。
Lock Activity
レポート対象となる該当が存在しない可能性があります。
Replication Activity
レポート対象となる該当が存在しない可能性があります。
Schema Information
一部のデータベースのスキーマ情報が含まれてない場合は、該当のデータベースに接続できる設定になっているか クライアント認証 などを確認してください。

詳細情報

より高度な利用方法や、内部構成について説明します。

複数の監視対象インスタンス

複数の監視対象インスタンスが存在する場合の構成として、監視対象インスタンス毎にリポジトリDBを用意して各個にスナップショットを蓄積する構成と、各監視対象インスタンスで共通のリポジトリDBを用意してスナップショットを蓄積する構成があります。
ここでは、後者の単一リポジトリに複数の監視対象インスタンスのスナップショットを蓄積する構成について説明します。

設定手順

各監視対象インスタンスの設定ファイルに、同一のリポジトリDBを参照する内容で「pg_statsinfo.repository_server」を設定します。
以下に設定例を示します。

pg_statsinfo.repository_server = 'hostaddr=192.168.0.32 port=5432 user=postgres dbname=postgres'

リポジトリDBは各監視対象インスタンスから上記で設定したデータベースにパスワード入力なしに接続できる必要があります。
各監視対象インスタンスからリポジトリDBにパスワード入力なしに接続できるよう クライアント認証 を設定してください。

注意事項

ウォームスタンバイ

構成例として、ウォームスタンバイでの構成を説明します。 ウォームスタンバイ構成で pg_statsinfo を利用する場合、大きく分けて2つの構成があります。 詳細は "pg_statsinfo: warm-standby" を参照してください。

  1. 稼動系、待機系と独立したインスタンスにリポジトリDBを配置
  2. 稼動系、待機系それぞれのインスタンスにリポジトリDBを配置

SystemTap 連携

Linux 系 OS には、様々なトレーシング/プロファイリングツールが備わっています。 その内の1つである SystemTap は、Solaris や FreeBSD などで利用可能な Dtrace に類似したプロダクトであり、カーネル内部のトレーシングやプロファイリングのほかに、 ユーザアプリケーションの情報取得 (プローブ定義) も可能なツールです。 PostgreSQL には、多くの標準的なプローブがソースコード内で提供されており、SystemTap を利用してこれらのプローブから収集されるプロファイリング情報を取得することができます。 pg_statsinfo は、この SystemTap が収集するプロファイリング情報をスナップショットとしてリポジトリDBに蓄積します。

前提環境

  1. OS は、RHEL6、Fedora13 以降が必要になります。
    これらは、PostgreSQL のプロファイリングが実施可能な SystemTap のバージョンをデフォルトで持つディストリビューションです。
  2. systemtap、systemtap-runtime、systemtap-sdt-devel パッケージ(RPM) がインストール済みである必要があります。
  3. PostgreSQL は、--enable-debug、--enable-dtrace の configure オプションを有効にしてビルドされている必要があります。

実行手順

SystemTap を実行するユーザは、postgres を起動するユーザ(典型的には postgres ユーザ)である必要があります。 まず、このユーザを stapdev グループに所属させる必要があります。

$ usermod -g stapdev <username>

PostgreSQL のプロファイリング情報収集を行うには、専用のスクリプトを使用します。スクリプトを pg_statsinfo_profile.stp に示します。 スクリプトを監視対象インスタンスの任意の場所に配置し、SystemTap で実行して下さい。

$ stap -m statsinfo_prof pg_statsinfo_profile.stp

SystemTap が収集するプロファイリング情報は、スナップショット取得と同じタイミングで取得され、リポジトリDBに蓄積されます。

制約

本機能は、性能への影響、プロダクトの成熟度合いなどを加味し、Experimental (実験的扱いの機能) の位置づけとなります。 つまり、商用などでは使用をまだ推奨せず、あくまで検証環境などでの使用を前提としています。

内部構成

pg_statsinfo はPostgreSQLのサーバサイドで動作する pg_statsinfo ライブラリと、クライアントとして動作する pg_statsinfo デーモンの2つのモジュールで構成されています。 ライブラリはロード直後に呼ばれるフック関数からデーモンを起動するため、ユーザがデーモンを明示的に起動することはありません。 詳細は "pg_statsinfo: internal" を参照してください。

関連項目

外部サイト

PGcon 2010: pg_statsinfo

PostgreSQLドキュメント

pg_ctl, psql, サーバの構成, 統計情報コレクタ, システムカタログ, pg_stat_statements, pg_reporter