PostgreSQL Page頁結構解析（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，如明確知道只執行查詢操作，請開啟自動提交事務。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/6906/viewspace-2374920/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- PostgreSQL Page頁結構解析（3）- 行資料SQL
- PostgreSQL Page頁結構解析（1）-基礎SQL
- PostgreSQL Page頁結構解析（2）- 頁頭和行資料指標SQL指標
- PostgreSQL Page頁結構解析（5）- B-Tree索引儲存結構#1SQL索引
- PostgreSQL Page頁結構解析（6）- B-Tree索引儲存結構#2SQL索引
- PostgreSQL Page頁結構解析（7）- B-Tree索引儲存結構#3SQL索引
- PostgreSQL中page頁結構SQL
- PostgreSQL 資料頁Page解析（1）- 基礎SQL
- PostgreSQL 資料頁Page解析（2）- 頁頭和行資料指標SQL指標
- 檢視臨時表空間佔用及當前天在執行的sqlSQL
- PostgreSQL DBA(190) - 行大小和空間佔用SQL
- MySQL:Innodb page clean 執行緒 (二) 解析MySql執行緒
- MYSQL造資料佔用臨時表空間MySql
- PostgreSQL：表空間SQL
- PostgreSQL 表空間SQL
- Oracle表空間建立引數解析Oracle
- AWR佔用sysaux表空間太大UX
- MySQL 系統表空間檔案解析MySql
- undo表空間佔用磁碟空間滿案例處理
- PostgreSQL儲存引擎之page結構SQL儲存引擎
- PostgreSQL官方並行更新時間表SQL並行
- oracle 表(table)上最後一次 DML時間、行(row)上最後DML時間Oracle
- Postgresql表空間詳解SQL
- Oracle的邏輯結構(表空間、段、區間、塊)——表空間Oracle
- Webpack執行時require能力解析WebUI
- PostgreSQL:表空間-->資料庫-->表SQL資料庫
- 臨時表空間操作總結
- ORACLE臨時表空間總結Oracle
- innodb表空間儲存結構
- Oracle 12C RMAN備份佔用大量臨時表空間Oracle
- sleep 時間段不佔指令碼執行時間指令碼
- Oracle下查詢臨時表空間佔用率Oracle
- 如何從零學習PostgreSQL Page結構SQL
- JAXB解析xml時忽略名稱空間的方法XML
- 用user_tab_modifications查詢表dml操作時間
- 用RMAN執行表空間及時點恢復——RMAN使用者手冊
- 一次臨時表空間大量佔用問題的處理
- 臨時表空間被佔滿的原因查詢