數(shù)據(jù)庫(kù)系統(tǒng)它是為了適應(yīng)數(shù)據(jù)處理的需要而逐漸發(fā)展起來(lái)的一種比較理想的數(shù)據(jù)處理系統(tǒng)，它也是一個(gè)為實(shí)際可運(yùn)行的存儲(chǔ)，維護(hù)以及應(yīng)用系統(tǒng)提供數(shù)據(jù)的軟件系統(tǒng)，在數(shù)據(jù)處理的工作中，每當(dāng)PostgreSQL數(shù)據(jù)庫(kù)中的表里面的行被更新或刪除時(shí)，死亡行會(huì)被遺留下來(lái)，此時(shí)VACUUM則會(huì)把它們除去來(lái)使空間能被重新利用。但是如果一個(gè)表沒(méi)有被清空，它就會(huì)變得臃腫，而且浪費(fèi)磁盤(pán)空間，同時(shí)也會(huì)降低順序表掃描的速度，在較小范圍內(nèi)也會(huì)降低索引掃描的速度。接下來(lái)我們說(shuō)說(shuō)怎樣抑制PostgreSQL集群中的xmin界限？

VACUUM命令只可以移除這些不再被需要的行版本(也被稱為元組)。如果被刪除事務(wù)的事務(wù)ID(存儲(chǔ)在xmax系統(tǒng)列中)比仍然活躍在PostgreSQL數(shù)據(jù)庫(kù)(或者共享表的整個(gè)集群)中最老的事務(wù)(xmin界限)更老，那么這個(gè)元組將不再被需要。注意以下三種情況就可以抑制PostgreSQL集群中的xmin界限。

1、查找長(zhǎng)時(shí)間運(yùn)行的事務(wù)

我們可以查找長(zhǎng)時(shí)間運(yùn)行的事務(wù)，然后使用pg_terminate_backend()函數(shù)去終止阻礙VACUUM命令的數(shù)據(jù)庫(kù)會(huì)話。

2、查找復(fù)制槽

復(fù)制槽是一種數(shù)據(jù)結(jié)構(gòu)，它使PostgreSQL服務(wù)器免于丟棄備用服務(wù)器仍然需要的信息。如果復(fù)制被推遲或者備用服務(wù)器被關(guān)閉，復(fù)制槽就會(huì)阻止VACUUM命令刪除舊的行。復(fù)制槽提供了一種自動(dòng)化的方式來(lái)確保主服務(wù)器不移除WAL塊直到它們被所有的從服務(wù)器接收。而且主服務(wù)器即使當(dāng)從服務(wù)器斷開(kāi)連接時(shí)也不移除可能導(dǎo)致恢復(fù)沖突的行。復(fù)制槽只保留已知所需數(shù)量的WAL塊而不是多于所需數(shù)量。使用復(fù)制槽可以避免這個(gè)問(wèn)題：在從服務(wù)器未連接的任意時(shí)間段內(nèi)不提供保護(hù)。我們可以使用pg_drop_replication_slot()函數(shù)去丟棄不需要的復(fù)制槽。這種情況只會(huì)發(fā)生在當(dāng)hot_standby_feedback參數(shù)設(shè)置為on時(shí)的物理復(fù)制中。如果是邏輯復(fù)制，那么會(huì)有一個(gè)相似的危險(xiǎn)，但是只有系統(tǒng)目錄會(huì)被影響。

3、查找準(zhǔn)備好的事務(wù)

二階段提交協(xié)議是一種原子性確認(rèn)協(xié)議。它是一種分布式算法，用來(lái)協(xié)調(diào)參與分布式原子事務(wù)的所有進(jìn)程，確定是否提交或者終止(回滾)這個(gè)事務(wù)。在二階段提交過(guò)程中，一個(gè)分布式事務(wù)首先使用PREPARE TRANSACTION，為二階段提交準(zhǔn)備當(dāng)前事務(wù)。如果由于任何原因PREPARE TRANSACTION 命令失敗，會(huì)變成ROLLBACK，而當(dāng)前事務(wù)則會(huì)被取消。然后我們使用COMMIT PREPARED，提交一個(gè)之前為兩階段提交預(yù)備的事務(wù)。一旦一個(gè)事務(wù)被準(zhǔn)備好，它會(huì)一直保持一種“游蕩”狀態(tài)直到被提交或者中止。通常情況下，事務(wù)不會(huì)在準(zhǔn)備狀態(tài)中保持很長(zhǎng)時(shí)間，但有時(shí)會(huì)出現(xiàn)錯(cuò)誤所以事務(wù)必須被管理員手動(dòng)移除。我們也可以使用ROLLBACK PREPARED，取消一個(gè)之前為兩階段提交準(zhǔn)備好的事務(wù)。

補(bǔ)充：postgresql vacuum操作

PostgreSQL數(shù)據(jù)庫(kù)管理工作中,定期vacuum是一個(gè)重要的工作.

vacuum的效果

1.1釋放,再利用 更新/刪除的行所占據(jù)的磁盤(pán)空間

1.2更新POSTGRESQL查詢計(jì)劃中使用的統(tǒng)計(jì)數(shù)據(jù)

1.3防止因事務(wù)ID的重置而使非常老的數(shù)據(jù)丟失

第一點(diǎn)的原因是PostgreSQL數(shù)據(jù)的插入,更新,刪除操作并不是真正放到數(shù)據(jù)庫(kù)空間.如果不定期釋放空間的話,由于數(shù)據(jù)太多,查詢速度會(huì)巨降。

第二點(diǎn)的原因是PostgreSQL在做查詢處理的時(shí)候,為了是查詢速度提高,會(huì)根據(jù)統(tǒng)計(jì)數(shù)據(jù)來(lái)確定執(zhí)行計(jì)劃.如果不及時(shí)更新的話,查詢的效果可能不如預(yù)期。

第三點(diǎn)的原因是PostgreSQL中每一個(gè)事務(wù)都會(huì)產(chǎn)生一個(gè)事務(wù)ID,但這個(gè)數(shù)字是有上限的. 當(dāng)事務(wù)ID達(dá)到最大值后,會(huì)重新從最小值開(kāi)始循環(huán).這樣如果不及時(shí)把以前的數(shù)據(jù)釋放掉的話,原來(lái)的老數(shù)據(jù)會(huì)因?yàn)槭聞?wù)ID的丟失而丟失掉。

雖然在新版本的Postgresql中有自動(dòng)的vacuum，但是如果是大批量的數(shù)據(jù)IO可能會(huì)導(dǎo)致自動(dòng)執(zhí)行很慢，需要配合手動(dòng)執(zhí)行以及自己的腳本來(lái)清理數(shù)據(jù)庫(kù)。

1. vacuumdb 是 SQL 命令 VACUUM的封裝

所以用vacuumdb和vacuum來(lái)清理數(shù)據(jù)庫(kù)都可以，效果是一樣的。

2.vacuumdb 中的幾個(gè)重要參數(shù)

可以用vacuumdb --help查詢

-a/--all vacuum所有的數(shù)據(jù)庫(kù)

-d dbname 只vacuum dbname這個(gè)數(shù)據(jù)庫(kù)

-f/--full 執(zhí)行full的vacuum

-t table 只vacuum table這個(gè)數(shù)據(jù)表

1-z/--analyze Calculate statistics for use by the optimizer

3. 切換到postgres用戶下

vacuumdb -d yourdbname -f -z -v 來(lái)清理你的數(shù)據(jù)庫(kù)

或者加到conrtab中15 1 * * * postgres vacuumdb -d mydb -f -z -v >> /tmp/vacuumdb.log

每天的一點(diǎn)一刻開(kāi)始進(jìn)行清理

4. 如何查詢我的XID是否接近臨界值的命令：

1select age(datfrozenxid) from pg_database

或者：

1select max(age(datfrozenxid)) from pg_database

5. 然而我們關(guān)心的是哪一個(gè)大的表組要真正的vacuum1

2SELECT relname, age(relfrozenxid) as xid_age, pg_size_pretty(pg_table_size(oid)) as table_size FROM pg_class WHERE relkind = 'r' and pg_table_size(oid) > 1073741824ORDER BY age(relfrozenxid) DESC LIMIT 20

這個(gè)命令是查詢按照最老的XID排序，查看大于1G而且是排名前20的表。

下面是一個(gè)例子：

relname | xid_age | table_size

------------------------+-----------+------------

postgres_log | 199785216 | 12 GB

statements | 4551790 | 1271 MB

normal_statement_times | 31 | 12 GB

然后你可以單獨(dú)每個(gè)表進(jìn)行vacuum：

1vacuumdb --analyze --verbose --table 'postgres_log' mydb

以上我們?yōu)榇蠹曳窒砹嗽鯓右种芇ostgreSQL集群中的xmin界限？如果您想了解更多相關(guān)信息，請(qǐng)您及時(shí)關(guān)注中培偉業(yè)。