これは Supership Advent Calendar 22日目の記事です。
前日の記事は @Y_nakatani さんのぼくのわたしの23年間でした。

みなさんはじめまして！

Supership株式会社に2018年度新卒で入社した @yuta_kageyama です。
現在アドテクノロジーセンターという部署で、巨大な広告配信インフラを担当するチームに所属しています。
入社して早くも半年以上が経ちましたが、これまで言うことを聞かない数百台単位のサーバや、
静的データを含まずに数Gbpsを超える膨大なトラフィックと戦ってきました。

最近はPostgreSQLのことを想ってばかりいるので、今回はPostgreSQLと上手に付き合うための統計ツールを2つほどご紹介したいと思います。

PostgreSQLの統計情報

PostgreSQLにはもともとpgstatという機能がついていて、様々な統計情報を参照することができるようになっています。
今回ご紹介するのはこれらの情報を自動的に取得し、時系列で保存した上でグラフィカルに表示するツールです。
では早速見ていきましょう。

pg_statsinfo

pg_statsinfoは、日本電信電話株式会社様で作られた日本発のオープンソースソフトウェアです。
監視対象のPostgreSQLインスタンスに共有ライブラリとしてインストールするとエージェントが起動するので、
そこから統計情報を蓄積するサーバにスナップショットとして送信します。

インストール

パッケージを入れる

まずパッケージを入れます。公式サイトから利用しているPostgreSQLとOSのバージョンに合わせたRPMをとってきます。
今回はPostgreSQL9.6(PGDGリポジトリから入れたもの)とCentOS7の想定で書きます。
またクエリと実行計画の情報もほしいので、
pg_stats_statements(postgresql-contribパッケージに入っています) と pg_store_plans も入れましょう。

# yum install postgresql96-contrib
# rpm -ivh pg_store_plans96-1.1-1.el7.x86_64.rpm
# rpm -ivh pg_statsinfo-3.3.0-1.pg96.rhel7.x86_64.rpm

リポジトリサーバの設定

今回は情報を蓄積するサーバを別にしたいので、適当なPostgreSQLインスタンスを立てて設定しておきます。
必要なのはpg_statsinfoが使用するユーザーの定義と、そのユーザーが読み書きできるデータベース、
また pg_store_plans をインストールした上で以下のSQLを実行しておきます:

postgres=# CREATE EXTENSION pg_store_plans;

※ shared_preload_libraries への追加や pg_stat_statements は必要ありません。
※ pg_hba.conf などを適切に設定し、監視対象サーバからの接続時にパスワード入力が必要ないようにしましょう。

設定ファイルを書く

監視対象サーバの postgresql.conf に以下のような記述を追加します（一例です）。
各設定項目の詳細はpg_statsinfoの解説などを参照してください。

shared_preload_libraries = 'pg_statsinfo,pg_stat_statements,pg_store_plans'
pg_statsinfo.snapshot_interval = 5min
pg_statsinfo.enable_maintenance = 'on'
pg_statsinfo.maintenance_time = '00:02:00'
pg_statsinfo.textlog_min_messages = warning
pg_statsinfo.syslog_min_messages = disable
pg_statsinfo.syslog_line_prefix = '%t %p '
pg_statsinfo.repository_server = 'host=repo_db_ip port=5432 dbname=repo_db_name user=repo_db_user'
pg_statsinfo.repository_keepday = 60
pg_statsinfo.repolog_keepday = 60
track_functions = 'all'
track_activities = on
track_counts = on
track_io_timing = on

ここで repo_db_ip 等は先程設定したリポジトリデータベースの情報を指定します。

再起動、CREATE EXTENSION

shared_preload_libraries の反映には再起動が必要なので、監視対象のPostgreSQLを再起動します。
その後、pg_stat_statementsとpg_store_plansを利用するために以下のようなSQLを発行しておきます:

postgres=# CREATE EXTENSION pg_stat_statements;
postgres=# CREATE EXTENSION pg_store_plans;

2行目の CREATE EXTENSION pg_store_plans は監視対象サーバとリポジトリサーバの双方で必要です。

pg_stats_reporterを入れる

Webインターフェースで統計情報を見るために、以下の手順でpg_stats_reporterを入れます。
これはpg_statsinfo用のPHP製Webインターフェースです。

# yum install httpd php php-pgsql php-intl php-cli
# rpm -ivh pg_stats_reporter-3.3.0-1.el7.noarch.rpm

設定は公式ドキュメントを参考に行ってください。ここまでできたら、

# systemctl start httpd

して、アクセスしてみましょう。

様子

（見せられないところばっかでごめんなさい……）

感想

テーブル数などが多いとかなりWebインターフェースが重いですが、基本的な情報はしっかり見えて便利です。
この後紹介するpgwatch2と違い、プラン情報も表示できるので、クエリがなぜ重いかまで追跡できます。
またログも蓄積するので、ログも追いながら分析をすることができます。
監視対象のサーバでエージェントを動かすのでマジモンの本番サーバに入れるにはちょっと勇気が必要です。
リポジトリDBのスキーマは公開されていますので、ご自身でWebフロントエンドを作るのもありかもしれません。

pgwatch2

pg_statsinfoを入れてしばらく、見えてきた激ヤバクエリたちは殲滅され、世界に平和が訪れました。
ある日pgwatch2というPostgreSQLの統計情報を Grafana で表示できるものがあると聞き、試しに入れてみることにしました。
これはCYBERTEC社というオーストリアの会社で作成されていて、Go言語で書かれたGathererが、
監視対象サーバに情報を取得しに行ってInfluxDBに蓄積する仕組みのようです（現代的ですね）。
つまり監視対象サーバにエージェントを入れなくていいってことですね。

インストール

今回はお試し気分でとっても簡単にやりましょう。適当にDockerでドッカーンします。

$ docker run -d --name pgwatch2 -p 8080:8080 -p 3000:3000 cybertec/pgwatch2

もうこれで動き始めています。本当に簡単ですね。
Dockerホストの8080番ポートにブラウザでアクセスしてみると管理画面が見えます。

監視対象サーバの準備

監視対象サーバにはちょっとした補助Functionと、CPU Loadなどの取得のためにPL/Pythonを入れてあげる必要があります。
まず設定ファイルに以下のような設定を追加します（私の場合上記でpg_statsinfoを入れた際にすでに設定してあります）。

shared_preload_libraries = 'pg_stat_statements'
track_io_timing = on

PL/Pythonは次のパッケージでインストールできます。

# yum install postgresql96-plpython

次に補助Function群とEXTENSIONの作成を行います。
※監視対象のDBで行ってください

db=# CREATE EXTENSION pg_stat_statements;
db=# CREATE EXTENSION plpythonu;

# psql -U postgres -f pgwatch2/sql/metric_fetching_helpers/stat_activity_wrapper.sql db
# psql -U postgres -f pgwatch2/sql/metric_fetching_helpers/stat_statements_wrapper.sql db
# psql -U postgres -f pgwatch2/sql/metric_fetching_helpers/cpu_load_plpythonu.sql db

※これらのSQLファイルはリポジトリ等からもとってこれるので、適当に保存して使ってください

設定

pgwatch2が動いているホストの8080番を開き、右上の DBs を押します。
以下のように設定して右端の New を押しましょう。

Unique Name: 適当な名前（監視対象のDB名など）
DB host: 監視対象のデータベースのホストアドレス
DB dbname: 監視対象のデータベース名
DB user: 監視対象のデータベースに作成したpgwatch2用ユーザ名
DB password: 上記ユーザーのパスワード
Preset config: お好みで（私は exhaustive にしています）

様子

しばらくすると統計情報が集まってくるので、今度はpgwatch2が動いているホストの3000番を開きます。
するとGrafanaが出て統計情報が見れます。

現代的でいいですね

感想

Webインターフェースの表示が早く、また監視対象サーバへの変更がほとんど必要ないのでいいですね（再起動は必要です）。
プランの情報などは多分取れていないのですが、 shared_buffer のヒット率が見れるなど、
情報量的には一長一短という感じです。現代的インターフェースなのでこちらのほうが好まれるかも？

おわりに

ずいぶん駆け足になってしまいましたが、大きく2つのPostgreSQLの統計情報を取得する方法についてご紹介しました。
コメントしていただければもう少し丁寧に書きますので、わからないところがあったら言ってくださいね！

弊社では超大規模広告配信に携わるインフラエンジニアを大募集しています。
低レイテンシ高トラフィックのネットワーク設計、運用から物理層まで、インフラっぽい仕事がしたい方！
また仮想化基盤（OpenStack等）をオンプレ上で自分の手で運用したい方など、大絶賛募集中です。

あなたとSupership、今すぐ応
募

明日は @bootjp さんによる「fluentdへ新機能追加した話」の予定です。ご期待下さい！

pg_statsinfoとpgwatch2でPostgreSQLの統計を見よう

PostgreSQLの統計情報

pg_statsinfo

インストール

パッケージを入れる

リポジトリサーバの設定

設定ファイルを書く

再起動、CREATE EXTENSION

pg_stats_reporterを入れる

様子

感想

pgwatch2

インストール

監視対象サーバの準備

設定

様子

感想

おわりに