pgBadgerでPostgreSQLのクエリ・DBの使用状況を確認する

「SAVACAN」担当のMKです。

今回はpgBadgerという、PostgreSQLのログ解析ツールを使ってみます。
pgBadgerは、PostgreSQLのログを解析し、スロークエリやクエリの統計情報を解析して見やすいhtmlファイルとして出力してくれるツールです。

pgBadgerの基本事項

pgBadgerの基本事項

以上のことがpgBadgerで出来ることです。
pgBadgerはログ解析ツールなので、CPU、メモリ、Disk I/Oなどサーバー本体の負荷は分かりません。
また原則リアルタイム解析では無い為、障害中の原因調査には不向きです。

pgBadgerをインストールする

今回はPostgreSQL 9.6をソースからインストールしてグラフを確認しました。
PostgreSQL の統計情報を取得する方法として、約20GBのDBのダンプをリストアしています。
（DBのダンプをリストアしただけなのでセッション数は取得しませんでした）

事前にWEBサーバーとperlをインストールして、WEBサーバーのポートを解放してください。
公式インストール手順はこちらに記載されています。
https://github.com/darold/pgbadger?tab=readme-ov-file#INSTALLATION

ソースファイルをダウンロードしてインストールします。

wget https://github.com/darold/pgbadger/archive/refs/tags/v12.4.tar.gz
tar xzf v12.4.tar.gz
cd pgbadger-12.4/
perl Makefile.PL
make && make install

正常にインストールが出来たらpgBadgerの設定をpostgresql.confに設定します。
以下は設定例になります。

ログファイルへの出力設定
#logging_collector = off
logging_collector = on

クエリの実行時間が指定した時間以上のときクエリのテキストと実行時間をログに残す
-1が無効、0で全クエリをログ出力
#log_min_duration_statement = -1
log_min_duration_statement = 0

チェックポイントの実行をログに残す
#log_checkpoints = off
log_checkpoints = on

クライアントの接続をログに残す
#log_connections = off
log_connections = on

クライアントの切断をログに残す
#log_disconnections = off
log_disconnections = on

ログメッセージの頭につける情報
#log_line_prefix = ''
log_line_prefix = '%t [%p]: [%l-1] user=%u, db=%d'

deadlock_timeout で指定した時間（デフォルト1秒）以上のロック待ちをログに残す
#log_lock_waits = off
log_lock_waits = on

一時ファイルが作成されたことをログに残す(0は全て)
#log_temp_files = -1
log_temp_files = 0

autovacuumで指定時間（ミリ秒）以上かかったログを残す（0は全て）
#log_autovacuum_min_duration = -1
log_autovacuum_min_duration = 0

設定ができたらPostgreSQLを再起動します。

systemctl restart postgres

以上でインストールは完了です！

出力されたログから解析ファイルを作成

pgbadgerコマンドを使ってPostgreSQLのログを解析し、ブラウザで確認するためのhtmlファイルを出力します。

デフォルト設定では、ログファイルはPostgreSQLをインストールしたディレクトリの/pg_log/に作られています。
pgbadgerコマンドでログを指定して実行すると out.html ファイルが作成されます。

pgbadger /var/lib/pgsql/data/pg_log/postgresql-2024-09-17_160621.log

解析ファイルをWEBサーバーで公開しているディレクトリに移動してブラウザから閲覧ます。

mv out.html /var/www/html/

DBのリストアログから出力された内容を紹介します。
クエリの比率はこのように円グラフにて視覚化されます。

クエリの件数はクエリ別に件数とパーセントにて表示されます。

クエリ実行時間の比率はこのように視覚化されます。

リストア処理の解析結果なのでCOPY TOとSELECTが多く実行されていることが分かります。

他にもPostgreSQLの使用状況を確認するツールに 「pg_statsinfo」 というものがあります。こちらはDBに組み込む形でPostgreSQLの一部として動作します。リアルタイムの解析が可能ですが若干のオーバーヘッドが想定されます。

pgBadgerはcronで定期実行する事で自動化も可能ですし、ログを渡せば別サーバ―でも実行できるので解析負荷を分離する事が可能な点は大きなメリットですね。ただ、解析用のログを大量に出力する必要があるのでログの管理に注意が必要かも知れません。

大規模なデータベース環境では日常ログも大量になります。手動での解析では見落としも出てきますのでツールを活用して効率よく監視したいです。