明日から使えるPostgreSQLの運用管理テクニック (監視編)

2013年9月14日OSSC Hokkaido 2013

日本PostgreSQLユーザ会笠原 辰仁

自己紹介● 笠原辰仁@kasa_zip● 2004年からPostgreSQLを触ってます● SIerでPostgreSQLの技術支援や研究開発● たまにユーザ会の勉強会などで講演したりなど

本日のお話● 運用管理の 監視 に特化した話です● 監視に重要となる考え方、観点とともに、

基本的な設定やテクニックを中心に紹介します– まずは「これだけはやっておこう」というアレコレ

● PostgreSQLを使う(使ってみたい)ことになったけど、よくわからなくて困っている方の手助けになれば幸いです

● バージョンに依存した話ではありません– 機能上、バージョンを意識した話は注釈を入れてい

ます

(ちょっと寄り道)最近のPostgreSQL

2011

2012

2013

9.09.1

9.22010

8系-2009

9.32013.9.9 に最新版となるVer 9.3.0 リリース！

非同期レプリケーションホットスタンバイ

SQL構文強化

同期レプリケーションUNLOGGED TABLESQL/MED

カスケードレプリケーションIndexOnlyScanスケーラビリティ向上

Viewの改良postgres_fdwJSON型用演算子Fast promote ..etc

運用管理には何がある？■ 監視 ・ レポート 　　死活監視 　　リソース監視・性能監視 　　統計情報確認 ■ サービス管理 　　停止 / 再起動 　　フェイルオーバ 　　プロモート ■ バックアップ/リストア 　　バックアップ 　　PITR

■ 性能維持 　　メンテナンスコマンド実施 ■ 性能分析 / トラブルシュート 　　クエリキャンセル 　　ボトルネック調査 　　実行計画分析 ■ アップデート 　　セキュリティアップデート 　　アップグレード

監視の目的と役割● 運用管理の目的

– DBの状態を適切に把握し、健全な状態を保つこと● 監視の目的

– 様々な情報を補足し現状を把握する– 加えて今後に予想される状況を推測する

● 見えている問題だけでなく、隠されている問題も● 運用管理の起点ともいえる仕事

– 監視で得た情報がないと始まらない● サポートへのエスカレーション時にも必要

– 地味だけどバックアップやメンテナンスと同じ様に重要

監視対象には何がある？■ 死活監視 　外形監視(ping/port/SQL監視) 　H/W監視 　プロセス監視 　ログ監視 ■ リソース監視・性能監視 　CPU/MEM/IO/NW使用量監視 　ディスク容量監視 　スロークエリ監視 　最頻SQL監視 　キャッシュヒット率監視 　レプリケーション遅延監視

■ 統計情報レポート 　トランザクション量 　コミット数/ロールバック数 　アーカイブ量 　DBサイズ量 　チェックポイント頻度 　自動VACUUM頻度 　同時接続数 　スループット・レスポンス 　バッチ処理時間

他にも、システム特性に応じて様々・・

監視対象には何がある？■ 死活監視 　外形監視(ping/port/SQL監視) 　H/W監視 　プロセス監視 　ログ監視 ■ リソース監視・性能監視 　CPU/MEM/IO/NW使用量監視 　ディスク容量監視 　スロークエリ監視 　最頻SQL監視 　キャッシュヒット率監視 　レプリケーション遅延監視

■ 統計情報レポート 　トランザクション量 　コミット数/ロールバック数 　アーカイブ量 　DBサイズ量 　チェックポイント頻度 　自動VACUUM頻度 　同時接続数 　スループット・レスポンス 　バッチ処理時間

まずはログから！

ログでこれだけは押さえておこう● ログから分かること

– 何をログから読み取る必要があるだろう？– そもそもログは何を出してくれるのか？

● 設定– 必要な情報を出力できるよう適切に設定– 問題が起こってからでは後の祭り

● ログの基本的な見方– 自分でログを見て何が起こっているかを判断する– 事象によって緊急度や必要なアクションが変わる– ログはトラブルシュートの起点

PostgreSQLのログで分かること● エラーなどの異常処理

– 無視できるものから、システム停止に至るまで● スロークエリ

– 指定時間より時間のかかったDML・DDL文● 処理されたDML、DDL

– ユーザ毎に出力する・しないが制御可能– 監査や定期処理の監視に

● autovacuumの処理● Checkpoint処理● 接続、切断処理● など

ログの設定(これだけはやっておく)

パラメータ 説明 おすすめ設定log_destination ログの出力形態 stderr, (ログ監視ツール有 syslog)

orcsvlog, (ログ監視ツール有 syslog)

logging_collector Stderrやcsvlogログをファイルへリダイレクトするか？

on

log_line_prefix ログメッセージの接頭情報

(9.0 ～)[%t][%p][%c-%l][%x][%e]%q(%u, %d, %r, %a) (～8.4)l[%t][%p][%c-%l][%x ]%q(%u, %d, %r)

log_line_prefix は時間やログ対象処理のDB、ユーザなどなど、ログ解析に必要な情報を付与するので絶対指定すること。デフォルトは何も指定されていません！！

ログの設定(必要ならばやっておく)パラメータ 説明log_connections ユーザの接続時刻を記録log_disconnections ユーザの接続断の時刻と接続していた時間を記録log_statement 実施したSQL文

接続系のログは監査などに有用です。log_statementは監査用途の他、定期メンテ時のオペレーションチェックなどにも便利です。ただし、全てのSQLを記録するのは避けましょう。特定のセッションで動的に指定する、もしくはDBに対して重要な変更を実施できるスーパーユーザに設定しておくと便利です。

(参考) sysadm ユーザの処理を記録する

postgres=# ALTER ROLE sysadm SET log_statement TO 'all';postgres=# SELECT usename, usesuper, useconfig FROM pg_user; usename | usesuper | useconfig ----------+----------+--------------------- postgres | t | sysadm | t | {log_statement=all}(2 rows)

ログの設定(できればやっておく)パラメータ 説明 おすすめ設定log_min_duration_statement 本パラメータの指定時間

以上かかったSQL文を時間とともに出力する

'10sec'(要件に応じて)

log_autovacuum_min_duration 本パラメータの指定時間以上かかったautovacuumの処理をログに出力する

'1min'

log_min_duration_statement は、性能上問題のあるSQLをあぶり出すのに便利です。可能な限り活用しましょう。

ログの見方

レベル syslog eventlog 説明PANIC CRIT ERROR クリティカルなエラーなど、DBインス

タンス全体に影響する問題。FATAL ERR ERROR 接続エラーや強制切断など、セッショ

ンレベルに影響する問題。LOG INFO INFORMATION チェックポイントやautovacum実施、

スロークエリ結果など、DBAが把握すべき処理。

ERROR WARNING ERROR シンタックスエラーなど、SQLレベルに影響する問題。

WARNING NOTICE WARNING 作法違反の警告。アプリケーションなどの見直しを推奨。

NOTICE NOTICE INFORMATION ユーザ補助となる情報。INFO INFO INFORMATION ユーザが明示的に出力する、ユーザ補

助となる情報。DEBUG DEBUG INFORMATION 開発時向け。量が多いので運用時に出

力しないように！

エラーレベルごとの意味を押さえましょう。(LOGを除き)ERROR以上は即座の対応が必要です。

ログの見方

カテゴリ 内容[エラーレベル] エラー内容STATEMENT エラー起因となった実際の処理内容LOCATION エラーが発生したコード上の位置HINT 発生したエラーの原因や回避策CONTEXT エラーが発生したコンテキスト(関数など)

PostgreSQLでは、エラーメッセージの他、発生個所や解決のためのヒントもログに添えてくれます。落ち着いて読み込み、適切なアクションにつなげましょう。

(参考) ログメッセージの例

(表示の関係で、prefixを削っています)LOG: 22023: invalid value for parameter "log_min_duration_statement": "10sec"HINT: Valid units for this parameter are "ms", "s", "min", "h", and "d".LOCATION: set_config_option, guc.c:5472

LOG: F0000: configuration file "/Users/postgres/base/pgsql930/postgresql.conf" contains errors; unaffected changes were applied

FATAL: 57P01: terminating connection due to administrator commandCONTEXT: SQL statement "select pg_sleep($1)" PL/pgSQL function sample_f(integer) line 1 at SQL statementLOCATION: ProcessInterrupts, postgres.c:2857STATEMENT: SELECT sample_f(1000);

PostgreSQLのログでできないこと● レベルやカテゴリ別のログルーティング

– PostgreSQLは基本的に全てのログメッセージを特定のファイルに吐きます

– ERRORだけをsyslogに吐く、スロークエリだけ別のファイルに出力する、といった機能はありません

● 古いログの削除– ログファイルの切り替えは可能ですが、古いログ

のクリーンナップ機能はありません● logrotateやcronなどを利用し、ユーザ側で古いログの別所へのアーカイブ、あるいは削除を実施しましょう

PostgreSQLのログでできないこと● ALERT

– 特定のエラーメッセージやエラーコードの出現時にアラートをあげる機能はありません

– 別途、ログ監視ツールなどを利用して監視を行いましょう

監視対象には何がある？■ 死活監視 　外形監視(ping/port/SQL監視) 　H/W監視 　プロセス監視 　ログ監視 ■ リソース監視・性能監視 　CPU/MEM/IO/NW使用量監視

　ディスク容量監視 　スロークエリ監視 　最頻SQL監視 　キャッシュヒット率監視 　レプリケーション遅延監視

■ 統計情報レポート 　トランザクション量 　コミット数/ロールバック数 　アーカイブ量 　DBサイズ量 　チェックポイント頻度 　自動VACUUM頻度 　同時接続数 　スループット・レスポンス 　バッチ処理時間

特にはまりやすい容量監視

容量監視でこれだけは押さえておこう● どこに何があるのか

– PostgreSQLが扱うファイルとかはどこにあるのか– 溢れた場合に即システム停止になる・・・

● 測るタイミング– 常時見ておくべきもの– 特定の処理の前に見ておくべきもの– メンテナンス処理の前は要注意！！

● 測り方– どのようにしてサイズを取得するのか– 何のサイズを測っているのかを正しく知る

PostgreSQLが関連するファイルとディレクトリ

ディレクトリ名 利用用途 監視すべきタイミング サイズの測り方$PGDATA/base 各テーブルやイン

デックスの格納常時メンテナンス前

専用の関数(次ページ)

$PGDATA/base/pgsql_tmp ディスクソートやハッシュ処理などの一時領域

バッチ処理時メンテナンス処理時

ls や du

$PGDATA/pg_log サーバログの格納 常時 ls や du$PGDATA/pg_xlog トランザクション

ログ(WAL)の格納常時 ls や du

アーカイブディレクトリ WALのアーカイブ格納

バッチ処理時メンテナンス実行時

ls や du

テーブルスペース 各テーブルやインデックスの格納

常時メンテナンス前

専用の関数(次ページ)

オブジェクトのサイズ

対象 関数 備考テーブル pg_relation_size()テーブル+TOAST pg_table_size() (ver9.0から)インデックス(単体) pg_relation_size()インデックス(合計) pg_indexes_size() テーブルに付与された全ての

インデックス(ver9.0から)テーブル+TOAST+インデックス(合計)

pg_total_relation_size()

テーブルスペース pg_tablespace_size()データベース pg_database_size()

目的に応じて様々な関数を利用できます。名前が若干紛らわしいので、間違わないように注意しましょう！

こんな時に注意● VACUUM実施

– 意外とWALが大量に出る– アーカイブログディレクトリに要注意

● REINDEXやALTER TABLE実施– 内部的に新規作成 & スワップ– 対象のテーブルやインデックスがあるディレクト

リを圧迫– 加えてWALも出る– REINDEXやALTER TABLE実施前は、最低限対

象テーブルの1.5倍のサイズの空き領域があることを確認

監視対象には何がある？■ 死活監視 　外形監視(ping/port/SQL監視) 　H/W監視 　プロセス監視 　ログ監視 ■ リソース監視・性能監視 　CPU/MEM/IO/NW使用量監視 　ディスク容量監視 　スロークエリ監視 　最頻SQL監視 　キャッシュヒット率監視 　レプリケーション遅延監視

■ 統計情報レポート 　トランザクション量 　コミット数/ロールバック数 　アーカイブ量 　DBサイズ量 　チェックポイント頻度 　自動VACUUM頻度 　同時接続数 　スループット・レスポンス 　バッチ処理時間

稼働状態を把握しよう！意外と簡単！？

稼働統計情報監視でこれだけは押さえておこう

● 取得可能な稼働統計情報– PostgreSQLの稼働統計情報とは何か– どんな情報がとれるのか？

● 取得方法– 基本的にSQLで取得することになる– 累積または揮発情報となるので定期的に取得する

● 情報の見方と使い方– 取得したら使う– 今と将来の状態を予測しよう！

稼働統計情報って？● PostgreSQLが内部で蓄積している様々なアクティビティ情報– stats_collectorというプロセスが情報を受け付け記録– ユーザはビューを通して参照可能

PostgreSQLインスタンス

postgres

postgres

autovacuum

stats_collector

checkpointer

稼働統計情報

抑えておくべき稼働統計情報ビュー 説明 役立つ情報pg_stat_database DB単位の稼働統計

情報ビュー - 基本的に累積情報

DB単位の・ コミット/ロールバック数・ 現在の接続数・ 更新&参照件数・ deadlock数

pg_stat_user_tables テーブル単位の稼働統計情報ビュー - 基本的に累積情報

テーブル単位の・ SeqScan回数と件数・ 更新件数・ 最後のautovacuum/analyze時間

pg_stat_activity 現在の各クライアントからの処理内容 - 揮発情報

現在DBに接続しているクライアントの・ クエリ内容・ 接続開始&トランザクション開始　 &クエリ開始時間・ ロック待ちかどうか

たくさんの稼働統計情報用のビューがありますが、まずは上記3つのビューを使えるようにしましょう。これで大半のことが分かります。

ぜひ使いたい稼働統計情報ビュー 説明 役立つ情報pg_stat_statements(v8.4から)

DBインスタンス全体の各SQLの稼働統計情報ビュー - 基本的に累積情報

各SQLの・ 実施回数・ 累積レスポンスタイム・ 取得された件数

pg_stat_statementsにより、SQLのパフォーマンス情報が非常に手軽に取得できます。ただし、このビューを有効にするにはPostgreSQLの再起動が必要になるため、できれば早い段階で導入しておきましょう！

(参考) pg_stat_statementsの導入1. rpm などでcontribパッケージを入れる　(もしくはソースをmake && make install)

– postgresql-xx-contrib-*.rpm

2. postgresql.conf の shared_preload_librariesパラメータを　次の様に設定 shared_preload_libraries = 'pg_stat_statement'

3. PostgreSQLを再起動(service postgresql restart など)

4. ビューを作成したいDBに対して　CREATE EXTENSION pg_stat_statements;

を実施

これだけ。

(参考) pg_stat_statementsの導入SELECT dbid, userid, substr(query,1, 15) || '...' || right(query,15), total_time, calls, total_time/calls as avg_time, rows FROM pg_stat_statements order by total_time desc limit 5;-[ RECORD 1 ]---------------------------------dbid | 16391userid | 10?column? | UPDATE pgbench_... WHERE bid = ?;total_time | 2393.6419999997calls | 61019avg_time | 0.0392278142873483rows | 61019

稼働統計情報の取得● SELECT文でビューにアクセスして取得● スーパーユーザで実施する

– 権限の問題で情報の取りこぼしが起こる● 累積情報が主となるので定期的に取得

– 差分を見られるようにする– 最低１日１回– バッチや繁忙時間帯の前後に取得すると効果的

稼働統計情報の取得● ちょっとした一工夫で解析が楽に

– COPY文でCSV形式に保存しておく– 別テーブルへ保存しておく

- - COPY取得COPY (SELECT now(), * FROM pg_stat_database WHERE datname = 'postgres') TO '/var/lib/pgsql/stats_log/xxxx.csv' WITH CSV;

- - 別テーブルへ保存CREATE TABLE pg_stat_database_store (correct_time timestamp, LIKE pg_stat_database);

INSERT INTO pg_stat_database_store SELECT now(), * FROM pg_stat_database WHERE datname = 'postgres'

稼働統計情報の取得● ちょっとした一工夫で解析が楽に

– 現在時刻との差分で長時間実施処理を把握postgres=# SELECT 　　　　　now() - xact_start AS txn_duration, 　　　　　now() - query_start AS query_duration, 　　　　　datname, pid, usename, query, waiting 　　　　　FROM pg_stat_activity 　　　　　WHERE pid <> pg_backend_pid() order by query_duration desc;

-[ RECORD 1 ]--+-------------------------------txn_duration | 00:02:06.745105query_duration | 00:02:06.745105datname | postgrespid | 29689usename | postgresquery | SELECT * from pg_sleep(10000);waiting | f

でもSQL発行はメンドクサイ・・● ツールを活用するのも手だて● pgperf

– 稼働統計情報の保存用スキーマとテーブル作成– 任意のタイミングで情報一括取得– 差分を自動計算してビューとして表示– アドホックに情報を確認したい場合に有用– http://pgsqldeepdive.blogspot.jp/2012/12/bl

og-post.html

でもSQL発行はメンドクサイ・・● ツールを活用するのも手だて● pgperf

– 稼働統計情報の保存用スキーマとテーブル作成– 任意のタイミングで情報一括取得– 差分を自動計算してビューとして表示– アドホックに情報を確認したい場合に有用– http://pgsqldeepdive.blogspot.jp/2012/12/bl

og-post.html

でもSQL発行はメンドクサイ・・● pg_statsinfo

– http://pgstatsinfo.projects.pgfoundry.org/pg_statsinfo-ja.html

まとめ● ログ監視、容量監視、稼働統計情報の3つにつ

いて、基本的な観点やテクニックをお伝えしました– すぐに使えるものばかりですね！– 基本ではありますが、これだけできれば大部分の

問題に対処できると思います● 今日紹介したもの以外にも、重要な監視項目

はあります– プロセス監視とかHWリソース監視とか・・– これらはまた別の機会にお話できれば・・

● 適切な監視で、安心運用管理を！

PostgreSQLの運用に役立つ参考情報

■ バックアップの入門に　- PostgreSQLバックアップ・リカバリ入門　 http://www.slideshare.net/satock/postgre-sql-20215836

■ 運用管理全般について、もっと深く知りたい　- PostgreSQL運用テクニック　 　http://www.postgresql.jp/events/event_sozai/Summer_seminar2011_Operation_technique.pdf

　- PostgreSQL運用テクニック・レベルアップ編　 http://www.postgresql.jp/events/pgcon2012/docs/c3.pdf　- Let's Postgres 運用管理　 http://lets.postgresql.jp/map/operation　- OSS-DB Exam Gold 技術解説無料セミナー　 http://www.oss-db.jp/news/event/image/20130615_01.pdf

ご清聴ありがとうございました