PostgreSQLのVACUUMを整理する - ぼくは明日、昨日のじぶんに頼りたい

以下の動画をみてVACUUMのこと何も知らないなと思ったので個人的に整理しました。

www.youtube.com

www.techscore.com

上の記事を参考にハンズオン形式で整理したいと思います。

テストデータの準備
- テスト用のテーブルを作成
- テーブルの実ファイルの確認
1行INSERT
- テーブルの実ファイルの確認
- SELECT
1000万件INSERT
- テーブルの実ファイルの確認
- SELECT
1000万件DELETE
再度1000万件INSERT
autovacuum_vacuum_scale_factorに関して
- テーブルごとにautovacuum_vacuum_scale_factorを調整
VACUMM FULL
終わりに

version

PostgreSQL 14.2 (Debian 14.2-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit

postgresql.conf

log_autovacuum_min_duration = 0
autovacuum_vacuum_scale_factor = 0.2

テストデータの準備

テスト用のテーブルを作成

CREATE TABLE sample
(
  id serial NOT NULL,
  name text,
  create_date timestamp without time zone NOT NULL
);

テーブルの実ファイルの確認

$ psql -U postgres -d sample
sample=# SELECT datid, datname FROM pg_stat_database WHERE datname = 'sample';
-[ RECORD 1 ]---
datid   | 16384
datname | sample
sample=# SELECT relfilenode,relname FROM pg_class WHERE relname = 'sample';
-[ RECORD 1 ]-------
relfilenode | 16386
relname     | sample
sample=# exit
$ ls -lh $PGDATA/base/16384/16386*
-rw------- 1 postgres postgres 0 May  1 12:55 /var/lib/postgresql/data/base/16384/16386

ファイルサイズは0です。

1行INSERT

INSERT INTO sample(name, create_date )
VALUES (
  'abc',
  -- 2022/4/1～2023/3/31でランダムを期待している
  ('2022-04-01 00:00:00'::timestamp + trunc(random() * 365) * '1 day'::interval + trunc(random() * 24) * '1 hour'::interval)
);

テーブルの実ファイルの確認

$ ls -lh $PGDATA/base/16384/16386*
-rw------- 1 postgres postgres 8.0K May  1 13:01 /var/lib/postgresql/data/base/16384/16386

ファイルサイズが8KBとなりました。SELECTしてみます。

SELECT

sample=# EXPLAIN ANALYZE SELECT * FROM sample WHERE id = 1;
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Seq Scan on sample  (cost=0.00..24.12 rows=6 width=44) (actual time=0.020..0.021 rows=1 loops=1) 
   Filter: (id = 1)
 Planning Time: 0.188 ms
 Execution Time: 0.042 ms
(4 rows)

1000万件INSERT

INSERT INTO sample(name, create_date )
SELECT
md5(clock_timestamp()::TEXT),
-- 2022/4/1～2023/3/31でランダムを期待している
('2022-04-01 00:00:00'::timestamp + trunc(random() * 365) * '1 day'::interval + trunc(random() * 24) * '1 hour'::interval)
FROM generate_series(1,10000000);

テーブルの実ファイルの確認

$ ls -lh $PGDATA/base/16384/16386*
-rw------- 1 postgres postgres 731M May  1 13:10 /var/lib/postgresql/data/base/16384/16386    
-rw------- 1 postgres postgres 200K May  1 13:08 /var/lib/postgresql/data/base/16384/16386_fsm
-rw------- 1 postgres postgres  24K May  1 13:10 /var/lib/postgresql/data/base/16384/16386_vm

700MBを超えました。空き領域マップ(fsm)、可視性マップ(vm)のファイルも作成されました。

68.1. データベースファイルのレイアウト

SELECT

sample=# EXPLAIN ANALYZE SELECT * FROM sample WHERE id = 1;
                                                       QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..146541.38 rows=1 width=45) (actual time=3.516..296.051 rows=1 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on sample  (cost=0.00..145541.28 rows=1 width=45) (actual time=167.849..260.686 rows=0 loops=3)
         Filter: (id = 1)
         Rows Removed by Filter: 3333333
 Planning Time: 0.366 ms
 JIT:
   Functions: 6
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 1.252 ms, Inlining 0.000 ms, Optimization 0.758 ms, Emission 8.043 ms, Total 10.054 ms
 Execution Time: 296.749 ms
(12 rows)

1000万件DELETE

idが1のもの1件だけを残して削除します。

sample=# DELETE FROM sample WHERE id != 1;
DELETE 10000000
sample=# SELECT COUNT(*) FROM sample;
-[ RECORD 1 ]-
count | 1
$ ls -lh $PGDATA/base/16384/16386*
-rw------- 1 postgres postgres 8.0K May  1 13:56 /var/lib/postgresql/data/base/16384/16386    
-rw------- 1 postgres postgres  24K May  1 13:56 /var/lib/postgresql/data/base/16384/16386_fsm
-rw------- 1 postgres postgres 8.0K May  1 13:56 /var/lib/postgresql/data/base/16384/16386_vm

データ削除で、ファイルサイズは変わらないはずでしたが変わりました。参考記事の下の方に書いてる「VACUUMの例外ケース」です。

2022-05-01 13:56:57.315 JST [221] LOG:  automatic vacuum of table "sample.public.sample": index scans: 
0
        pages: 93457 removed, 1 remain, 0 skipped due to pins, 0 skipped frozen
        tuples: 4774018 removed, 1 remain, 0 are dead but not yet removable, oldest xmin: 741
        index scan not needed: 93458 pages from table (100.00% of total) had 10000000 dead item identifiers removed
        avg read rate: 30.876 MB/s, avg write rate: 15.100 MB/s
        buffer usage: 135949 hits, 144514 misses, 70674 dirtied
        WAL usage: 231536 records, 44621 full page images, 52825589 bytes
        system usage: CPU: user: 1.53 s, system: 1.32 s, elapsed: 36.56 s

あらためて1000万件INSERT、1件残しのDELETE

sample=# INSERT INTO sample(name, create_date) SELECT md5(clock_timestamp()::TEXT), ('2022-04-01 00:00:00'::timestamp + trunc(random() * 365) * '1 day'::interval + trunc(random() * 24) * '1 hour'::interval) FROM generate_series(1,10000000);
INSERT 0 10000000
$ ls -lh $PGDATA/base/16384/16386*
-rw------- 1 postgres postgres 731M May  1 17:10 /var/lib/postgresql/data/base/16384/16386
-rw------- 1 postgres postgres 200K May  1 17:10 /var/lib/postgresql/data/base/16384/16386_fsm
-rw------- 1 postgres postgres  24K May  1 17:10 /var/lib/postgresql/data/base/16384/16386_vm
sample=# DELETE FROM sample WHERE id != 30000001;
DELETE 9999999
sample=# SELECT COUNT(*) FROM sample;
-[ RECORD 1 ]-
count | 1
$ ls -lh $PGDATA/base/16384/16386*
-rw------- 1 postgres postgres 731M May  1 17:33 /var/lib/postgresql/data/base/16384/16386
-rw------- 1 postgres postgres 200K May  1 17:32 /var/lib/postgresql/data/base/16384/16386_fsm
-rw------- 1 postgres postgres  24K May  1 17:33 /var/lib/postgresql/data/base/16384/16386_vm

データ削除しましたがファイルサイズは変わりませんでした。期待した状況です。

自動VACUUMの確認

YouTubeの話になりますが、約1000万件削除したので「autovacuum_vacuum_scale_factor = 0.2」等により閾値を超えて自動VACUUMが動作していました。

ログで気にする項目

index scans: 1以外はワークメモリ不足してるので確認する
- このテーブルはインデックスがないので「index scans: 0」ということかと思います
tuples: 削除できない不要行増えてないか
- tuples: 9999999 removed なので問題なし

2022-05-01 17:33:10.474 JST [334] LOG:  automatic vacuum of table "sample.public.sample": index scans: 0
        pages: 0 removed, 93458 remain, 1 skipped due to pins, 0 skipped frozen
        tuples: 2858184 removed, 108 remain, 0 are dead but not yet removable, oldest xmin: 750
        index scan not needed: 93457 pages from table (100.00% of total) had 9999892 dead item identifiers removed
        avg read rate: 15.174 MB/s, avg write rate: 16.528 MB/s
        buffer usage: 113394 hits, 73608 misses, 80177 dirtied
        WAL usage: 213627 records, 26716 full page images, 43675683 bytes
        system usage: CPU: user: 1.57 s, system: 1.00 s, elapsed: 37.89 s
2022-05-01 17:33:11.128 JST [334] LOG:  automatic analyze of table "sample.public.sample"
        avg read rate: 298.308 MB/s, avg write rate: 0.060 MB/s
        buffer usage: 5312 hits, 24972 misses, 5 dirtied
        system usage: CPU: user: 0.01 s, system: 0.05 s, elapsed: 0.65 s
2022-05-01 17:33:32.600 JST [340] LOG:  automatic vacuum of table "sample.public.sample": index scans: 0
        pages: 0 removed, 93458 remain, 0 skipped due to pins, 93456 skipped frozen
        tuples: 0 removed, 4 remain, 0 are dead but not yet removable, oldest xmin: 751
        index scan not needed: 1 pages from table (0.00% of total) had 107 dead item identifiers removed
        avg read rate: 2.230 MB/s, avg write rate: 2.230 MB/s
        buffer usage: 89 hits, 1 misses, 1 dirtied
        WAL usage: 3 records, 0 full page images, 509 bytes
        system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s

pg_stat{all,sys,user}tables 統計情報ビューを確認

28.2. 統計情報コレクタ

以下が気にするポイントだとYouTubeで話していました。

n_dead_tup(不要行数)が定常的に増えていないか
定期的に自動VACUUMMは行われているか

sample=# SELECT relname, n_tup_ins, n_tup_del, n_live_tup, n_dead_tup, last_autovacuum, autovacuum_count FROM pg_catalog.pg_stat_user_tables WHERE relname = 'sample';
-[ RECORD 1 ]----+------------------------------
relname          | sample
n_tup_ins        | 30000001
n_tup_del        | 39235147
n_live_tup       | 4
n_dead_tup       | 0
last_autovacuum  | 2022-05-01 17:33:32.599949+09
autovacuum_count | 7

あと、pg_stat_progress_vacuum 統計情報ビューでVACUUMの処理フェーズを確認できるとのことでした。

28.4. 進捗状況のレポート

ヒープ=テーブルのデータ部分とのこと
vacuuming indexesでmaintenance_work_memが不足しているとバキューム実行ごとに複数回発生する可能性があるというのが「index scans: 1」を確認することにつながるみたいです。
YouTubeの話しでは、不要行を1行回収するのに必要なメモリは6バイト、デフォルトの64MBでも約1000万行回収できるとのことでした。

定期的に自動VACUUMMは行われているかに関しては、競合するロックが要求されると自動VACUUMは自動的にキャンセルされるのでログに同じテーブルのキャンセルが続いていないか確認するとYouTubeで話してました。

ERROR: canceling autovacuum task
CONTEXT: automatic vacuum of table "xxxxxxxx"

再度1000万件INSERT

参考記事とは異なり自動VACUUMで不要領域が回収されているので、ここではファイルサイズは変わらないはずです。

sample=# INSERT INTO sample(name, create_date) SELECT md5(clock_timestamp()::TEXT), ('2022-04-01 00:00:00'::timestamp + trunc(random() * 365) * '1 day'::interval + trunc(random() * 24) * '1 hour'::interval) FROM generate_series(1,10000000);
INSERT 0 10000000
sample=# SELECT COUNT(*) FROM sample;
  count   
----------
 10000001
(1 row)

$ ls -lh $PGDATA/base/16384/16386*
-rw------- 1 postgres postgres 731M May  1 17:54 /var/lib/postgresql/data/base/16384/16386    
-rw------- 1 postgres postgres 200K May  1 17:53 /var/lib/postgresql/data/base/16384/16386_fsm
-rw------- 1 postgres postgres  24K May  1 17:53 /var/lib/postgresql/data/base/16384/16386_vm 

sample=# SELECT relname, n_tup_ins, n_tup_del, n_live_tup, n_dead_tup, last_autovacuum, autovacuum_count FROM pg_catalog.pg_stat_user_tables WHERE relname = 'sample';
-[ RECORD 1 ]----+------------------------------
relname          | sample
n_tup_ins        | 40000001
n_tup_del        | 39235147
n_live_tup       | 10000006
n_dead_tup       | 0
last_autovacuum  | 2022-05-01 17:53:55.123102+09
autovacuum_count | 8

sample=# EXPLAIN ANALYZE SELECT * FROM sample WHERE id = 39234530;
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------        
 Gather  (cost=1000.00..146541.46 rows=1 width=45) (actual time=7.130..882.356 rows=1 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on sample  (cost=0.00..145541.36 rows=1 width=45) (actual time=494.259..783.931 rows=0 loops=3)
         Filter: (id = 39234530)
         Rows Removed by Filter: 3333333
 Planning Time: 0.195 ms
 JIT:
   Functions: 6
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 2.756 ms, Inlining 0.000 ms, Optimization 1.743 ms, Emission 20.110 ms, Total 24.609 ms
 Execution Time: 883.301 ms
(12 rows)

ファイルサイズに変わりはありませんでした。pg_stat_user_tables の値は期待したものと微妙に一致はしないものなのか。（統計情報更新されてないからか）
「VACUUM FULL」をやる前にいくつか試します。

autovacuum_vacuum_scale_factorに関して

19.10. 自動Vacuum作業

現状はpostgresql.confで「autovacuum_vacuum_scale_factor = 0.2」と設定しています。

テーブルごとにautovacuum_vacuum_scale_factorを調整

24.1. 定常的なバキューム作業

「バキューム閾値 = バキューム基礎閾値 + バキューム規模係数 * タプル数」

バキューム基礎閾値(autovacuum_vacuum_threshold=50)
バキューム規模係数(autovacuum_vacuum_scale_factor)
タプル数(pg_class.reltuples)

sample=# SELECT reltuples FROM pg_catalog.pg_class WHERE relname = 'sample';
   reltuples   
---------------
 1.0000006e+07

デフォルトのバキューム規模係数は「autovacuum_vacuum_scale_factor = 0.2」なので、少なめに設定して動作を確認します。

ALTER TABLE sample
  SET (autovacuum_vacuum_scale_factor = 0.001, autovacuum_analyze_scale_factor = 0.001)

10,050(バキューム閾値) = 50(バキューム基礎閾値) + 0.001(バキューム規模係数) * 1000万(タプル数)

sample=# SELECT COUNT(*) FROM sample;
  count   
----------
 10000001
(1 row)

sample=# DELETE FROM sample WHERE id IN (SELECT id FROM sample LIMIT 10000);
DELETE 10000

1万削除で自動VACUUMが実行されました。
autovacuum_vacuum_scale_factor に関しては定期的にデータ量を確認してその時々の適切な値を指定するのが正しい運用ということですね。

VACUMM FULL

VACUMM FULLを行って、ファイルサイズが小さくなることを確認します。
※ 手順を色々と失敗したので仕切り直しています。

sample=# SELECT COUNT(*) FROM sample;
-[ RECORD 1 ]-
count | 912000
sample=# SELECT relname, n_tup_ins, n_tup_del, n_live_tup, n_dead_tup, last_autovacuum, autovacuum_count FROM pg_catalog.pg_stat_user_tables WHERE relname = 'sample';
-[ RECORD 1 ]----+-----------------------------
relname          | sample
n_tup_ins        | 50000001
n_tup_del        | 66575055
n_live_tup       | 891153
n_dead_tup       | 0
last_autovacuum  | 2022-05-01 21:16:31.92196+09
autovacuum_count | 25

sample=# SELECT datid, datname FROM pg_stat_database WHERE datname = 'sample';
-[ RECORD 1 ]---
datid   | 16384
datname | sample
sample=# SELECT relfilenode,relname FROM pg_class WHERE relname = 'sample';
-[ RECORD 1 ]-------
relfilenode | 16416
relname     | sample

$ ls -lh $PGDATA/base/16384/16416*
-rw------- 1 postgres postgres 797M May  1 21:17 /var/lib/postgresql/data/base/16384/16416
-rw------- 1 postgres postgres 224K May  1 21:15 /var/lib/postgresql/data/base/16384/16416_fsm
-rw------- 1 postgres postgres  32K May  1 21:15 /var/lib/postgresql/data/base/16384/16416_vm

# ここでsampleのバックアップを取得

sample=# VACUUM FULL sample;
VACUUM
sample=# SELECT relfilenode,relname FROM pg_class WHERE relname = 'sample';
-[ RECORD 1 ]-------
relfilenode | 16436
relname     | sample

$ ls -lh $PGDATA/base/16384/16436*
-rw------- 1 postgres postgres 67M May  1 21:24 /var/lib/postgresql/data/base/16384/16436

sample=# SELECT relname, n_tup_ins, n_tup_del, n_live_tup, n_dead_tup, last_autovacuum, autovacuum_count FROM pg_catalog.pg_stat_user_tables WHERE relname = 'sample';
-[ RECORD 1 ]----+-----------------------------
relname          | sample
n_tup_ins        | 50000001
n_tup_del        | 66575055
n_live_tup       | 891153
n_dead_tup       | 0
last_autovacuum  | 2022-05-01 21:16:31.92196+09
autovacuum_count | 25

sample=# EXPLAIN ANALYZE SELECT * FROM sample WHERE id = 49088002;
                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------      
 Gather  (cost=1000.00..14274.10 rows=1 width=45) (actual time=0.185..46.649 rows=1 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on sample  (cost=0.00..13274.00 rows=1 width=45) (actual time=11.008..25.856 rows=0 loops=3)       
         Filter: (id = 49088002)
         Rows Removed by Filter: 304000
 Planning Time: 0.044 ms
 Execution Time: 46.666 ms
(8 rows)

VACUUM FULLによりファイルサイズが小さくなることを確認できました。
実は気になっていたので、論理バックアップ・リストアした場合にリストア先の状況がどうなっているのかを念のため確認します。

sample2データベースを作成してリストアします。論理バックアップはVACUUM FULL前に取得していました。

$ psql -U postgres -d sample2
sample2=# SELECT datid, datname FROM pg_stat_database WHERE datname = 'sample2';
-[ RECORD 1 ]----
datid   | 16441
datname | sample2

sample2=# SELECT relfilenode,relname FROM pg_class WHERE relname = 'sample';
-[ RECORD 1 ]-------
relfilenode | 16442
relname     | sample

$ ls -lh $PGDATA/base/16441/16442*
-rw------- 1 postgres postgres  67M May  1 21:30 /var/lib/postgresql/data/base/16441/16442
-rw------- 1 postgres postgres  40K May  1 21:29 /var/lib/postgresql/data/base/16441/16442_fsm
-rw------- 1 postgres postgres 8.0K May  1 21:30 /var/lib/postgresql/data/base/16441/16442_vm

sample2=# SELECT relname, n_tup_ins, n_tup_del, n_live_tup, n_dead_tup, last_autovacuum, autovacuum_count FROM pg_catalog.pg_stat_user_tables WHERE relname = 'sample';
-[ RECORD 1 ]----+------------------------------
relname          | sample
n_tup_ins        | 912000
n_tup_del        | 0
n_live_tup       | 912000
n_dead_tup       | 0
last_autovacuum  | 2022-05-01 21:30:39.399737+09
autovacuum_count | 1

不要領域は含まれないファイルサイズでした。また、n_tup_insやlast_autovacuum、autovacuum_countの値も初期化されていました。

終わりに

長くなりましたが、ドキュメント読むよりは体感的に動作が理解できました。
VACUUMは不要領域を再利用可能な状態に、VACUUM FULLはファイルサイズも小さくなります。ただ気軽にVACUMM FULLはできないという話。

GitHub - reorg/pg_repack: Reorganize tables in PostgreSQL databases with minimal locks