概要
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を以下のように書き換える。
-#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つのクエリが実行された平均秒数 |