PostgreSQL Page頁(yè)結(jié)構(gòu)解析（4）- 執(zhí)行DML時(shí)表占用空間解析

本文介紹了在長(zhǎng)事務(wù)（開(kāi)啟事務(wù)，一直不提交/回滾）的情況下，通過(guò)使用pageinspect插件分析Update數(shù)據(jù)表導(dǎo)致數(shù)據(jù)表占用空間“暴漲”的原因。

一、測(cè)試場(chǎng)景

使用psql啟動(dòng)會(huì)話Session B

testdb=# --------------------------- Session B
testdb=# -- 開(kāi)啟事務(wù)
testdb=# begin;
BEGIN
testdb=# 
testdb=# select txid_current();  
 txid_current 
--------------
      1600322
(1 row)

testdb=# -- 創(chuàng)建表&插入100行數(shù)據(jù)
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=# -- 提交事務(wù)
testdb=# end;
COMMIT
testdb=#

開(kāi)啟新的Console創(chuàng)建，使用psql啟動(dòng)會(huì)話Session A

testdb=# --------------------------- Session A
testdb=# -- 開(kāi)啟事務(wù)
testdb=# begin;
BEGIN
testdb=# 
testdb=# -- 查詢當(dāng)前事務(wù)
testdb=# select txid_current();  
 txid_current 
--------------
      1600324
(1 row)

testdb=# 
testdb=# -- do nothing

雖然什么都不做，但Session A仍然可以開(kāi)啟一個(gè)事務(wù)，在這里這個(gè)事務(wù)一直不提交。
回到Session B，查看數(shù)據(jù)表t1的數(shù)據(jù)：

testdb=# --------------------------- Session B
testdb=# -- 查看數(shù)據(jù)表
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=# -- 查看數(shù)據(jù)占用空間
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)

再打開(kāi)一個(gè)Shell窗口，使用pgbench持續(xù)不斷的更新t1，在此過(guò)程進(jìn)行數(shù)據(jù)分析。

[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

二、數(shù)據(jù)分析

下面通過(guò)pageinspect插件分析t1數(shù)據(jù)頁(yè)中的數(shù)據(jù)。

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個(gè)數(shù)據(jù)頁(yè)的頭部數(shù)據(jù)和用戶數(shù)據(jù)
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)

可以看出，數(shù)據(jù)表占用空間一直在增長(zhǎng)，已遠(yuǎn)遠(yuǎn)超出一個(gè)數(shù)據(jù)頁(yè)的范圍。同時(shí)，我們注意到t_xmax、t_infomask2、t_infomask中的部分值與先前首次插入的數(shù)據(jù)的取值不同。
t_xmax
該值 > 0，表示該行數(shù)據(jù)已廢棄，該值為delete/update操作的事務(wù)號(hào)
t_infomask2
該值為16386，轉(zhuǎn)換為十六進(jìn)制顯示：

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

前（低）11位表示屬性個(gè)數(shù)，值為2，也就是說(shuō)數(shù)據(jù)表有2個(gè)屬性（字段）；\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，轉(zhuǎn)換為十六進(jìn)制顯示：

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

\0x0502 = HEAP_XMIN_COMMITTED | HEAP_XMAX_COMMITTED
意思是插入數(shù)據(jù)的事務(wù)和更新（或刪除）的事務(wù)均已提交。

三、空間回收

數(shù)據(jù)表t1不管如何Update，實(shí)際的數(shù)據(jù)行數(shù)只有100行，大小遠(yuǎn)不到8K，但為何占用了幾百KB的空間？原因是PG為了MVCC（多版本并發(fā)控制）的需要保留了更新前的“垃圾”數(shù)據(jù)，這些垃圾數(shù)據(jù)可以通過(guò)vacuum機(jī)制定期清理這些垃圾數(shù)據(jù)。但在本例中，由于“長(zhǎng)”事務(wù)的存在，垃圾數(shù)據(jù)不能正常清理。

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均不能正?；厥绽鴶?shù)據(jù)，原因是PG認(rèn)為這些垃圾數(shù)據(jù)對(duì)于正在活動(dòng)中的事務(wù)（Session A）是可見(jiàn)的。
我們回顧一下，Session A的事務(wù)號(hào)：1600324，Session B插入數(shù)據(jù)時(shí)的事務(wù)號(hào)：1600322，更新數(shù)據(jù)時(shí)的事務(wù)號(hào) > 1600324，Session A（活動(dòng)事務(wù)）查詢t1時(shí)，通過(guò)PG的snapshot機(jī)制實(shí)現(xiàn)“一致性”讀。PG的snapshot可以通過(guò)txid_current_snapshot函數(shù)獲得：

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. 未提交并活躍的事務(wù)中最小的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.所有已提交事務(wù)中最大的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.

數(shù)據(jù)行中的xin和xmax符合條件xmax>活動(dòng)事務(wù)號(hào)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)

反之，活動(dòng)事務(wù)提交后，垃圾數(shù)據(jù)占用的空間可正?；厥眨?/p> 

testdb=# --------------------------- Session A
testdb=# -- 結(jié)束事務(wù)
testdb=# end;
COMMIT

執(zhí)行vacuum命令回收垃圾數(shù)據(jù)：

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)

通過(guò)vacuum t1命令還不能回收數(shù)據(jù)？為什么？請(qǐng)注意t_infomask2標(biāo)志HEAP_HOT_UPDATED，簡(jiǎn)單來(lái)說(shuō)，在update chain中的data不會(huì)回收，由于涉及到HOT機(jī)制，詳細(xì)后續(xù)再解析。

四、小結(jié)

主要有三點(diǎn)需要總結(jié)：
1、保留原數(shù)據(jù)：PG沒(méi)有回滾段，在執(zhí)行更新/刪除操作時(shí)并沒(méi)有真正的更新和刪除，而是保留原有數(shù)據(jù)，在合適的時(shí)候通過(guò)vacuum機(jī)制清理垃圾數(shù)據(jù)；
2、避免長(zhǎng)事務(wù)：為了避免垃圾數(shù)據(jù)暴漲，在業(yè)務(wù)邏輯允許的情況下應(yīng)盡可能的盡快提交事務(wù)，避免長(zhǎng)事務(wù)的出現(xiàn)；
3、查詢操作：使用JDBC驅(qū)動(dòng)或者其他驅(qū)動(dòng)連接PG，如明確知道只執(zhí)行查詢操作，請(qǐng)開(kāi)啟自動(dòng)提交事務(wù)。

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書(shū)系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容