资讯

精准传达 • 有效沟通

从品牌网站建设到网络营销策划，从策略到执行的一站式服务

网站建设 >

查看其它板块

PostgreSQLPage页结构解析（4）-执行DML时表占用空间解析

本文介绍了在长事务（开启事务，一直不提交/回滚）的情况下，通过使用pageinspect插件分析Update数据表导致数据表占用空间“暴涨”的原因。

创新互联专注于茂南网站建设服务及定制，我们拥有丰富的企业做网站经验。 热诚为您提供茂南营销型网站建设，茂南网站制作、茂南网页设计、茂南网站官网定制、小程序开发服务，打造茂南网络公司原创品牌,更为您提供茂南网站排名全网营销落地服务。

一、测试场景

使用psql启动会话Session B

testdb=# --------------------------- Session B
testdb=# -- 开启事务
testdb=# begin;
BEGIN
testdb=# 
testdb=# select txid_current();  
 txid_current 
--------------
      1600322
(1 row)

testdb=# -- 创建表&插入100行数据
testdb=# drop table if exists t1;
DROP TABLE
testdb=# create table t1(id int,c1 varchar(50));
CREATE TABLE
testdb=# insert into t1 select generate_series(1,100),'#abcd#';
INSERT 0 100
testdb=# select txid_current();  
 txid_current 
--------------
      1600322
(1 row)

testdb=# select count(*) from t1;
 count 
-------
   100
(1 row)

testdb=# 
testdb=# -- 提交事务
testdb=# end;
COMMIT
testdb=#

开启新的Console创建，使用psql启动会话Session A

testdb=# --------------------------- Session A
testdb=# -- 开启事务
testdb=# begin;
BEGIN
testdb=# 
testdb=# -- 查询当前事务
testdb=# select txid_current();  
 txid_current 
--------------
      1600324
(1 row)

testdb=# 
testdb=# -- do nothing

虽然什么都不做，但Session A仍然可以开启一个事务，在这里这个事务一直不提交。
回到Session B，查看数据表t1的数据：

testdb=# --------------------------- Session B
testdb=# -- 查看数据表
testdb=# select ctid, xmin, xmax, cmin, cmax,id from t1 limit 8;
 ctid  |  xmin   | xmax | cmin | cmax | id 
-------+---------+------+------+------+----
 (0,1) | 1600322 |    0 |    4 |    4 |  1
 (0,2) | 1600322 |    0 |    4 |    4 |  2
 (0,3) | 1600322 |    0 |    4 |    4 |  3
 (0,4) | 1600322 |    0 |    4 |    4 |  4
 (0,5) | 1600322 |    0 |    4 |    4 |  5
 (0,6) | 1600322 |    0 |    4 |    4 |  6
 (0,7) | 1600322 |    0 |    4 |    4 |  7
 (0,8) | 1600322 |    0 |    4 |    4 |  8
(8 rows)

testdb=# -- 查看数据占用空间
testdb=# \set v_tablename t1
testdb=# SELECT pg_size_pretty( pg_total_relation_size(:'v_tablename') );
 pg_size_pretty 
----------------
 8192 bytes
(1 row)

testdb=# -- page_header
testdb=# SELECT * FROM page_header(get_raw_page('t1', 0));
    lsn     | checksum | flags | lower | upper | special | pagesize | version | prune_xid 
------------+----------+-------+-------+-------+---------+----------+---------+-----------
 1/4476E4A0 |        0 |     0 |   424 |  4192 |    8192 |     8192 |       4 |         0
(1 row)

再打开一个Shell窗口，使用pgbench持续不断的更新t1，在此过程进行数据分析。

[xdb@localhost benchmark]$ cat update.sql 
\set rowid random(1,100)
begin;
update t1 set c1=:rowid where id= :rowid;
end;

[xdb@localhost benchmark]$ pgbench -c 2 -C -f ./update.sql -j 1 -n -T 600 -U xdb testdb

二、数据分析

下面通过pageinspect插件分析t1数据页中的数据。

testdb=# \set v_tablename t1
testdb=# 
testdb=# SELECT pg_size_pretty( pg_total_relation_size(:'v_tablename') );
 pg_size_pretty 
----------------
 160 kB
(1 row)

testdb=# -- 查看第0个数据页的头部数据和用户数据
testdb=# SELECT * FROM page_header(get_raw_page('t1', 0));
    lsn     | checksum | flags | lower | upper | special | pagesize | version | prune_xid 
------------+----------+-------+-------+-------+---------+----------+---------+-----------
 1/44787990 |        0 |     2 |   840 |   864 |    8192 |     8192 |       4 |   1600325
(1 row)

testdb=# select * from heap_page_items(get_raw_page('t1',0)) limit 10;
 lp | lp_off | lp_flags | lp_len | t_xmin  | t_xmax  | t_field3 | t_ctid  | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |          t_data          
----+--------+----------+--------+---------+---------+----------+---------+-------------+------------+--------+--------+-------+--------------------------
  1 |   8152 |        1 |     35 | 1600322 | 1600365 |        0 | (0,141) |       16386 |       1282 |     24 |        |       | \x010000000f236162636423
  2 |   8112 |        1 |     35 | 1600322 | 1600325 |        0 | (0,101) |       16386 |       1282 |     24 |        |       | \x020000000f236162636423
  3 |   8072 |        1 |     35 | 1600322 | 1600421 |        0 | (0,197) |       16386 |       1282 |     24 |        |       | \x030000000f236162636423
  4 |   8032 |        1 |     35 | 1600322 | 1600435 |        0 | (1,7)   |           2 |       1282 |     24 |        |       | \x040000000f236162636423
  5 |   7992 |        1 |     35 | 1600322 | 1600474 |        0 | (1,46)  |           2 |       1282 |     24 |        |       | \x050000000f236162636423
  6 |   7952 |        1 |     35 | 1600322 | 1600538 |        0 | (1,110) |           2 |       1282 |     24 |        |       | \x060000000f236162636423
  7 |   7912 |        1 |     35 | 1600322 | 1600396 |        0 | (0,172) |       16386 |       1282 |     24 |        |       | \x070000000f236162636423
  8 |   7872 |        1 |     35 | 1600322 | 1600331 |        0 | (0,107) |       16386 |       1282 |     24 |        |       | \x080000000f236162636423
  9 |   7832 |        1 |     35 | 1600322 | 1600531 |        0 | (1,103) |           2 |       1282 |     24 |        |       | \x090000000f236162636423
 10 |   7792 |        1 |     35 | 1600322 | 1600413 |        0 | (0,189) |       16386 |       1282 |     24 |        |       | \x0a0000000f236162636423
(10 rows)

testdb=# -- 再次查看空间占用
testdb=# SELECT pg_size_pretty( pg_total_relation_size(:'v_tablename') );
 pg_size_pretty 
----------------
 360 kB
(1 row)

可以看出，数据表占用空间一直在增长，已远远超出一个数据页的范围。同时，我们注意到t_xmax、t_infomask2、t_infomask中的部分值与先前首次插入的数据的取值不同。
t_xmax
该值 > 0，表示该行数据已废弃，该值为delete/update操作的事务号
t_infomask2
该值为16386，转换为十六进制显示：

[xdb@localhost benchmark]$ echo "obase=16;16386"|bc
4002

前（低）11位表示属性个数，值为2，也就是说数据表有2个属性（字段）；\x4000表示HEAP_HOT_UPDATED，官方解释如下：

An updated tuple, for which the next tuple in the chain is a heap-only tuple. Marked with HEAP_HOT_UPDATED flag.

t_infomask
该值为1282，转换为十六进制显示：

[xdb@localhost benchmark]$ echo "obase=16;1282"|bc
502

\0x0502 = HEAP_XMIN_COMMITTED | HEAP_XMAX_COMMITTED
意思是插入数据的事务和更新（或删除）的事务均已提交。

三、空间回收

数据表t1不管如何Update，实际的数据行数只有100行，大小远不到8K，但为何占用了几百KB的空间？原因是PG为了MVCC（多版本并发控制）的需要保留了更新前的“垃圾”数据，这些垃圾数据可以通过vacuum机制定期清理这些垃圾数据。但在本例中，由于“长”事务的存在，垃圾数据不能正常清理。

testdb=# SELECT pg_size_pretty( pg_total_relation_size(:'v_tablename') );
 pg_size_pretty 
----------------
 472 kB
(1 row)

testdb=# vacuum t1;
VACUUM
testdb=# SELECT pg_size_pretty( pg_total_relation_size(:'v_tablename') );
 pg_size_pretty 
----------------
 472 kB
(1 row)

testdb=# vacuum full;
VACUUM
testdb=# SELECT pg_size_pretty( pg_total_relation_size(:'v_tablename') );
 pg_size_pretty 
----------------
 472 kB
(1 row)

使用命令vacuum t1和vacuum full均不能正常回收垃圾数据，原因是PG认为这些垃圾数据对于正在活动中的事务（Session A）是可见的。
我们回顾一下，Session A的事务号：1600324，Session B插入数据时的事务号：1600322，更新数据时的事务号 > 1600324，Session A（活动事务）查询t1时，通过PG的snapshot机制实现“一致性”读。PG的snapshot可以通过txid_current_snapshot函数获得：

testdb=# select txid_current_snapshot();
  txid_current_snapshot  
-------------------------
 1600324:1612465:1600324
(1 row)

返回值分为三部分，分别是xin、xmax和xip_list：

格式：xin:xmax:xip_list
xin：Earliest transaction ID (txid) that is still active. 未提交并活跃的事务中最小的XID
xmax：First as-yet-unassigned txid. All txids greater than or equal to this are not yet started as of the time of the snapshot, and thus invisible.所有已提交事务中最大的XID + 1
xip_list：Active txids at the time of the snapshot. All of them are between xmin and xmax. A txid that is xmin <= txid < xmax and not in this list was already completed at the time of the snapshot, and thus either visible or dead according to its commit status.

数据行中的xin和xmax符合条件xmax>活动事务号xid（1600324）>xin的所有记录均不能被回收！

testdb=# --------------------------- Session B
testdb=# vacuum t1;
VACUUM
testdb=# SELECT pg_size_pretty( pg_total_relation_size(:'v_tablename') );
 pg_size_pretty 
----------------
 472 kB
(1 row)

testdb=# vacuum full;
VACUUM
testdb=# SELECT pg_size_pretty( pg_total_relation_size(:'v_tablename') );
 pg_size_pretty 
----------------
 472 kB
(1 row)

反之，活动事务提交后，垃圾数据占用的空间可正常回收：

testdb=# --------------------------- Session A
testdb=# -- 结束事务
testdb=# end;
COMMIT

执行vacuum命令回收垃圾数据：

testdb=# --------------------------- Session B
testdb=# vacuum t1;
VACUUM
testdb=# SELECT pg_size_pretty( pg_total_relation_size(:'v_tablename') );
 pg_size_pretty 
----------------
 472 kB
(1 row)
testdb=# vacuum full;

VACUUM
testdb=# SELECT pg_size_pretty( pg_total_relation_size(:'v_tablename') );
 pg_size_pretty 
----------------
 8192 bytes
(1 row)

通过vacuum t1命令还不能回收数据？为什么？请注意t_infomask2标志HEAP_HOT_UPDATED，简单来说，在update chain中的data不会回收，由于涉及到HOT机制，详细后续再解析。

四、小结

主要有三点需要总结：
1、保留原数据：PG没有回滚段，在执行更新/删除操作时并没有真正的更新和删除，而是保留原有数据，在合适的时候通过vacuum机制清理垃圾数据；
2、避免长事务：为了避免垃圾数据暴涨，在业务逻辑允许的情况下应尽可能的尽快提交事务，避免长事务的出现；
3、查询操作：使用JDBC驱动或者其他驱动连接PG，如明确知道只执行查询操作，请开启自动提交事务。


当前名称：PostgreSQLPage页结构解析（4）-执行DML时表占用空间解析
网页路径：http://cdkjz.cn/article/jiggsp.html
返回首页 了解更多建站资讯

相关资讯

多年建站经验

多一份参考，总有益处

联系快上网，免费获得专属《策划方案》及报价

咨询相关问题或预约面谈，可以通过以下方式与我们联系

大客户专线   成都：13518219792   座机：028-86922220

在线咨询 提交需求
友情链接 交换友情链接
关键词优化排名云主机子晨木包装箱成都名片印刷成都响应式网站建设公司成都网站设计公司成都发电机维修西部信息服务器托管成都网站建设tpbzx.cn

成都网站建设公司地址：成都市青羊区太升南路288号锦天国际A座10层 建设咨询028-86922220

成都快上网科技有限公司-四川网站建设设计公司 | 蜀ICP备19037934号 Copyright 2020,ALL Rights Reserved cdkjz.cn | 成都网站建设 | © Copyright 2020版权所有.

专家团队为您提供成都网站建设,成都网站设计,成都品牌网站设计,成都营销型网站制作等服务,成都建网站就找快上网！ | 成都网站建设哪家好？ | 网站建设地图