PG表膨脹原因

PG特殊的MVCC機(jī)制，delete不會(huì)真的刪除元組，update相當(dāng)于delete+insert，舊元組本身不能通過DML語句來刪除，這樣就只有“漲”空間沒有“清理”空間，這就是表膨脹。

此時(shí)一般需要vacuum來清理死元組，把空間標(biāo)記為可用，下次寫入時(shí)可以用到這部分空間；或者vacuum full等方式重寫表，讓表變得更加緊湊。

場(chǎng)景復(fù)現(xiàn)

OS:CentOS 7.6  

db版本:PG 14.2 

表名: cmdb_objects

圖片

表結(jié)構(gòu)：

圖片

我先插入兩條數(shù)據(jù)，然后進(jìn)行循環(huán)插入，只有object_type字段數(shù)據(jù)不同，用于后續(xù)做更新操作。

insert into cmdb_objects values ('xgrdb','xgr','test67','index');
insert into cmdb_objects values ('xgrdb','xgr','test67','table');
insert into cmdb_objects select * from cmdb_objects;

現(xiàn)在該表中存在32768條數(shù)據(jù)，再進(jìn)行update更新操作：

update cmdb_objects set object_type='view' where object_type='table';

圖片

更新后該表會(huì)存在16384死行，如業(yè)務(wù)數(shù)據(jù)量大，且有頻繁的更新操作，該表就會(huì)產(chǎn)生高水位。可通過pg_stat_all_tables視圖來查詢?cè)摫碇写嬖诘乃佬小⒒钴S行數(shù)。

select relname,n_live_tup,n_dead_tup from pg_stat_all_tables where relname='cmdb_objects';

圖片

處理方式

第一種處理方法vacuum：

把表中的dead tuples進(jìn)行標(biāo)記刪除，并沒有真正的物理刪除，表的實(shí)際使用空間沒有減小，只是將dead tuples的航空件轉(zhuǎn)換為可以使用的狀態(tài)，vacuum過程中，可以正常訪問表數(shù)據(jù)，不鎖表。

你也可以在執(zhí)行碎片清理時(shí)收集統(tǒng)計(jì)信息：

vacuum(verbose,analyze) test;

16384死行已被刪除（該方式適用于實(shí)時(shí)更新的，更新數(shù)據(jù)量不大的表，可以每天進(jìn)行批量操作）。

第二種處理方法vacuum full：

物理刪除表中的dead tuples，釋放空間給操作系統(tǒng)；vacuum full過程中，表被鎖定，不允許訪問。

圖片

執(zhí)行前表大小如下：

圖片

執(zhí)行vacuum full：

圖片

執(zhí)行后表大小：

可以看到該表的使用空間已被物理刪除釋放。（該方式通過重建表，回收所有碎片空間，適用于經(jīng)常進(jìn)行大批量更新數(shù)據(jù)的表，定制策略進(jìn)行執(zhí)行，也可以在維護(hù)時(shí)間業(yè)務(wù)表每周執(zhí)行一次。）

總結(jié)一下vacuum 與 vacuum full的區(qū)別：

• Vacuum不會(huì)阻塞查詢和DML業(yè)務(wù)；不會(huì)立即回收空間，只是把空間標(biāo)記為可用，降低高水位；如果表的最后一個(gè)page沒有元組了，這個(gè)page會(huì)被truncate。
• Vacuum ful8級(jí)鎖，阻塞一切；表完全重寫，操作系統(tǒng)上對(duì)應(yīng)的文件會(huì)被清理和重建；重建索引、FSM（可用空間文件）、VM （page可見性文件）；會(huì)創(chuàng)建一個(gè)副本表，該表使用的磁盤空間最大可能翻至2倍，磁盤空間不足，謹(jǐn)慎執(zhí)行。

第三種處理方法autovacuum參數(shù)控制：

圖片

autovacuum是postgres里面一個(gè)服務(wù)端進(jìn)程，可在一定條件下自動(dòng)觸發(fā)執(zhí)行。

該參數(shù)值默認(rèn)為 on(開啟全局自動(dòng))，主要作用包括：

• 清理死元組(UPDATE或DELETE操作后留下的),并對(duì)表進(jìn)行分析；
• 更新可用空間映射(free space map)，以跟蹤表塊中的可用空間；
• 更新僅索引掃描所需的可見性圖(visibility map)；
• 凍結(jié)(freeze)表行，以便事務(wù)ID計(jì)數(shù)器可以安全地環(huán)繞。

表膨脹的危害：

• 表占用過大的空間；
• 進(jìn)而引起sql性能降低；
• 表過大會(huì)也會(huì)導(dǎo)致vacuum清理時(shí)間變成長(zhǎng)；vacuum full阻塞時(shí)間也會(huì)變長(zhǎng)，不過可以通過pg_repack來代替vacuum full，減少阻塞時(shí)間。

以上操作都會(huì)增加cpu與io的資源消耗。

補(bǔ)充：有一種表膨脹情況比較難處理，autovacuum自動(dòng)清理死元組速度趕不上生成速度。因?yàn)閡pdate、 insert、delete事務(wù)并發(fā)太多，這次的vacuum還沒來得及清理出之前的可用空間，就有大量update生成新的空間和死元組，導(dǎo)致表不斷膨脹。