postgrsqlで実行されたSQLの実行時間や回数などを記録する
スロークエリの記録はpostgresqlにも機能として備わっていますが、流れているSQLの実行時間や回数などを記録できるようcontrib拡張モジュールをインストールしてみます。
インストール
(linux)yum install postgresql-contrib.x86_64
(freebsd)pkg install postgresql-contrib
上記のようにyumやpkg使ったりソースからビルドしたりしてインストールしてください。
postgresql.confの編集
postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 2000
pg_stat_statements.track = top
pg_stat_statements.save = on
| 設定値 | 説明 |
|---|---|
| pg_stat_statements.max | モジュールで収集する SQL の最大数を指定するパラメータです。 SQL の数がこのパラメータの値を越えた場合には、実行回数の少ない SQL から順番に統計情報が削除されます。 |
| pg_stat_statements.track | pg_stat_statements モジュールで収集する SQL の種類を指定するパラメータです。 パラメータの値に「top」と指定するとクライアントから直接実行された SQL のみ、「all」を指定すると関数内から間接的に実行された SQL も含めて収集されます。 pg_stat_statements モジュールを無効にするには、このパラメータの値に「none」と指定します。 |
| pg_stat_statements.save | pg_stat_statements モジュールが収集した統計情報をデータベースサーバの停止時に保存するかを指定するパラメータです |
| track_activity_query_size | こちらはpg_stat専用ではないですが、記録するSQLの長さを設定します。以上になると...で表現されます。デフォルトは1024byteです。 |
再起動
postgresql.confを編集したので再起動します。
モジュール有効化
モジュールが使用可能かどうかを確認します。下記のように表示されれば使用可能です。
psql
$ psql -d hoge -c "select * from pg_available_extensions where name = 'pg_stat_statements'"
name | default_version | installed_version | comment
--------------------+-----------------+-------------------+-----------------------------------------------------------
pg_stat_statements | 1.1 | | track execution statistics of all SQL statements executed
(1 row)
下記コマンドでインストールします。
CREATE EXTENSIONと表示されればOKです。
psql
$ psql -d hoge
hoge=# CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION
インストールできたか確認します。
installed_versionに表示されればOKです。
psql
$ psql -d hoge -c "select * from pg_available_extensions where name = 'pg_stat_statements'"
name | default_version | installed_version | comment
--------------------+-----------------+-------------------+-----------------------------------------------------------
pg_stat_statements | 1.1 | 1.1 | track execution statistics of all SQL statements executed
(1 row)
使用方法
実際に使ってみましょう。幾つかのSQLを実行した後、下記のようにpg_stat_statementsに対してクエリを投げれば実行されたSQLが色々な情報と共に取得できます。
queryやcalls、total_time、rowsあたりをみながら、またはblksなども必要に応じて取得してチューニングすれば良いと思います。
各カラムの意味は
https://www.postgresql.jp/document/9.4/html/pgstatstatements.html
私は下記で遅いSQLを特定して、必要に応じて他のblksなどの情報も表示させます。
sql
SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /
nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements ORDER BY total_time DESC LIMIT 100;
reset
蓄積された情報を一旦リセットしたい場合は下記でリセットできます。
SELECT pg_stat_statements_reset();
アンインストール
アンインストールは下記で行います。
DROP EXTENSION pg_stat_statements;