技術情報

pgBadger (PostgreSQL のログ解析ツール)

2015/6/2 掲載

pgBadger とは

pgBadger 概念図

pgBadger は PostgreSQL のログファイルを解析して、 SQL の実行やサーバの動作に関するレポートを作成するツールです(図1)。 多数の視点から解析された統計レポートは普段のデータベース動作の把握のみならず、 パフォーマンス改善のヒントとして役立てることができます。

pgBadger で出力されるレポートは以下の特徴があります。


HTML 形式でグラフ出力が可能

出力されるレポートを HTML 形式にすることで解析結果の多くを グラフで表示することができます(テキスト形式や JSON 形式での出力も可能です)。 解析されたログ情報がグラフ化されることで、素のログデータよりも 人が見る上で非常に見通しの良いものとなります。 なので、 別途に他ツールで処理する必要がなければ、HTML 形式のレポート出力をおすすめします。


多数の統計対象

レポートには以下に示すようにデータベースの パフォーマンス分析に役立つデータが多数出力されます。


日次、週次単位で作成可能

pgBadger は各ログが記述された日時のデータを用いて1日単位、 1週単位のレポートを作成することができます。 これを継続的に利用することで各日、各週におけるデータベース動作の 比較検証が可能になります。


クエリ統計について

pgBadger のクエリ統計には具体的なクエリと 汎化されたクエリに関する統計があります。クエリを汎化するとはつまり、

UPDATE pgbench_tellers SET tbalance + 3645 WHERE tid = 2;
UPDATE pgbench_tellers SET tbalance + 1632 WHERE tid = 10;

といったクエリを以下のように一般化して、

UPDATE pgbench_tellers SET tbalance + 0 WHERE tid = 0;

同一種類のクエリとして認識するということです。

汎化されたクエリに関する統計を含めることにより、 より実用的な統計を導き出しています。


インストールと設定

本記事の検証環境は CentOS 6.6、PostgreSQL 9.4.1 です。

pgBadger インストール

今回は rpm パッケージからインストールする方法をご案内します。 リポジトリは こちらのページからお使いの Linux ディストリビューションを選んで rpm ファイルのリンク先を確認して下さい。

rpm ファイルのリンク先

yum コマンドでリポジトリを登録し、そのリポジトリから pgBadger を インストールします。本記事執筆時の pgBadger のバージョンは 6.4 です。

# yum install http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-centos94-9.4-1.noarch.rpm
# yum install pgbadger

PostgreSQL 設定(postgresql.conf)

pgBadger は PostgreSQL のログからレポートを作成するため、 PostgreSQL のログにはレポートに必要な情報が記述されるように設定する必要があります。

postgresql.conf を以下のように変更します。

# クエリの実行時間が指定した時間以上であったら、クエリのテキストと実行時間をログに残す
log_min_duration_statement = 0
# ログメッセージの頭につける情報
log_line_prefix = '%t [%p]: [%l-1] user=%u, db=%d'	# 標準エラー出力を使う場合
# log_line_prefix = 'user=%u, db=%d'		# syslog を使う場合
# %t = タイムスタンプ(ミリ秒単位はなし)
# %p = プロセスID
# %l = セッション行番号
# %u = ユーザ名
# %d = データベース名
# user, db の指定は任意

# チェックポイントの実行をログに残す
log_checkpoints = on
# クライアントの接続をログに残す
log_connections = on
# クライアントの切断をログに残す
log_disconnections = on
# deadlock_timeout で指定した時間(デフォルト1秒)以上のロック待ちをログに残す
log_lock_waits = on
# 一時ファイルが作成されたことをログに残す(0はすべて)
log_temp_files = 0
# ログの言語は英語限定
lc_messages = 'C'

レポートを作成してみよう

ここでは、1つのログファイルから1つのレポートを作成してみます。

以下の例ではデータベースサーバを起動してから書き込んでいるログファイルを
$PGDATA/pg_log/postgresql-2015-05-12_170333.log とします。

サンプルデータを集めるため、pgbench でいくつかクエリを実行させてみましょう。
pgbench はベンチマークを目的としたモジュールですが、 ここでは多数のクエリを実行させるために用います。

$ pgbench -i
$ pgbench -c 10 -t 1000 # クライアント10, トランザクション1000 の想定で実行

ログファイルに実行したクエリの記述がたまったら、 いよいよレポートを作成します。
と言っても、 pgBadger コマンドに PostgreSQL ログファイルを指定するだけで作成できてしまいます。

$ pgbadger $PGDATA/pg_log/postgresql-2015-05-12_170333.log
[========================>] Parsed 10485879 bytes of 10485879 (100.00%), queries: 6578786, events: 1
LOG: ok, generating html report...

ログの解析が完了すると out.html という名前のレポートファイルが カレントディレクトリに作成されます(オプションでファイル名や 出力フォーマット、保存するディレクトリなどは指定可能です)。
HTML 形式でレポートを作成したので、Web ブラウザを使ってレポートの中身を見てみましょう。
* ここでレポートを作成せずに HTML レポートをご覧になりたい方は こちらに公開されているサンプルレポートをご覧ください。

ファイルをブラウザで開くと以下のような Overview 画面が開きます。
画面上部のタブから各解析項目のページを開く事ができます。

pgBadger sample report

pgBadger query

pgBadger query by type

pgBadger session time

継続的ログの解析(インクリメンタルモード)を行うには

継続的に解析結果を得るにはインクリメンタルモードを用います。
インクリメンタルモードで pgBadger を実行することで、 日次週次のレポート(インクリメンタルレポート)が作成されます。

$ pgbadger -I $PGDATA/pg_log/postgresql.log -O /var/www/html/pg_reports # -I: インクリメンタルモード, -O: 出力ディレクトリ(インクリメンタルモード利用時には必須)
[==========================>] Parsed 23520870 bytes of 2352870 (100.00%), queries: 2, events: 2
LOG: Ok, generating HTML daily report into /var/www/html/pg_reports/2015/05/13/...
LOG: Ok, generating HTML weekly report into /var/www/html/pg_reports/2015/week-20/...
LOG: Ok, generating global index to access incremental reports...

$ ls /var/www/html/pg_reports # 年付のディレクトリ、全レポートをリンクしたインデックスページ、最終パース行を保存したファイルが作成されます。
2015	index.html	LAST_PARSED
$ ls /var/www/html/pg_reports/2015 # 年付のディレクトリの中に日付、週付のディレクトリが作成されてレポートが作成されます。
05	week-20

インクリメンタルモードでレポートを作成した場合、 各レポートページをリンクしたインデックスページが作成されます。

pgBadger report index

また、一度解析したログファイルは以前に解析した部分を省略して 解析することができるようになります。
これは最後にパースした行を LAST_PARSED という名前のファイルに 保存することで実現しています。

$ cat /var/www/html/pg_reports/LAST_PARSED # 最後に解析した行が保存されています。
2015-05-13 13:58:15	23520870	2015-05-13 13:58:15 JST [7040]: [6-1] user=postgres,db=postgres STATEMENT:	SELECT * FROM test;

手動でインクリメンタルレポートを作成していくこともできますが、 実際は cron を用いて、インクリメンタルレポートの作成を自動化するとよいでしょう。

$ crontab -e
0 4 * * * /usr/bin/pgbadger -I -q /usr/local/pgsql/data/pg_log/postgresql.log # 毎日04:00 にレポートを作成

おわりに

いかがだったでしょうか。
pgBadger は豊富な統計結果を得られるので、 データベースのパフォーマンスの改善を目指すにはうってつけの解析ツールです。
PostgreSQL のパフォーマンスが悪い理由は何なのか分からない、 クエリの実行時間が怪しいのでは、などとお考えのときはぜひ使ってみてください。

リンク

pgBadger ドキュメント(レポートのサンプルが公開されています)
http://dalibo.github.io/pgbadger/

GitHub
https://github.com/dalibo/pgbadger