PostgreSQL12で使いたいモジュール（pg_prewarm/pg_stat_statements/auto_explain/pg_repack/pgstattuple）

# はじめに

PostgreSQL12で使いたい以下のモジュールのインストール・設定方法についてのメモです。

- pg_prewarm
- pg_stat_statements
- pg_repack
- auto_explain
- pg_store_plans
- pgstattuple

※pg_store_plansを入れれば、auto_explainは必要ないかもしれません。

以下については使用したかったのですが、PostgreSQL12がまだリリースされていなかったため、リリースした後に追記します。

- pg_hint_plan（REL12-1.3.5で対応済み）
- pg_statsinfo（2020/2/5に対応済み）
- pg_stats_reporter（2020/2/5に対応済み）


# auto_explain

auto_explainモジュールは、自動的に遅いSQLの実行計画をログへ出力することができます。

使用するためにはpostgresql.confを以下のように変更します。設定パラメータは他にもあるので公式サイトを参照してください。

```
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration=10000
```

auto_explain.log_min_durationに設定した値（ms）よりも時間がかかったSQLの実行計画を出力します。大量のログが出力されないように10秒などを設定します。
ログの出力は以下のとおりです。

```
2019-10-25 14:56:30 CEST [10147]: [3-1] user=postgres, db=testdbLOG:  duration: 0.069 ms  plan:
        Query Text: SELECT abalance FROM pgbench_accounts WHERE aid = 900347;
        Index Scan using pgbench_accounts_pkey on pgbench_accounts  (cost=0.42..8.44 rows=1 width=4)
          Index Cond: (aid = 900347)
```

参考

- [F.4. auto_explain](https://www.postgresql.jp/document/11/html/auto-explain.html)
- [PostgreSQL の contrib モジュール “auto_explain” を使ってみた ](https://tech-lab.sios.jp/archives/8865)


# pg_store_plans

pg_store_plansは実行計画ごとに実行統計を取得する機能拡張です。実行計画に関連する統計情報を表示するビュー(pg_store_plansビュー)が利用できます。
また、pg_store_plansを入れておくとpg_stats_reporterのレポート機能で実行計画の統計を出力することができます。


## コンパイル

CentOS7.5でコンパイルしたときのメモです。

モジュールは以下からダウンロードしておきます。

- https://github.com/ossc-db/pg_store_plans

```
# tar xvzf pg_store_plans-1.4.tar.gz
# cd pg_store_plans-1.4
# make USE_PGXS=1
# sudo make USE_PGXS=1 install
```

使用するためにはpostgresql.confを以下のように変更します。変更後、PostgreSQLを再起動します。

```
shared_preload_libraries = 'pg_store_plans'
```

モジュールを使用するためには、以下を実行します。

```
# CREATE EXTENSION pg_repack;
CREATE EXTENSION
```

参考

- [pg_store_plans](https://github.com/ossc-db/pg_store_plans)
- [pg_store_plansを使ってみた(1)](https://qiita.com/nuko_yokohama/items/243e057998220959ffb6)


# pg_stat_statements

pg_stat_statementsモジュールは、SQLの実行時間や回数などを統計情報をデータベースに記録するモジュールです。

使用するためにはpostgresql.confを以下のように変更します。

```
shared_preload_libraries = 'pg_stat_statements'

pg_stat_statements.max = 10000
pg_stat_statements.track = all # またはtop
pg_stat_statements.save = on
```

pg_stat_statements.maxは記録されるSQLの最大数を指定します。デフォルトは5000で、5000を超えると実行回数が少ないSQLが削除されます。

モジュールを使用するためには、以下を実行します。

```
CREATE EXTENSION pg_stat_statements;
```

統計情報が保存されたpg_stat_statementsビューから、実行回数、実行時間等を検索すると以下のようになります。

```
select substr(query, 0, 80) as query, calls
  ,(total_time / 1000)::numeric(10,3) as total_time_sec
  ,(mean_time / 1000)::numeric(10,3) as avg_time_sec
  ,(min_time / 1000)::numeric(10,3) as min_time_sec
 ,(max_time / 1000)::numeric(10,3) as max_time_sec
from pg_stat_statements
order by total_time desc
limit 10;

                                      query                                      | calls | total_time_sec | avg_time_sec | min_time_sec | max_time_sec 
---------------------------------------------------------------------------------+-------+----------------+--------------+--------------+--------------
 UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2             | 20000 |         69.178 |        0.003 |        0.000 |        0.034
 UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2              | 20000 |         55.097 |        0.003 |        0.000 |        0.045
 UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2             | 20000 |          0.632 |        0.000 |        0.000 |        0.005
 SELECT abalance FROM pgbench_accounts WHERE aid = $1                            | 20000 |          0.199 |        0.000 |        0.000 |        0.000
 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, $2, $3, $ | 20000 |          0.161 |        0.000 |        0.000 |        0.002
 copy pgbench_accounts from stdin                                                |     1 |          0.128 |        0.128 |        0.128 |        0.128
 vacuum analyze pgbench_accounts                                                 |     1 |          0.063 |        0.063 |        0.063 |        0.063
 alter table pgbench_accounts add primary key (aid)                              |     1 |          0.058 |        0.058 |        0.058 |        0.058
 CREATE EXTENSION pg_stat_statements                                             |     1 |          0.030 |        0.030 |        0.030 |        0.030
 vacuum pgbench_branches                                                         |     2 |          0.025 |        0.013 |        0.012 |        0.013
(10 rows)
```

蓄積されたデータをいったんリセットする場合は以下のSQLを実行します。

```sql
select pg_stat_statements_reset();
```

参考

- [F.29. pg_stat_statements](https://www.postgresql.jp/document/11/html/pgstatstatements.html)
- [PostgreSQL pg_stat_statementsで統計情報を見てみよう](https://products.sint.co.jp/siob/blog/pg-stat-statements)
- [postgrsqlで実行されたSQLの実行時間や回数などを記録する](https://qiita.com/kite_999/items/ae312952dc5a55bd7cf5)

# pg_repack

pg_repackモジュールは、テーブル・インデックスの再編成をオンラインで実行するためのモジュールです。テーブル・インデックスの再編成は表ロックが長時間かかってしまいますが、pg_repackを利用することにより表ロックがかかる時間が短くなります。

## コンパイル

CentOS7.5でコンパイルしたときのメモです。

モジュールは以下からダウンロードしておきます。

- https://pgxn.org/dist/pg_repack/

```
# yum install -y postgresql12-devel
# yum install zlib-devel
# yum install readline-devel
# yum install openssl-devel

# yum install -y cmake
# yum install -y clang
# yum install -y centos-release-scl
# yum install -y llvm-toolset-7 llvm5.0
```

```
# mkdir -p /opt/rh/llvm-toolset-7/root/usr/bin
# ln -s /usr/bin/clang /opt/rh/llvm-toolset-7/root/usr/bin/clang
# mkdir -p /usr/lib64/llvm5.0/bin/
# ln -s /opt/rh/llvm-toolset-7/root/usr/bin/llvm-lto /usr/lib64/llvm5.0/bin/llvm-lto
```

```
$ export PATH=$PATH:/usr/pgsql-12/bin
$ cd pg_repack
$ make
$ sudo make install
```

```
-bash-4.2$ psql testdb
psql (12.0)
Type "help" for help.

testdb=# CREATE EXTENSION pg_repack;
CREATE EXTENSION
```

データベースtestdbのpgbench_accountsテーブルを再編成する場合は以下のように実行します。

```
$ pg_repack -n -t pgbench_accounts -d testdb
INFO: repacking table "public.pgbench_accounts"
```

参考

- [pg_repack 1.4.5](http://reorg.github.io/pg_repack/)
- [pg_repack (オンラインテーブル再編成ツール)](https://www.sraoss.co.jp/tech-blog/pgsql/pg_repack/)
- [運用で役立つツールpg_repackのご紹介](https://qiita.com/bwtakacy/items/3be7a3e1540ccef7c9e3)
- [RDS Postgresqlのディスク使用量を削減する(pg_repack)](https://dev.classmethod.jp/cloud/aws/rds_postgresql_pg_repack/)
- [pg_repack のインストール](http://a4dosanddos.hatenablog.com/entry/2016/05/06/121708)


# pg_hint_plan

インストールしようとしましたが、11までしか対応していませんでした。リリースされてから記載します。

https://osdn.net/projects/pghintplan/releases/

参考

- [pg_hint_plan (PostgreSQL 実行計画制御ツール)](https://www.sraoss.co.jp/technology/postgresql/3rdparty/pg_hint_plan.php)
- [使ってみませんか？pg_hint_plan](https://www.slideshare.net/hadoopxnttdata/pghintplan)
- [pg_hint_planで実行計画を制御する](https://www.fujitsu.com/jp/products/software/resources/feature-stories/pg-hint-plan/?postgresql-inside)


# pg_prewarm/pg_buffercache

pg_prewarmでは、ユーザが手動でテーブルやインデックスを指定することで共有バッファにキャッシュさせることができpg_buffercacheは共有バッファの状態を表示できます。

使用するためにはpostgresql.confを以下のように変更します。

```
shared_preload_libraries = 'pg_prewarm'
```


```
$ psql -U postgres testdb
testdb=# create extension pg_prewarm;
CREATE EXTENSION
testdb=# create extension pg_buffercache;
CREATE EXTENSION
```

pgbench_accountsテーブルを共有バッファにキャッシュさせる場合は以下のように実行します。

```
testdb=# select pg_prewarm('pgbench_accounts', 'buffer');
 pg_prewarm 
------------
      16693
(1 row)
```

共有バッファの状態を表示するためには、以下のようなSQLを実行します。

```sql
testdb=# select c.relname, count(*) as buffers
from pg_buffercache as b
inner join pg_class as c on b.relfilenode = pg_relation_filenode(c.oid) and b.reldatabase in (0, (select oid from pg_database where datname = current_database()))
group by c.relname
order by 2 desc;

                 relname                 | buffers 
-----------------------------------------+---------
 pgbench_accounts                        |   16693
 pg_proc                                 |      83
 pg_attribute                            |      31
 pg_class                                |      16
 pg_depend_reference_index               |      12
```

参考

- [学習ノート](http://blog.hahasoha.net/article/181923072.html)
- [pg_prewarmと複合indexを用いてPostgreSQLをチューニングし、劇的な改善を実現した話](https://www.forcia.com/blog/001178.html)
- [F.27. pg_prewarm](https://www.postgresql.jp/document/11/html/pgprewarm.html)


# pgstattuple

pgstattupleモジュールは、タプルレベルの統計情報を取得することができます。

モジュールを使用するためには、以下を実行します。

```
CREATE EXTENSION pgstattuple;
```

テーブル情報を取得するためには以下のように実行します。

```
testdb=# select * from pgstattuple('pgbench_accounts');
-[ RECORD 1 ]------+----------
table_len          | 134381568
tuple_count        | 1000000
tuple_len          | 121000000
tuple_percent      | 90.04
dead_tuple_count   | 207
dead_tuple_len     | 25047
dead_tuple_percent | 0.02
free_space         | 1894920
free_percent       | 1.41
```

インデックス情報を取得するためには以下のように実行します。

```
testdb=# select * from pgstatindex('pgbench_accounts_pkey');
-[ RECORD 1 ]------+---------
version            | 4
tree_level         | 2
index_size         | 22487040
root_block_no      | 290
internal_pages     | 11
leaf_pages         | 2733
empty_pages        | 0
deleted_pages      | 0
avg_leaf_density   | 90.08
leaf_fragmentation | 0
```

avg_leaf_densityはリーフページの平均密度（%）です。
leaf_fragmentationはリーフページの断片化の状況で、低い方が良いです。


参考

- [F.30. pgstattuple](https://www.postgresql.jp/document/11/html/pgstattuple.html)
- [pgstattuple関数の使い方](https://tigawa.github.io/blog/2016/11/23/pgstattuple/)