名前
pg_statsinfo -- PostgreSQL サーバの性能情報を定期的に収集し、蓄積します。
概要
pg_statsinfo は、PostgreSQL サーバのインスタンス1つを監視対象とし、その統計情報や活動状況を定期的に収集し、スナップショットとして蓄積します。 蓄積先のリポジトリ・データベースは監視対象と同じでも、異なるサーバでも構いません。 さらに、PostgreSQLの出力する CSV 形式のサーバログを解析し、性能情報を抽出すると共に、テキスト形式ログ及び syslog への振り分けを行います。
起動や終了、パラメータの設定は PostgreSQL と密に連携しており、手間をかけずに導入できます。 また、pg_statsinfo で収集した情報は pg_reporter でグライフィカルなレポートして閲覧できます。
pg_statsinfo の動作概要のイメージ図を以下に示します。 監視の対象は PostgreSQL インスタンス (サーバ) 単位です。
統計情報スナップショット
統計情報スナップショットは定期的に取得され、リポジトリ・データベース (以下 リポジトリ) に保存します。 リポジトリは監視対象インスタンスと同一インスタンスのデータベースでも、別インスタンスでも構いません。 また、1つのリポジトリに対して複数の監視対象からのスナップショットを格納することもできます。
- 定期的 (デフォルト 5分間隔) またはユーザ操作による任意のタイミングでのスナップショット取得。
- スナップショット取得直後にイベントハンドラ関数を呼び出します。任意のイベントハンドラ関数を定義でき、過去のスナップショットと比較して異常値があればアラート・メッセージとして出力できます。
スナップショットとして以下の統計情報を収集します。
- 統計情報コレクタが収集する全ての情報。挿入 / 更新 / 削除行数やバッファアクセス回数。
- テーブルスペース、pg_xlog 領域、アーカイブログ領域のディスク使用量。
- ロングトランザクション化しているクエリ。
- プロセスの状況。処理中、ロック待ち、トランザクション中の待機、アイドルのそれぞれで集計します。
- チェックポイントや自動バキュームの経過時間やバッファアクセス回数。
- 実行回数、累積実行時間の多いSQLと関数。
- PostgreSQLの設定パラメータ。
スナップショットサイズは、DB内のオブジェクト数に依存しますが、概ね1回のスナップショットで1DBあたり600 - 800KBを消費します。 取得間隔が5分の場合、監視対象DB一つあたり1日で180 - 240MBをリポジトリに必要とします。
古いスナップショットは自動的には削除されないため、ユーザ操作で定期的に削除して下さい。
サーバログ処理機能
- メッセージレベル (エラー種別) に応じたサーバログの分配。CSVログ、テキストログ、syslog に個別で出力閾値を設定できます。
- 最新テキストログのファイル名を固定。常に同じ名前 (デフォルト $PGDATA/pg_log/postgresql.log) で最新のサーバログを閲覧でき、ログ監視ソフトウェアとの連携が容易になります。
- テキストログファイルのアクセス権の設定。テキストログのファイル権限を 600 以外にも設定できるため、運用が柔軟になります。
古いサーバログは自動的には削除されないため、ユーザ操作で定期的に削除して下さい。
その他の機能
- PostgreSQL と連動した起動 / 終了。 pg_statsinfo デーモンは、PostgreSQL インスタンスの起動 / 終了に連動します。 サーバ管理手順の変更は不要です。
- オンラインでの設定値変更。 pg_ctl reload と連動し、スナップショット間隔などの設定パラメータをオンラインで変更できます。
導入方法
pg_statsinfo の基本的な導入方法について説明します。
インストール
ソースコードからビルドするには、pgxs を使用します。 なお、pg_statsinfo の登録スクリプト (sql) を手動でインストールする必要はありません。 監視対象、リポジトリ共に、初回起動時に必要に応じて pg_statsinfo がスキーマを自動的にインストールします。
$ cd pg_statsinfo $ make USE_PGXS=1 $ su $ make USE_PGXS=1 install
注意: PostgreSQL9.0のRPMでは、各種ファイルのインストールディレクトリが変更されているため、pg_statsinfoのRPMもそれと同じ ディレクトリへ各ファイルをインストールするようになっています。例えば、実行ファイルは従来"/usr/bin"配下に配置されていましたが、 PostgreSQL9.0用のRPMでは"/usr/pgsql-9.0/bin"になっています。
次に、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
一方、Windows では上記の認証方式を利用できないため、ローカルホストからの接続に trust を設定します。 もし trust 認証がセキュリティ上問題であれば、他の 認証方式 や .pgpass ファイルを使い、接続時にパスワードを要求されないように設定して下さい。
# TYPE DATABASE USER CIDR-ADDRESS METHOD [for Windows] host all postgres 127.0.0.1/32 trust
最後に、監視対象の PostgreSQL インスタンスを停止した状態で、postgresql.conf に以下の設定を行います。 この設定では、スナップショットのリポジトリは同一インスタンスの postgres データベースになります。 これら以外の設定については、「設定ファイル」を参照して下さい。
log_checkpoints = on # チェックポイントを記録 log_autovacuum_min_duration = 0 # 自動バキュームを記録 shared_preload_libraries = 'pg_statsinfo' # 事前ロードを行う
なお、contrib/pg_statsinfo.sql、contrib/pg_statsrepo.sqlは自動的にインストールされるため、手動での実行は不要です。
起動と終了
起動は、単に PostgreSQL サーバの起動するだけです。 サーバ連動して pg_statsinfo も自動的に起動します。 pg_statsinfo を実行ファイル単体で起動することはできません。
$ pg_ctl start [OPTIONS]
終了も同様に、PostgresSQL サーバの終了に連動します。 smart 以外の終了モード (fast, immediate) ではエラーが出力される場合がありますが、正常な動作です。
$ pg_ctl stop -m smart [OPTIONS]
アンインストール
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
取得済みのスナップショットも不要な場合には、リポジトリに接続し、$PGSHARE/contrib/uninstall_pg_statsrepo.sql を実行して下さい。 この操作ではリポジトリに蓄積した全てのスナップショットを削除しますので、リポジトリを共有している場合には特に注意して下さい。
$ psql -d <repository> -f $PGSHARE/contrib/uninstall_pg_statsrepo.sql
使用方法
pg_statsinfo の各種設定と操作方法について説明します。
なお、PostgreSQL のバージョン 8.4 以上を使っている場合、監視対象インスタンスの postgres データベースに pg_stat_statements をインストールすることで、クエリの統計情報もスナップショットとして収集できるようになります。 利用する場合には、初回起動時に以下の手順で登録してください。
$ psql -d postgres -f $PGSHARE/contrib/pg_stat_statements.sql
設定ファイル
pg_statsinfo は設定ファイルとして監視対象インスタンスの postgresql.conf を使用します。 設定ファイルに記載した内容は、インスタンス起動時とリロード時 (pg_ctl reload) に読み込まれ、動作に反映されます。
pg_statsinfo を利用するために必須のパラメータは以下です。 パラメータを後から変更するためには PostgreSQL インスタンスの再起動が必要な設定もあります。
| 設定項目 | 設定値 | 説明 |
|---|---|---|
| shared_preload_libraries | 'pg_statsinfo' | 事前読込み用のライブラリの指定。 pg_stat_statements を併用する場合は 'pg_statsinfo, pg_stat_statements' のようにカンマ区切りで指定します。 |
| 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 で利用する場合には、独自パラメータ '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_filename | 'postgresql-%Y-%m-%d_%H%M%S.log' | CSVログおよびテキストログのファイル名。デフォルトから変更する場合でも、%Y, %m, %d, %H, %M, %S がこの順に全て表れる形式でなければなりません。 |
| 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)。Windows 環境ではイベントログに出力されます。 |
| 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.repository_server | 'dbname=postgres' | リポジトリへの接続文字列 (*4)。パスワードの入力待ちは避ける。 |
- *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(秒) を指定できます。 指定無しの場合は秒単位とみなします。
- *4 : 接続文字列
- 例えば 'hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres' といったlibpq形式の接続情報文字列です。 詳細はデータベース接続制御関数のPQconnectdbを参照して下さい。 この他、libpq が使用する環境変数の影響を受けますので、「環境変数」も参照して下さい。
- リポジトリに接続する際にパスワードの入力待ちにならないようにする必要があります。 パスワード認証が必要な場合には、PostgreSQL インスタンス起動ユーザに .pgpass を設定し、パスワードの入力を自動化してください。 なお、.pgpassを用いる場合、リポジトリへの接続文字列のホスト名は"host=xxxx"で指定して下さい。
運用上必要となる作業
ユーザが行う必要のある操作と運用上必要となる作業について説明します。
サーバログの削除
CSVログ、テキストログは手動で削除しない限り残り続けます。 不要となった古いログファイルは定期的に削除して下さい。
スナップショットの削除
スナップショットは手動で削除しない限りリポジトリに残り続けます。 不要となった古いスナップショットは定期的に削除して下さい。
スナップショットの削除はリポジトリに対して、関数 statsrepo.del_snapshot(timestamptz) を実行して下さい。 引数で指定した時刻より古いスナップショットが削除されます。
例: 取得日時が 2010-02-01 07:00:00 より古いスナップショットを削除します。
$ psql -d <repository> -c "SELECT statsrepo.del_snapshot('2010-02-01 07:00:00'::timestamptz);"
スナップショットの手動取得
任意のタイミングでスナップショットを取得する場合は、監視対象インスタンスのpostgresデータベースに対し、関数 statsinfo.snapshot(text DEFAULT NULL) を実行して下さい。 引数でスナップショット取得理由をコメントとして記録できます。
例: 手動でスナップショットを取得します。コメントとして文字列 'comment' を付与します。
$ psql -d postgres -c "SELECT statsinfo.snapshot('comment')"
設定値のリロード
PostgreSQLサーバを終了させることなく postgresql.conf の設定を反映させたい場合は、PostgresSQLのリロードコマンドを実行します。
$ pg_ctl reload
ログローテート
任意のタイミングでログをローテートさせたい場合は、監視対象インスタンスにて以下を実行して下さい。
$ 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 を使用する際には、以下の使用上の注意と制約があります。
- 監視対象インスタンスではエンコーディングと 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 シャットダウンであればエラーは発生しません。
- シャットダウン・チェックポイントは収集できない
- シャットダウン時のチェックポイントログはリポジトリに保存されません。 同居構成の場合には既にリポジトリが終了してしまっているためです。
詳細
より高度な利用方法や、内部構成について説明します。
アラート関数
新しいスナップショットを保存した直後、リポジトリにてアラート関数 statsrepo.alert() が呼ばれます。
この関数のプロトタイプは CREATE FUNCTION statsrepo.alert(snap_id bigint) RETURNS SETOF text です。
デフォルトではデータベースには登録されませんが、実用的なアラート関数の例が $PGSHARE/contrib/pg_statsrepo_alert.sql にインストールされています。
アラート関数はリポジトリに登録する必要があります。 この関数内でスナップショットの差分を計算し、異常値があればメッセージを text 型で返却します。 返却結果はメッセージレベル "ALERT" の専用メッセージとしてログに記録されます。 異常発生後できるだけ早く検知したい場合にはアラート関数を使用してください。 過去の性能や稼働状況は pg_reporter のグラフィカル・レポートでも閲覧できますが、リアルタイム監視には向きません。
statsrepo.alert (snap_id bigint) : setof text
- [引数] snap_id:
- 最新のスナップショットIDが渡されます。
- [返値] setof text:
- ALERT として出力したいメッセージを返却します。
簡単なサンプルを alert-sample.sql に示します。 付属の pg_statsrepo_alert.sql 関数はより高度なチェック機能を持ちます。 以下のパラメータをリポジトリ側のpostgresql.confに記述することで、アラートの閾値を調整できます。
| 変数名 | デフォルト | アラート判定基準 |
|---|---|---|
| statsrepo.alert_rollbacks_per_second | 100 | 秒間のロールバック数 |
| statsrepo.alert_transactions_per_second | 6000 | 秒間のコミット数 (tps) |
| statsrepo.alert_garbage_size | 20GB | 監視インスタンス中の不要領域のサイズ (byte) |
| statsrepo.alert_garbage_percent | 30 | 監視インスタンスに占める不要領域の割合 (%) |
| statsrepo.alert_garbage_percent_table | 30 | 各テーブルに占める不要領域の割合 (%) |
| statsrepo.alert_response_average | 10s | クエリの平均レスポンス時間 (秒) |
| statsrepo.alert_response_worst | 60s | クエリの最悪レスポンス時間 (秒) |
パラメータを設定する場合には、custom_variable_classes にも 'statsrepo' を追加する必要があります。
custom_variable_classes = 'statsrepo' statsrepo.alert_transactions_per_second = 5000
ウォームスタンバイ
構成例として、ウォームスタンバイでの構成を説明します。 ウォームスタンバイ構成で pg_statsinfo を利用する場合、大きく分けて2つの構成があります。 詳細は "pg_statsinfo: warm-standby" を参照してください。
- 稼動系、待機系と独立したインスタンスにリポジトリを配置
- 稼動系、待機系それぞれのインスタンスにリポジトリを配置
内部構成
pg_statsinfo はPostgreSQLのサーバサイドで動作する pg_statsinfo ライブラリと、クライアントとして動作する pg_statsinfo デーモンの2つのモジュールで構成されています。 ライブラリはロード直後に呼ばれるフック関数からデーモンを起動するため、ユーザがデーモンを明示的に起動することはありません。 詳細は "pg_statsinfo: internal" を参照してください。
動作環境
- PostgreSQLバージョン
- PostgreSQL 8.3, 8.4, 9.0
- OS
- RHEL 5.3, CentOS 5.3, Windows XP
- 必要接続数
- 監視対象 1接続 / リポジトリ 1接続 (同居構成の場合は合計2接続)