抓狂!啊，表和索引不断膨胀确找不到原因!
digoal约 8 分钟文中参考文档在github需点击阅读原文打开, 同时推荐2个学习环境:
1、懒人Docker镜像, 已打包200+插件:《最好的PostgreSQL学习镜像》
抓狂! 表和索引不断膨胀确找不到原因!
大概率会和这几个有关: long query、未结束事务、2pc、inactive slot
如果存在只读实例, 并且开启了hot_standby_feedback, 也可能和只读实例上的 long query 有关.
结果就是“autovacuum launcher”触发频率越高, 垃圾无法被回收, 但是判断有足够多的垃圾存在, 所以autovacuum worker会被不断唤醒, 所以这个信息打印越频繁.
接下来分析一下根本原因
普通对象膨胀点
用户创建的表、物化视图、索引等。
哪些垃圾不能被回收？
1、当前数据库中最老事务快照之后产生的垃圾记录
2、年龄小于vacuum_defer_cleanup_age设置的垃圾记录
3、备库开启了feedback后，备库返回的最老事务快照(仅指 global xmin)之后产生的垃圾记录。(catalog xmin无影响)
什么时候可能膨胀？
1、standby 开启了 feedback (且standby有慢事务, LONG SQL),
2、vacuum_defer_cleanup_age 设置太大
3、当前数据库中的 : 长事务, 慢SQL, 未结束2pc,
例子
1、创建slot
postgres=# select pg_create_logical_replication_slot('a','test_decoding');
pg_create_logical_replication_slot
------------------------------------
(a,0/92C9C038)
(1 row)
2、查看slot的位点信息
postgres=# select * from pg_get_replication_slots();
slot_name | plugin | slot_type | datoid | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn
-----------+---------------+-----------+--------+-----------+--------+------------+------+--------------+-------------+---------------------
a | test_decoding | logical | 13585 | f | f | | | 1982645 | 0/92C9BFE8 | 0/92C9C038
(1 row)
3、查看catalog_xmin对应XID的事务提交时间，需要开启事务时间跟踪track_commit_timestamp
postgres=# select pg_xact_commit_timestamp(xmin),pg_xact_commit_timestamp(catalog_xmin) from pg_get_replication_slots();
psql: ERROR: could not get commit timestamp data
HINT: Make sure the configuration parameter "track_commit_timestamp" is set.
4、从RESTART_LSN找到对应WAL文件，从文件中也可以查到大概的时间。
postgres=# select pg_walfile_name(restart_lsn) from pg_get_replication_slots();
pg_walfile_name
--------------------------
000000010000000000000092
(1 row) postgres=# select * from pg_stat_file('pg_wal/000000010000000000000092');
size | access | modification | change | creation | isdir
----------+------------------------+------------------------+------------------------+----------+-------
16777216 | 2019-06-29 22:56:16+08 | 2019-07-01 09:50:16+08 | 2019-07-01 09:50:16+08 | | f
(1 row)
postgres=# select * from pg_ls_waldir() where name='000000010000000000000092';
name | size | modification
--------------------------+----------+------------------------
000000010000000000000092 | 16777216 | 2019-07-01 09:50:16+08
(1 row)
5、建表
postgres=# create table b(id int);
CREATE TABLE
postgres=# insert into b values (1);
INSERT 0 1
6、消费SLOT WAL
postgres=# select * from pg_logical_slot_get_changes('a',pg_current_wal_lsn(),1);
lsn | xid | data
------------+---------+----------------
0/92C9C0C0 | 1982645 | BEGIN 1982645
0/92CA4A40 | 1982645 | COMMIT 1982645
(2 rows) postgres=# select * from pg_logical_slot_get_changes('a',pg_current_wal_lsn(),1);
lsn | xid | data
------------+---------+---------------------------------------
0/92CA4A78 | 1982646 | BEGIN 1982646
0/92CA4A78 | 1982646 | table public.b: INSERT: id[integer]:1
0/92CA4AE8 | 1982646 | COMMIT 1982646
(3 rows)
7、删除记录
postgres=# delete from b;
DELETE 1
8、垃圾回收，正常。本地表垃圾不受slot catalog_xmin影响
postgres=# vacuum verbose b;
psql: INFO: vacuuming "public.b"
psql: INFO: "b": removed 1 row versions in 1 pages
psql: INFO: "b": found 1 removable, 0 nonremovable row versions in 1 out of 1 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 1982648
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
psql: INFO: "b": truncated 1 to 0 pages
DETAIL: CPU: user: 0.09 s, system: 0.00 s, elapsed: 0.09 s
VACUUM
9、建表，删表，使得CATALOG发生变化，产生CATALOG垃圾
postgres=# create table c (id int);
CREATE TABLE
postgres=# drop table c;
DROP TABLE
postgres=# create table c (id int);
CREATE TABLE
postgres=# drop table c;
DROP TABLE
10、垃圾回收catalog，无法回收SLOT后产生的CATALOG垃圾，因为还需要这个CATALOG版本去解析对应WAL的LOGICAL 日志
postgres=# vacuum verbose pg_class;
psql: INFO: vacuuming "pg_catalog.pg_class"
psql: INFO: "pg_class": found 0 removable, 465 nonremovable row versions in 13 out of 13 pages
DETAIL: 2 dead row versions cannot be removed yet, oldest xmin: 1982646
There were 111 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
catalog 受影响
postgres=# vacuum verbose pg_attribute ;
psql: INFO: vacuuming "pg_catalog.pg_attribute"
psql: INFO: "pg_attribute": found 0 removable, 293 nonremovable row versions in 6 out of 62 pages
DETAIL: 14 dead row versions cannot be removed yet, oldest xmin: 1982646
There were 55 unused item identifiers.
Skipped 0 pages due to buffer pins, 55 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
11、长事务不影响其他库的垃圾回收
postgres
postgres=# begin;
BEGIN
postgres=# delete from a;
DELETE 1
db1
db1=# create table b(id int);
CREATE TABLE
db1=# insert into b values (1);
INSERT 0 1
db1=# delete from b;
DELETE 1
db1=# vacuum verbose b;
psql: INFO: vacuuming "public.b"
psql: INFO: "b": removed 1 row versions in 1 pages
psql: INFO: "b": found 1 removable, 0 nonremovable row versions in 1 out of 1 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 1982671
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
psql: INFO: "b": truncated 1 to 0 pages
DETAIL: CPU: user: 0.09 s, system: 0.00 s, elapsed: 0.09 s
VACUUM
小结
1 全局catalog 膨胀点
哪些垃圾不能被回收？
1、年龄小于vacuum_defer_cleanup_age设置的垃圾记录
2、当前实例中最老事务快照之后产生的垃圾记录
3、SLOT catalog_xmin后产生的垃圾记录
4、备库开启了feedback后，备库中最老事务快照(包括catalog_xmin, global xmin)之后产生的垃圾记录
什么时候可能膨胀？
1、vacuum_defer_cleanup_age 设置太大
2、整个实例中的 : 长事务, 慢SQL, 慢2pc,
3、慢/dead slot(catalog_xmin, 影响catalog垃圾回收),
4、standby 开启了 feedback (且standby有慢事务, LONG SQL, 慢/dead slot),
2 库级catalog 膨胀点
哪些垃圾不能被回收？
1、年龄小于vacuum_defer_cleanup_age设置的垃圾记录
2、当前数据库中最老事务快照之后产生的垃圾记录
3、备库开启了feedback后，备库返回的最老事务快照(包括catalog_xmin, global xmin)之后产生的垃圾记录
4、SLOT catalog_xmin后产生的垃圾记录(create table, drop table, pg_class, pg_att等)。影响全局（所有DB）
什么时候可能膨胀？
1、vacuum_defer_cleanup_age 设置太大
2、当前数据库中的 : 长事务, 慢SQL, 慢2pc,
3、standby 开启了 feedback (且standby有慢事务, LONG SQL, 慢/dead slot),
4、慢/dead slot(catalog_xmin, 影响catalog垃圾回收),
普通对象膨胀点
用户创建的表、物化视图、索引等。
哪些垃圾不能被回收？
1、年龄小于vacuum_defer_cleanup_age设置的垃圾记录
2、当前数据库中最老事务快照之后产生的垃圾记录
3、备库开启了feedback后，备库返回的最老事务快照(仅指 global xmin)之后产生的垃圾记录。(catalog xmin无影响)
什么时候可能膨胀？
1、vacuum_defer_cleanup_age 设置太大
2、当前数据库中的 : 长事务, 慢SQL, 慢2pc,
3、standby 开启了 feedback (且standby有慢事务, LONG SQL),
WAL文件 膨胀点
wal是指PG的REDO文件。
哪些WAL不能被回收 或 不能被重复利用？
1、从最后一次已正常结束的检查点(检查点开始时刻, 不是结束时刻)开始，所有的REDO文件都不能被回收
2、归档开启后，所有未归档的REDO。（.ready对应的redo文件）
3、启用SLOT后，还没有被SLOT消费的REDO文件
4、设置wal_keep_segments时，当REDO文件数还没有达到wal_keep_segments个时。
什么时候可能膨胀？
1、archive failed ，归档失败
2、user defined archive BUG，用户开启了归档，但是没有正常的将.ready改成.done，使得WAL堆积
3、wal_keep_segments 设置太大，WAL保留过多
4、max_wal_size设置太大，并且checkpoint_completion_target设置太大，导致检查点跨度很大，保留WAL文件很多
5、slot slow(dead) ，包括(physical | logical replication) ， restart_lsn 开始的所有WAL文件都要被保留
欢迎关注我的github (https://github.com/digoal/blog) , 学习数据库不迷路.
近期正在写公开课材料, 未来将通过视频号推出, 欢迎关注视频号:
文章中的参考文档请点击阅读原文获得.