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

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

## 環境
- CentOS 7.5
- postgres (PostgreSQL) 10.10

## postgresql10のインストール
postgresql10をインストールする。

```console
$ 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確認

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

initdb

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

enable, start

```console
$ 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

```console
$ 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用のコマンドを利用する。

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

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

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

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

### postgresql10-contribのインストール

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

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

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

```console
$ 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`が空欄なので、まだインストールはされていない状態。

```sql
$ 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を有効にする。

```sql
postgres=# CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION
```

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

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

```sql
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を以下のように書き換える。

```diff:/var/lib/pgsql/10/data/postgresql.conf
-#shared_preload_libraries = ''          # (change requires restart)
+shared_preload_libraries = 'pg_stat_statements'
```

postgresのrestart

```console
$ sudo systemctl restart postgresql-10
```

これで設定は完了。

## slow queryを調べる
以下のSQLで調べることができる。


```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インストール・設定編](http://dbnote.web.fc2.com/note_install_pg_stats_statements.html)