はじめに
pg_statsinfoとpg_stats_reporterをインストールしたときのメモです。
公式サイトのドキュメントを参考にしています。
公式サイトのドキュメントが詳細に書かれており分かりやすかったので、そちらを参照して構築するのが良いと思います。
※version11をインストールしたのですが、ドキュメントは11用が見つけられなかったので10用を参照しました。
pg_statsinfoのインストール
以下のサイトからRPMファイルをダウンロードしてインストールします。
pg_statsinfo-11.0-1.pg11.rhel7.x86_64.rpm
https://sourceforge.net/projects/pgstatsinfo/
※2019年10月時点ではPostgreSQL12用のRPMファイルはありませんでした。
# rpm -ivh pg_statsinfo-11.0-1.pg11.rhel7.x86_64.rpm
pg_statsinfo用の設定をpostgresql.confに追記します。
以下は、公式サイトに記載された推奨設定の抜粋です。
shared_preload_libraries = 'pg_statsinfo' # 事前ロードを行う
pg_statsinfo.snapshot_interval = 30min # スナップショットの取得間隔
pg_statsinfo.enable_maintenance = 'on' # 自動メンテナンス設定
pg_statsinfo.maintenance_time = '00:02:00' # 自動メンテナンス実行時刻設定
pg_statsinfo.repolog_min_messages = disable # ログ蓄積機能の設定
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_threshold = 30s # ロック競合情報に記録する対象の条件(閾値)を指定する
log_min_messagesは"log"になっていますが、errorが出力されないのでinfo, warningあたりで良いかも。
有効な値は以下のとおり。
- DEBUG5、DEBUG4、DEBUG3、DEBUG2、DEBUG1、INFO、NOTICE、WARNING、ERROR、LOG、FATAL、PANIC
また、pg_statsinfo.stat_statements_maxも変更した方が良いです。
pg_statsinfo.stat_statements_maxはpg_stat_statements、pg_store_plansで収集するデータ数の上限で、デフォルトで30と少ない値になっているので、いつも100～300ぐらいに設定しています。
PostgreSQLを再起動すると、pg_statsinfodプロセスが自動起動します。
$ pg_ctl restart
$ ps -efl | grep pg_statsinfod
0 S postgres 3607 3605 0 80 0 - 117800 futex_ 05:44 ? 00:00:00 /usr/pgsql-11/bin/pg_statsinfod 3597
また、ログはlog/pg_statsinfo.logに出力されます。
クエリの統計情報を取得
クエリの統計情報をスナップショットで取得する場合の設定です。
まず、contrib拡張モジュールをインストールします。
# yum -y install postgresql11-contrib
postgresql.confのshared_preload_librariesに"pg_stat_statements"を追加します。
shared_preload_libraries = 'pg_statsinfo,pg_stat_statements'
pg_stat_statements.max = 100 # 収集するSQLの最大数
pg_stat_statements.track = top # 収集対象のSQL（top:クライアントから直接実行されたSQL）
pg_stat_statements.save = on # サーバ停止時に保存する
再起動(pg_ctl restart)後、以下のコマンドを実行します。
$ psql -d postgres -c "CREATE EXTENSION pg_stat_statements"
CREATE EXTENSION
レポートを生成すると以下の項目が追加されるようになります。
/** Statements **/
-----------------------------------
User Database Calls Total Time Time/Call Block Read Time Block Write Time Query
------------------------------------------------------------------------------------------------------------------------
postgres testdb 10000 36.313 sec 0.004 sec 0.000 ms 0.000 ms UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2
postgres testdb 10000 29.610 sec 0.003 sec 0.000 ms 0.000 ms UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2
postgres testdb 10000 0.603 sec 0.000 sec 0.000 ms 0.000 ms UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2
PostgreSQL pg_stat_statementsで統計情報を見てみよう by 株式会社システムインテグレータでは以下のような記述があります。
pg_stat_statementsを使用しているとパフォーマンスが低下するため、特に本番環境やパフォーマンステストを行う際は無効にすることを推奨します。
手動でのスナップショットの取得
手動でスナップショットを取得します。手動の場合は非同期で処理されるため、プロンプトはすぐに返ってきます。
$ psql -d postgres -c "select statsinfo.snapshot('manual')"
snapshot
----------
(1 row)
スナップショットの一覧を表示
$ pg_statsinfo -l -d postgres -p 5432 -U postgres
----------------------------------------
Snapshot List
----------------------------------------
SnapshotID InstanceID Host Port Timestamp Comment Execute Time Size
-----------------------------------------------------------------------------------------------------------------------------------------
1 1 postgresserver1 5432 2019-05-06 05:46:15 manual 00:00:00 360 KiB
2 1 postgresserver1 5432 2019-05-06 05:46:31 manual 00:00:00 144 KiB
3 1 postgresserver1 5432 2019-05-06 06:00:00 00:00:00 152 KiB
4 1 postgresserver1 5432 2019-05-06 06:30:00 00:00:00 152 KiB
5 1 postgresserver1 5432 2019-05-06 07:00:00 00:00:00 136 KiB
レポートを生成
レポートを生成するコマンドの例は以下のとおりです。
最初から最後のスナップショットまでのレポートを生成
$ pg_statsinfo -r All -d postgres -p 5432 -U postgres
スナップショットIDが22から23までのレポートを生成
$ pg_statsinfo -r All -d postgres -p 5432 -U postgres -b 22 -e 23
pg_stats_reporterのインストール
以下のサイトからRPMファイルをダウンロードし、インストールします。
pg_stats_reporter-11.0-1.el7.noarch.rpm
https://sourceforge.net/projects/pgstatsinfo/files/pg_stats_reporter/11.0/
事前にphp, php-pgsql, httpdをインストールしておく必要があります。
# yum -y install php php-pgsql php-intl
# rpm -ivh pg_stats_reporter-11.0-1.el7.noarch.rpm
設定ファイルは"/etc/pg_stats_reporter.ini"に格納されています。
今回はDBへの接続情報だけ修正しました。
;-------------------------------------
; database connection
;-------------------------------------
host = 127.0.0.1
port = 5432
dbname = postgres
username = postgres
password = postgres
PostgreSQL への接続パスワードを「.pgpass」を使用して設定する場合は「password」は設定しないでください。
pg_hba.confの設定を変更します。
ファイルの最初のほうに以下の1行を追加します。
host postgres postgres 127.0.0.1/32 md5
設定後、PostgreSQLを再起動します。
設定が終わったらHttpdサービスを自動起動に設定し、サービスを起動します。
# systemctl enable httpd.service
# systemctl start httpd.service
サービス起動後、以下のURLでGUIにアクセスできます。
http://[IPアドレス]/pg_stats_reporter/pg_stats_reporter.php
アクセス後の画面は以下のようになります。
ちなみにSELinuxを無効化しないと接続できなくてはまりました。