PostgreSQL處理膨脹與事務(wù)回卷

IT那活兒發(fā)布于2023-01-11 13:20 / 3058人閱讀

一、表膨脹查詢與處理

1、創(chuàng)建擴展

create extension pgstattuple;

2、表膨脹查詢

如下查詢出來表的怕膨脹系數(shù)為81%。

select *, 1.0 - tuple_len::numeric / table_len as bloat from pgstattuple(tab_brin1);

占用2414個page。

select * from pg_relpages(tab_brin1);

3、表膨脹處理

vacuum (verbose,full,analyze) tab_brin1;

Vacuum

它將進行普通的垃圾收集，將垃圾空間標識為可用的狀態(tài)。它不會影響其它事務(wù)發(fā)出的表上的讀操作和寫操作，因為普通的垃圾收集不會在表上加一個互斥鎖。

VacuumFull

啟動完全垃圾收集，完全垃圾收集會在表上加一個互斥鎖，對表進行垃圾回收期間，其它的事務(wù)不能對表進行讀操作和寫操作。VACUUMFULL比VACUUM的執(zhí)行時間要長一些，執(zhí)行的操作也多一些，它在進行垃圾收集的過程中，可能會將一個記錄從一個數(shù)據(jù)塊轉(zhuǎn)移到另一個數(shù)據(jù)塊。

Vacuumanalyze

除了回收垃圾空間還收集優(yōu)化器統(tǒng)計數(shù)據(jù)

Vacuumverbose

輸出垃圾收集的詳細數(shù)據(jù)。

回收完后，膨脹系數(shù)降到3%。

select *, 1.0 - tuple_len::numeric / table_len as bloat from pgstattuple(tab_brin1);

表占用473個page。

select * from pg_relpages(tab_brin1);

二、數(shù)據(jù)庫防止事務(wù)回卷

VacuumFreeze

為了保證同一個數(shù)據(jù)庫中的最新和最舊的兩個事務(wù)之間的年齡不超過2^31，postgresql引入了凍結(jié)(freeze)功能。

涉及到的術(shù)語：

1、表年齡：當前事務(wù)號距上一次執(zhí)行freeze操作的事務(wù)id的差值

2、元組年齡：當前元組的xmin距上一次執(zhí)行freeze操作的事務(wù)id的差值

如果發(fā)生當新老事務(wù)id差超過21億的時候，事務(wù)號會發(fā)生回卷，此時數(shù)據(jù)庫會報出如下錯誤并且拒絕接受所有連接，必須進入單用戶模式執(zhí)行vacuumfreeze操作。

事務(wù)凍結(jié)操作：

vacuum freeze tab_brin1;

查看指定表的年齡

SELECT relname, age(relfrozenxid) as xid_age,pg_size_pretty(pg_table_size(oid)) as table_size FROM pg_class WHERE relname = tab_brin1;

查詢所有數(shù)據(jù)庫的年齡：

select datname, age(datfrozenxid) from pg_database;

通常報錯如下：

error：database is not accepting commands to avoid wraparound data loss indatabase “mydb”

hint：stop the postmaster and vacuum that database in single-user mode

參數(shù)設(shè)置：

在postgresql中，vacuum是一個比較耗費io的過程，而vacuumfreeze更是被稱為“凍結(jié)炸彈”，因為涉及到了大量的讀寫io，讀io(datafile)和寫io(datafile以及寫wal)。對于業(yè)務(wù)繁忙的庫，可能會出現(xiàn)如下情況：

可能有很多大表的年齡會先后到達2億，數(shù)據(jù)庫的autovacuum會開始對這些表依次進行vacuumfreeze，從而集中式的爆發(fā)大量的讀寫io，數(shù)據(jù)庫和操作系統(tǒng)響應(yīng)遲緩，如果又碰上業(yè)務(wù)高峰，會出現(xiàn)很不好的影響。

所以設(shè)置好參數(shù)尤為重要：

設(shè)置vacuum_cost_delay為一個比較高的數(shù)值（例如50ms），這樣可以減少普通vacuum對正常數(shù)據(jù)查詢的影響。
autovacuum_freeze_max_age和vacuum_freeze_table_age的值也不適合設(shè)置過大，因為過大會造成pg_clog中的日志文件堆積，來不及清理。我們把autovacuum_freeze_max_age設(shè)置為最大值20億。
vacuum_freeze_table_age設(shè)置為0.95* autovacuum_freeze_max_age。
vacuum_freeze_min_age不宜設(shè)置過小，比如我們freeze某個元組后，這個元組馬上又被更新，那么之前的freeze操作其實是無用功，freeze真正應(yīng)該針對的是那些長時間不被更新的元組。
生產(chǎn)環(huán)境中做好pg_database.frozenxid的監(jiān)控，當快達到觸發(fā)值時，我們應(yīng)該選擇一個業(yè)務(wù)低峰期窗口主動執(zhí)行vacuumfreeze操作，而不是等待數(shù)據(jù)庫被動觸發(fā)。
分區(qū)，把大表分成小表。每個表的數(shù)據(jù)量取決于系統(tǒng)的io能力，前面說了vacuumfreeze是掃全表的，現(xiàn)代的硬件每個表建議不超過32gb，單表數(shù)據(jù)不要超過3000w。
對大表設(shè)置不同的vacuum年齡
用戶自己調(diào)度 freeze，如在業(yè)務(wù)低谷的時間窗口，對年齡較大，數(shù)據(jù)量較大的表進行vacuumfreeze。
年齡只能降到系統(tǒng)存在的最早的長事務(wù)即 min(pg_stat_activity.(backend_xid,backend_xmin))。因此也需要密切關(guān)注長事務(wù)。