自動VACUUMの状況を確認する

# はじめに

自動VACUUMについてのメモ。
対象はPostgreSQL 11です。


# 自動VACUUM

PostgreSQLは追記型のアーキテクチャを採用しているため、不要な領域を解放し再利用できるようにするためにVACUUMという機能があります。
基本は自動VACUUM（auto vacuum）で解放されるため、手動でVACUUMを実行することは少ないです。

自動VACUUMがVACUUMを実行するかどうかは、autovacuum_vacuum_threshold, autovacuum_vacuum_scale_factorパラメータの値を利用して決定されます。

- 不要レコード数（タプル数）　＞　autovacuum_vacuum_threshold + (レコード数 * autovacuum_vacuum_scale_factor)

autovacuum_vacuum_thresholdはデフォルトで"50"レコードです。
autovacuum_vacuum_scale_factorはデフォルトで"20"パーセントです。

レコード数が5000の場合は、以下のようになります。

- 50 + (5000 * 0.2) = 1050

つまり、1050レコードを超えると自動VACUUMが実行されることになります。
ここで"5000"（レコード数）はpg_classのreltuplesを参照します。不要レコード数はpg_stat_all_tablesのn_dead_tupを参照します。

1000万レコードだと、200万レコード以上変更が発生しないと自動VACUUMが実行されないことになるので、テーブルごとに変更した方が良いかも（今度考えてみよう）。


# 自動VACUUM（AUTO VACUUM）を有効にする設定

自動VACUUMはデフォルトで有効になっています。
明示的に設定するなら、postgresql.confを以下のように設定します。

```
autovacuum = on
```


# 設定した時間以上かかった自動VACUUMの情報をログ出力する設定

デフォルトでは-1になっており、自動VACUUMの情報はログへ出力されません。
例えば、5000ms以上かかった自動VACUUMの情報を出力するように設定するには、postgresql.confを以下のように修正します。

```
log_autovacuum_min_duration = 5000
```

ログファイルには以下のように出力されます。

```
2020-01-11 14:10:30.891 CET [14512] LOG:  automatic vacuum of table "testdb.public.pgbench_accounts": index scans: 1
        pages: 16586 removed, 0 remain, 0 skipped due to pins, 0 skipped frozen
        tuples: 1000000 removed, 0 remain, 0 are dead but not yet removable, oldest xmin: 852689
        buffer usage: 72556 hits, 13013 misses, 22281 dirtied
        avg read rate: 1.294 MB/s, avg write rate: 2.215 MB/s
        system usage: CPU: user: 1.09 s, system: 0.18 s, elapsed: 78.59 s
```


# VACUUMの進捗状況を確認する

VACUUMの進捗状況を確認します。VACUUM FULLはサポートされません。

```
# select
  v.pid,
  v.datname,
  c.relname,
  v.phase,
  v.heap_blks_total,
  v.heap_blks_scanned,
  v.heap_blks_vacuumed,
  v.index_vacuum_count,
  v.max_dead_tuples,
  v.num_dead_tuples
from
  pg_stat_progress_vacuum as v
join
  pg_class as c
on v.relid = c.relfilenode;
```


# 自動VACUUMの発生回数、発生時間を確認する

pg_stat_all_tablesビューで自動VACUUMの発生回数、発生時間を確認することができます。

- autovacuum_count: 自動VACUUMが実行された回数
- last_autovacuum: 最後に自動VACUUMが実行された時間
- autoanalyze_count: 自動VACUUMによってANALYZEが実行された回数


```sql
# SELECT * FROM pg_stat_all_tables WHERE relname = 'pgbench_accounts';

-[ RECORD 1 ]-------+------------------------------
relid               | 429226
schemaname          | public
relname             | pgbench_accounts
seq_scan            | 3
seq_tup_read        | 2000000
idx_scan            | 40000
idx_tup_fetch       | 40000
n_tup_ins           | 1000000
n_tup_upd           | 20000
n_tup_del           | 1000000
n_tup_hot_upd       | 8366
n_live_tup          | 0
n_dead_tup          | 0
n_mod_since_analyze | 0
last_vacuum         | 2020-01-11 14:04:35.749745+01
last_autovacuum     | 2020-01-11 14:10:30.89157+01
last_analyze        | 2020-01-11 14:04:35.859541+01
last_autoanalyze    | 2020-01-11 14:10:30.892868+01
vacuum_count        | 1
autovacuum_count    | 1
analyze_count       | 1
autoanalyze_count   | 1
```


# テーブルごとに自動VACUUMを設定する

自動VACUUMはテーブル毎に設定をOFFにすることができます。
自動VACUUMを指定したタイミングで手動実行したいテーブルではOFFに設定することがあるかと思います。
大量更新の時にだけOFFにするという使い方もあるかも？？

```
testdb=# alter table pgbench_accounts set (autovacuum_enabled = false);
ALTER TABLE
testdb=# alter table pgbench_accounts set (toast.autovacuum_enabled = false);
ALTER TABLE
※ただし、pgbench_accounts はTOASTを使用していません。
```

上ではalter tableで変更していますが、create tableで最初に指定することもできます。

- [CREATE TABLE](https://www.postgresql.jp/document/11/html/sql-createtable.html)


# テーブル確認



```
testdb=# select relname, n_live_tup, n_dead_tup, round(n_dead_tup * 100 / nullif(n_live_tup + n_dead_tup, 0), 2) as ratio, last_autovacuum from pg_stat_user_tables;
     relname      | n_live_tup | n_dead_tup | ratio |        last_autovacuum        
------------------+------------+------------+-------+-------------------------------
 pgbench_accounts |     100000 |          0 |  0.00 | 2020-01-18 01:36:27.898834+01
 pgbench_history  |         10 |          0 |  0.00 | 
 pgbench_branches |         10 |         10 | 50.00 | 
 pgbench_tellers  |        100 |         10 |  9.00 | 
(4 rows)

```

# pg_statsinfoから確認

pg_statsinfoはPostgreSQLの利用統計情報を定期的に収集・蓄積するツール（拡張）です。
Autovacuumの実行結果の履歴が保存され、レポートを出力することができます。

レポート以外に以下のSQLで直接Autovacuumの実行結果を確認することができます。

```sql
# Autovacuumの実行履歴
select * from statsrepo.autovacuum where start > '2020-08-26★日付を指定'::timestamp order by start;

# Autovacuumがキャンセルされた実行履歴
select * from statsrepo.autovacuum_cancel order by timestamp;
```


# 参考

- [19.10. 自動Vacuum作業](https://www.postgresql.jp/document/11/html/runtime-config-autovacuum.html)
- [24.1. 定常的なバキューム作業](https://www.postgresql.jp/document/11/html/routine-vacuuming.html)