ITPub博客

首页 > 数据库 > PostgreSQL > Postgresql验证_update、delete产生死亡元组,标准vacuum释放表文件磁盘空间的场景

Postgresql验证_update、delete产生死亡元组,标准vacuum释放表文件磁盘空间的场景

原创 PostgreSQL 作者:lusklusklusk 时间:2021-09-23 15:25:20 0 删除 编辑

已经验证
1、一张表t1,插入20万行,表文件58425总计大概1GB,再delete这20万行,死亡记录20万行,表文件58425总计大概1GB,再插入20万行,表文件58425总计大概2GB,死亡记录还是20万行,再删除这这20万行,表文件58425总计大概2GB,死亡记录40万行
2、一张表t2,插入20万行,表文件58431总计大概1GB,再update这20万行,死亡记录20万行,表文件58431总计大概2GB
3、一张表t3,插入20万行,表文件58434总计大概1GB,再truncate这表,死亡记录0,表文件58434总计0GB




得出结论:
1、delete都会对老行做一个标记,虽然死亡记录会增加,但是表文件大小并没有增加
2、update对老行做一个标记,再新建一行新行,所以update的时候,死亡记录增加,表文件大小也增加
3、truncate后死亡记录为0,表文件大小也变成0,truncate就是重建表一样,因为表对应的pg_class.relfilenode都变了
4、标准vacuum(不带full)后,表对应的pg_class.relfilenode不变
5、vacuum full 表的情况下,select没法查询表,select会被vacuum full堵塞
6、vacuum full 表会释放表文件对应的磁盘空间,因为表对应的pg_class.relfilenode都变了
7、官方文档说vacuum full会释放磁盘空间,标准VACUUM(即不带FULL)不会,标准VACUUM也能把该表文件空间交还给操作系统的情况:表尾部有空页就能释放这个空页的空间,页包含页头部和行数据,也就是说如果每行数据都是死元组,那么整个这个页就是空闲的,实验场景,当我们insert一张新建的表或全是死元组的表时,insert的时候都是往尾部插入(类似oracle的insert /*+ append */往高水位线以上插入),而一旦delete整表后,则整表的所有页里面都是死元组,直接VACUUM 不带FULL也能把这个页就给回收空间了,见本文的实验“delete后,执行vacuum,表的大小从1GB变成了0KB”





delete的案例t1表
create table t1(hid int, hid1 char(50),hid2 char(50),hid3 char(50),hid4 char(50),hid5 char(50),hid6 char(50),hid7 char(50),hid8 char(50),hid9 char(50),hid10 char(50));

do $$
declare
v_idx integer := 1;
begin
  while v_idx < 2000000 loop
  v_idx = v_idx+1;
insert into t1 values ( v_idx,'eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang');
  end loop;
end $$;


select count(*) from t1;
  count
---------
 1999999
(1 row)


查询文件路径
SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 't1';
            pg_relation_filepath             | relpages
---------------------------------------------+----------
 pg_tblspc/50003/PG_11_201809051/58424/58425 |        0


[root@FRSPGSQLDEV2 ~]# du -sh /var/lib/pgsql/pg/PG_11_201809051/58424/58425*
1.8G    /var/lib/pgsql/pg/PG_11_201809051/58424/58425
93M     /var/lib/pgsql/pg/PG_11_201809051/58424/58425.1
320K    /var/lib/pgsql/pg/PG_11_201809051/58424/58425_fsm


SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables  where relname='t1' ORDER BY n_dead_tup;
 schemaname | relname | n_live_tup | n_dead_tup
------------+---------+------------+------------
 public     | t1      |    2000005 |          0
(1 row)



delete from t1;
DELETE 1999999

select count(*) from t1;
 count
-------
     0
(1 row)


SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables  where relname='t1' ORDER BY n_dead_tup;
 schemaname | relname | n_live_tup | n_dead_tup
------------+---------+------------+------------
 public     | t1      |         13 |    1999999
(1 row)

[root@FRSPGSQLDEV2 ~]# du -sh /var/lib/pgsql/pg/PG_11_201809051/58424/58425*
1.8G    /var/lib/pgsql/pg/PG_11_201809051/58424/58425
93M     /var/lib/pgsql/pg/PG_11_201809051/58424/58425.1
320K    /var/lib/pgsql/pg/PG_11_201809051/58424/58425_fsm
8.0K    /var/lib/pgsql/pg/PG_11_201809051/58424/58425_vm


do $$
declare
v_idx integer := 1;
begin
  while v_idx < 2000000 loop
  v_idx = v_idx+1;
insert into t1 values ( v_idx,'eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang');
  end loop;
end $$;


select count(*) from t1;
  count
---------
 1999999
(1 row)

[root@FRSPGSQLDEV2 ~]# du -sh /var/lib/pgsql/pg/PG_11_201809051/58424/58425*
1.8G    /var/lib/pgsql/pg/PG_11_201809051/58424/58425
1.0G    /var/lib/pgsql/pg/PG_11_201809051/58424/58425.1
185M    /var/lib/pgsql/pg/PG_11_201809051/58424/58425.2
584K    /var/lib/pgsql/pg/PG_11_201809051/58424/58425_fsm
32K     /var/lib/pgsql/pg/PG_11_201809051/58424/58425_vm


SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables  where relname='t1' ORDER BY n_dead_tup;
 schemaname | relname | n_live_tup | n_dead_tup
------------+---------+------------+------------
 public     | t1      |    1999999 |    1999999
(1 row)



delete from t1;
DELETE 1999999

select count(*) from t1;
 count
-------
     0
(1 row)


[root@FRSPGSQLDEV2 ~]# du -sh /var/lib/pgsql/pg/PG_11_201809051/58424/58425*
1.8G    /var/lib/pgsql/pg/PG_11_201809051/58424/58425
1.0G    /var/lib/pgsql/pg/PG_11_201809051/58424/58425.1
185M    /var/lib/pgsql/pg/PG_11_201809051/58424/58425.2
584K    /var/lib/pgsql/pg/PG_11_201809051/58424/58425_fsm
32K     /var/lib/pgsql/pg/PG_11_201809051/58424/58425_vm

SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables  where relname='t1' ORDER BY n_dead_tup;
 schemaname | relname | n_live_tup | n_dead_tup
------------+---------+------------+------------
 public     | t1      |          0 |    3999997
(1 row)







update的案例t2表
create table t2(hid int, hid1 int,hid2 int,hid3 char(50),hid4 char(50),hid5 char(50),hid6 char(50),hid7 char(50),hid8 char(50),hid9 char(50),hid10 char(50));


do $$
declare
v_idx integer := 1;
begin
  while v_idx < 2000000 loop
  v_idx = v_idx+1;
insert into t2 values ( v_idx,v_idx,v_idx,'eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang');
  end loop;
end $$;

select count(*) from t2;
  count
---------
 1999999
(1 row)


查询文件路径
SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 't2';
            pg_relation_filepath             | relpages
---------------------------------------------+----------
 pg_tblspc/50003/PG_11_201809051/58424/58431 |        0

[root@FRSPGSQLDEV2 ~]# du -sh /var/lib/pgsql/pg/PG_11_201809051/58424/58431*
1.3G    /var/lib/pgsql/pg/PG_11_201809051/58424/58431
256K    /var/lib/pgsql/pg/PG_11_201809051/58424/58431_fsm

SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables  where relname='t2' ORDER BY n_dead_tup;
 schemaname | relname | n_live_tup | n_dead_tup
------------+---------+------------+------------
 public     | t2      |    1999999 |          0
(1 row)


update t2 set hid=0;
UPDATE 1999999

[root@FRSPGSQLDEV2 ~]# du -sh /var/lib/pgsql/pg/PG_11_201809051/58424/58431*
1.3G    /var/lib/pgsql/pg/PG_11_201809051/58424/58431
1.2G    /var/lib/pgsql/pg/PG_11_201809051/58424/58431.1
516K    /var/lib/pgsql/pg/PG_11_201809051/58424/58431_fsm

SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables  where relname='t2' ORDER BY n_dead_tup;
 schemaname | relname | n_live_tup | n_dead_tup
------------+---------+------------+------------
 public     | t2      |    2000016 |    1999999
(1 row)









truncate的案例t3表
create table t3(hid int, hid1 char(50),hid2 char(50),hid3 char(50),hid4 char(50),hid5 char(50),hid6 char(50),hid7 char(50),hid8 char(50),hid9 char(50),hid10 char(50));


do $$
declare
v_idx integer := 1;
begin
  while v_idx < 2000000 loop
  v_idx = v_idx+1;
insert into t3 values ( v_idx,v_idx,v_idx,'eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang');
  end loop;
end $$;


select count(*) from t3;
  count
---------
 1999999
(1 row)

SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 't3';
            pg_relation_filepath             | relpages
---------------------------------------------+----------
 pg_tblspc/50003/PG_11_201809051/58424/58434 |   142858
(1 row)



[root@FRSPGSQLDEV2 ~]# du -sh /var/lib/pgsql/pg/PG_11_201809051/58424/58434*
1.0G    /var/lib/pgsql/pg/PG_11_201809051/58424/58434
93M     /var/lib/pgsql/pg/PG_11_201809051/58424/58434.1
304K    /var/lib/pgsql/pg/PG_11_201809051/58424/58434_fsm

SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables  where relname='t3' ORDER BY n_dead_tup;
 schemaname | relname | n_live_tup | n_dead_tup
------------+---------+------------+------------
 public     | t3      |    2000012 |          0
(1 row)


truncate table t3;

[root@FRSPGSQLDEV2 ~]# du -sh /var/lib/pgsql/pg/PG_11_201809051/58424/58434*
0       /var/lib/pgsql/pg/PG_11_201809051/58424/58434

SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables  where relname='t3' ORDER BY n_dead_tup;
 schemaname | relname | n_live_tup | n_dead_tup
------------+---------+------------+------------
 public     | t3      |          0 |          0
(1 row)







delete后,执行vacuum,表的大小从1GB变成了0KB
lukes0818=# SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 't1';
            pg_relation_filepath             | relpages
---------------------------------------------+----------
 pg_tblspc/50003/PG_11_201809051/58424/58425 |   285715
(1 row)

lukes0818=# delete from t1;
DELETE 3999998

[root@FRSPGSQLDEV2 ~]# du -sh /var/lib/pgsql/pg/PG_11_201809051/58424/58425*
1.0G    /var/lib/pgsql/pg/PG_11_201809051/58424/58425
1.1G    /var/lib/pgsql/pg/PG_11_201809051/58424/58425.1
185M    /var/lib/pgsql/pg/PG_11_201809051/58424/58425.2
584K    /var/lib/pgsql/pg/PG_11_201809051/58424/58425_fsm
0       /var/lib/pgsql/pg/PG_11_201809051/58424/58425_vm

lukes0818=# vacuum t1;
VACUUM

[root@FRSPGSQLDEV2 ~]# du -sh /var/lib/pgsql/pg/PG_11_201809051/58424/58425*
0       /var/lib/pgsql/pg/PG_11_201809051/58424/58425
0       /var/lib/pgsql/pg/PG_11_201809051/58424/58425.1
0       /var/lib/pgsql/pg/PG_11_201809051/58424/58425.2
16K     /var/lib/pgsql/pg/PG_11_201809051/58424/58425_fsm
0       /var/lib/pgsql/pg/PG_11_201809051/58424/58425_vm





执行vacuum full的同时,无法执行select,select会被堵塞
会话1
lukes0818=# do $$
declare
v_idx integer := 1;
begin
  while v_idx < 2000000 loop
  v_idx = v_idx+1;
insert into t1 values ( v_idx,'eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang');
  end loop;
end $$;
DO
lukes0818=# delete from t1;
DELETE 1999999
lukes0818=# vacuum full t1;

会话2
lukes0818=# select * from t1 limit 1;

会话3
lukes0818=# select a.locktype,b.datname,a.pid,a.mode,a.granted,regclass(a.relation),regclass(a.classid) from pg_locks a join pg_database b on a.database=b.oid and a.granted<>'t';
 locktype |  datname  |  pid  |      mode       | granted | regclass | regclass
----------+-----------+-------+-----------------+---------+----------+----------
 relation | lukes0818 | 26820 | AccessShareLock | f       | t1       |

lukes0818=# select query from pg_stat_activity where pid=26820;
query      
----------+    
select * from t1 limit 1; | client backend




每次vacuum full都会重新生成relfilenode
lukes0818=# SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 't1';
            pg_relation_filepath             | relpages
---------------------------------------------+----------
 pg_tblspc/50003/PG_11_201809051/58424/58464 |   142858
(1 row)

lukes0818=# vacuum full t1;
VACUUM
lukes0818=# SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 't1';
            pg_relation_filepath             | relpages
---------------------------------------------+----------
 pg_tblspc/50003/PG_11_201809051/58424/58470 |   142858
(1 row)

lukes0818=# truncate table t1;
TRUNCATE TABLE
lukes0818=# SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 't1';
            pg_relation_filepath             | relpages
---------------------------------------------+----------
 pg_tblspc/50003/PG_11_201809051/58424/58476 |        0
(1 row)

lukes0818=# vacuum t1;
VACUUM
lukes0818=# SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 't1';
            pg_relation_filepath             | relpages
---------------------------------------------+----------
 pg_tblspc/50003/PG_11_201809051/58424/58476 |        0
(1 row)

lukes0818=# vacuum full t1;
VACUUM
lukes0818=# SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 't1';
            pg_relation_filepath             | relpages
---------------------------------------------+----------
 pg_tblspc/50003/PG_11_201809051/58424/58480 |        0
(1 row)

lukes0818=# select oid,relname,relfilenode from pg_class WHERE relname = 't1';
  oid  | relname | relfilenode
-------+---------+-------------
 58425 | t1      |       58480
(1 row)


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30126024/viewspace-2793304/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Welcome to Lukes DB HOME。 Oracle OCM、Mysql OCP, 10年DBA工作经验,博客仅记录自己的一个学习过程,不代表完全准确,如有需要,欢迎转载。

注册时间:2015-02-02

  • 博文量
    455
  • 访问量
    886225