はじめに

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を無効化しないと接続できなくてはまりました。

参考