postgresqlでpg_stat_statementsを使ってSlow Queryを調べる

概要

postgresql10でスロークエリを調べるための、pg_stat_statementsモジュールのインストール手順と使い方。9.6でも手順は同じ。

環境

• CentOS 7.5
• postgres (PostgreSQL) 10.10

postgresql10のインストール

postgresql10をインストールする。

$ yum -y localinstall https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm
$ sudo yum -y install postgresql10-server

version確認

$ /usr/pgsql-10/bin/postgres --version
postgres (PostgreSQL) 10.10

initdb

$ sudo /usr/pgsql-10/bin/postgresql-10-setup initdb
Initializing database ... OK

enable, start

$ sudo systemctl enable postgresql-10
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-10.service to /usr/lib/systemd/system/postgresql-10.service.
$ sudo systemctl start postgresql-10

login

$ sudo -u postgres /usr/pgsql-10/bin/psql -U postgres
psql (10.10)
Type "help" for help.

postgres=#

pg_stat_statementsモジュールのインストール

事前に確認

pg_configコマンドでライブラリの格納先を確認する。
/usr/bin/pg_configではなく、pgsql-10用のコマンドを利用する。

$ /usr/pgsql-10/bin/pg_config --libdir
/usr/pgsql-10/lib

# 見当たらなければ以下で探す
$ sudo find / -name pg_config

pg_stat_statements.soがあるか確認。
なければ次項のpostgresql-contribのインストールに進む。

$ find `/usr/pgsql-10/bin/pg_config --libdir` -name pg_stat_statements.so

postgresql10-contribのインストール

# CentOSの場合
$ sudo yum install -y postgresql10-contrib

# Ubuntuの場合
$ sudo apt-get install postgresql-contrib-10

pg_stat_statements.soがインストールされた。

$ find `/usr/pgsql-10/bin/pg_config --libdir` -name pg_stat_statements.so
/usr/pgsql-10/lib/pg_stat_statements.so

pg_stat_statementsを有効にする

pg_stat_statementsが利用可能な拡張（Extension)であることを確認する。
以下のように表示されれば有効。ただし、installed_versionが空欄なので、まだインストールはされていない状態。

$ sudo -u postgres /usr/pgsql-10/bin/psql -U postgres
psql (10.10)
Type "help" for help.

postgres=# \x
postgres=# select * from pg_available_extensions where name = 'pg_stat_statements';
-[ RECORD 1 ]-----+----------------------------------------------------------
name              | pg_stat_statements
default_version   | 1.6
installed_version |
comment           | track execution statistics of all SQL statements executed

pg_stat_statementsを有効にする。

postgres=# CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION

ちなみに削除する場合は DROP EXTENSION pg_stat_statements;

installed_versionにversionがinsertされ、モジュールが有効となった。

postgres=# select * from pg_available_extensions where name = 'pg_stat_statements';
-[ RECORD 1 ]-----+----------------------------------------------------------
name              | pg_stat_statements
default_version   | 1.6
installed_version | 1.6
comment           | track execution statistics of all SQL statements executed

postgresql.confを書き換える

$ sudo find / -name postgresql.conf
/var/lib/pgsql/10/data/postgresql.conf

/var/lib/pgsql/10/data/postgresql.confを以下のように書き換える。

/var/lib/pgsql/10/data/postgresql.conf

-#shared_preload_libraries = ''          # (change requires restart)
+shared_preload_libraries = 'pg_stat_statements'

postgresのrestart

$ sudo systemctl restart postgresql-10

これで設定は完了。

slow queryを調べる

以下のSQLで調べることができる。

postgres=# \x
postgres=# SELECT * FROM pg_stat_statements order by total_time desc limit 1;
-[ RECORD 1 ]-------+------------------------------------------------------
userid              | 10
dbid                | 13808
queryid             | 3494477430
query               | select * from pg_available_extensions where name = $1
calls               | 1
total_time          | 1.105203
min_time            | 1.105203
max_time            | 1.105203
mean_time           | 1.105203
stddev_time         | 0
rows                | 1
shared_blks_hit     | 0
shared_blks_read    | 1
shared_blks_dirtied | 0
shared_blks_written | 0
local_blks_hit      | 0
local_blks_read     | 0
local_blks_dirtied  | 0
local_blks_written  | 0
temp_blks_read      | 0
temp_blks_written   | 0
blk_read_time       | 0
blk_write_time      | 0

Column	Desc
userid	クエリを実行したユーザのID
dbid	クエリが実行されたDBのID
query	実行されたクエリ
calls	実行回数
total_time	クエリ実行にかかった総時間（秒単位）
rows	クエリによって影響を受けたレコードの総数
avg	1つのクエリが実行された平均秒数

参考

• pg_stat_statementsインストール・設定編