PostgreSQL nonremovable row versions message

测试环境: 存在两个事务,: 事务A，事务B 事务A先启动,事务B后启动,表结构 rmt_rescue=> create table tbl_test(id int,name text); CREATE TABLE rmt_rescue=> create table tbl_test1(id int,name text); CREATE TABLE 事务A： rmt_rescue=> begin ; BEGIN rmt_rescue=> insert into tbl_test select generate_series(1,10000),'test' ; INSERT 0 10000 事务B： rmt_rescue=> insert into tbl_test1 select generate_series(1,'digoal'; INSERT 0 10000 Time: 43.269 ms rmt_rescue=> analyze tbl_test1; ANALYZE Time: 3.198 ms rmt_rescue=> select pg_relation_size('tbl_test1'); pg_relation_size ------------------ 450560 (1 row) Time: 0.322 ms rmt_rescue=> delete from tbl_test1; DELETE 10000 Time: 8.925 ms rmt_rescue=> vacuum analyze verbose tbl_test1; INFO: vacuuming "rmt_rescue.tbl_test1" INFO: index "idx_test1" now contains 10000 row versions in 30 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted,0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "tbl_test1": found 0 removable,10000 nonremovable row versions in 55 out of 55 pages DETAIL: 10000 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: vacuuming "pg_toast.pg_toast_2473792" INFO: index "pg_toast_2473792_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 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pg_toast_2473792": found 0 removable,0 nonremovable row versions in 0 out of 0 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: analyzing "rmt_rescue.tbl_test1" INFO: "tbl_test1": scanned 55 of 55 pages,containing 0 live rows and 10000 dead rows; 0 rows in sample,0 estimated total rows VACUUM Time: 13.408 ms 事务B，出现了不可移除的行版本. rmt_rescue=> vacuum full analyze verbose tbl_test1; INFO: vacuuming "rmt_rescue.tbl_test1" INFO: analyzing "rmt_rescue.tbl_test1" INFO: "tbl_test1": scanned 55 of 55 pages,0 estimated total rows VACUUM Time: 185.097 ms 事务B，VACUUM FULL ANALYZE并没有回收这些PAGE。 rmt_rescue=> select pg_relation_size('tbl_test1'); pg_relation_size ------------------ 450560 (1 row) 事务A，（经测试rollback和commit都一样,只要结束事务） rmt_rescue=> rollback; ROLLBACK 事务B， rmt_rescue=> vacuum analyze verbose tbl_test1; INFO: vacuuming "rmt_rescue.tbl_test1" INFO: scanned index "idx_test1" to remove 10000 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "tbl_test1": removed 10000 row versions in 55 pages DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "idx_test1" now contains 0 row versions in 30 pages DETAIL: 10000 index row versions were removed. 27 index pages have been deleted,0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "tbl_test1": found 10000 removable,0 nonremovable row versions in 55 out of 55 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "tbl_test1": truncated 55 to 0 pages DETAIL: CPU 0.00s/0.01u sec elapsed 0.01 sec. INFO: vacuuming "pg_toast.pg_toast_2473792" INFO: index "pg_toast_2473792_index" now contains 0 row versions in 1 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted,0 nonremovable row versions in 0 out of 0 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: analyzing "rmt_rescue.tbl_test1" INFO: "tbl_test1": scanned 0 of 0 pages,containing 0 live rows and 0 dead rows; 0 rows in sample,0 estimated total rows VACUUM Time: 35.836 ms rmt_rescue=> select pg_relation_size('tbl_test1'); pg_relation_size ------------------ 0 (1 row) 页面被回收。 因此: 当数据库的UPDATE,DELETE操作较为频繁时不要做长事务的操作，因为在此时间段内的其他表不能回收垃圾空间。将造成数据表的膨胀效应。 如果发生了膨胀效应，应在业务低谷时使用VACUUM FULL回收垃圾空间。 紧急处理: 如果事务实在是太长了,并且还在继续，同时其他的表已经膨胀到影响性能的情况下，可以建个临时表,truncate来回收垃圾空间. repo=> select pg_relation_size('tbl_test1'); pg_relation_size ------------------ 892928 (1 row) repo=> begin; BEGIN repo=> lock table tbl_test1 in exclusive mode; LOCK TABLE repo=> create table tbl_test2 (like tbl_test1); CREATE TABLE repo=> insert into tbl_test2 select * from tbl_Test1; INSERT 0 10000 repo=> truncate table tbl_test1; TRUNCATE TABLE repo=> insert into tbl_Test1 select * from tbl_test2; INSERT 0 10000 repo=> commit; COMMIT repo=> select pg_relation_size('tbl_test1'); pg_relation_size ------------------ 450560 (1 row) 总结 ： 1. 当某个事物中带有DML(insert,update,delete),select 除外,已经其他对数据库有更改的操作；在这个事务处理过程中，vacuum都无法回收FREE 空间。 2. SELECT在数据库端处理过程中(返回结果集阶段不算在内),也会对vacuum回收FREE 空间有堵塞。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!