作者
digoal
日期
2020-07-09
标签
PostgreSQL , xmin , 垃圾回收
背景
当某些vacuum a很慢时, 会不会导致其他vacuum无法回收vacuum a开始后产生的垃圾版本?
测试
```
create table a (ID INT, info text, crt_time timestamp);
alter table a set (autovacuum_enabled =off);
alter table a set (toast.autovacuum_enabled =off);
insert into a select generate_series(1,100000000), md5(RANdom()::text), clock_timestamp();
create table b (id int primary key, info text, crt_time timestamp);
alter table b set (autovacuum_enabled =off);
alter table b set (toast.autovacuum_enabled =off);
insert into b select generate_series(1,100000), md5(RANdom()::text), clock_timestamp();
```
session a:
update b set info='test' where id=1;
delete from a where id<90000000;
set vacuum_cost_delay ='100';
set vacuum_cost_limit=1;
session a:
vacuum verbose a;
... ...
session b:
```
update b set info='test2' where id=2;
update b set info='test2' where id=100000;
update b set info='test2' where id=9999;
select backend_xid,backend_xmin,backend_type,query from pg_stat_activity order by least(backend_xid::text::int8,backend_xmin::text::int8);
backend_xid | backend_xmin | backend_type | query
-------------+--------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------
| 71140608 | client backend | vacuum verbose a;
| 71140611 | client backend | select backend_xid,backend_xmin,backend_type,query from pg_stat_activity order by least(backend_xid::text::int8,backend_xmin::text::int8);
| | logical replication launcher |
| | autovacuum launcher |
| | background writer |
| | checkpointer |
| | walwriter |
(7 rows)
```
vacuum verbose b;
```
postgres=# vacuum verbose b;
INFO: vacuuming "public.b"
INFO: scanned index "b_pkey" to remove 1 row versions
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO: "b": removed 1 row versions in 1 pages
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO: index "b_pkey" now contains 100000 row versions in 276 pages
DETAIL: 1 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: "b": found 5 removable, 276 nonremovable row versions in 3 out of 935 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 71140611
There were 2 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.
INFO: vacuuming "pg_toast.pg_toast_18273"
INFO: index "pg_toast_18273_index" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s.
INFO: "pg_toast_18273": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 71140611
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s.
VACUUM
```
```
vacuum verbose b;
postgres=# vacuum verbose b;
INFO: vacuuming "public.b"
INFO: "b": found 0 removable, 63 nonremovable row versions in 1 out of 935 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 71140611
There were 2 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.
INFO: vacuuming "pg_toast.pg_toast_18273"
INFO: index "pg_toast_18273_index" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: "pg_toast_18273": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 71140611
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.
```
在session a完成垃圾回收前, 虽然vacuum a 有backend_xmin, 但是不影响vacuum b回收backend_xmin之后产生的垃圾版本.
vacuum的backend xmin不会导致其他vacuum无法回收新的垃圾版本.
注意
在计算膨胀点时， 应该过滤autovacuum, vacuum的backend_xmin， 否则可能会误导膨胀点。
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
