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

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

一、表膨脹查詢與處理

1、創(chuàng)建擴(kuò)展

create extension pgstattuple;

2、表膨脹查詢

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

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

占用2414個(gè)page。

select * from pg_relpages(tab_brin1);

3、表膨脹處理

vacuum (verbose,full,analyze) tab_brin1;

Vacuum

它將進(jìn)行普通的垃圾收集，將垃圾空間標(biāo)識(shí)為可用的狀態(tài)。它不會(huì)影響其它事務(wù)發(fā)出的表上的讀操作和寫(xiě)操作，因?yàn)槠胀ǖ睦占粫?huì)在表上加一個(gè)互斥鎖。

VacuumFull

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

Vacuumanalyze

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

Vacuumverbose

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

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

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

表占用473個(gè)page。

select * from pg_relpages(tab_brin1);

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

VacuumFreeze

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

涉及到的術(shù)語(yǔ)：

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

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

如果發(fā)生當(dāng)新老事務(wù)id差超過(guò)21億的時(shí)候，事務(wù)號(hào)會(huì)發(fā)生回卷，此時(shí)數(shù)據(jù)庫(kù)會(huì)報(bào)出如下錯(cuò)誤并且拒絕接受所有連接，必須進(jìn)入單用戶模式執(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ù)庫(kù)的年齡：

select datname, age(datfrozenxid) from pg_database;

通常報(bào)錯(cuò)如下：

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是一個(gè)比較耗費(fèi)io的過(guò)程，而vacuumfreeze更是被稱為“凍結(jié)炸彈”，因?yàn)樯婕暗搅舜罅康淖x寫(xiě)io，讀io(datafile)和寫(xiě)io(datafile以及寫(xiě)wal)。對(duì)于業(yè)務(wù)繁忙的庫(kù)，可能會(huì)出現(xiàn)如下情況：

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

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

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