我們之前的博客文章“我們最討厭的 PostgreSQL 部分”討論了大家最喜歡的 DBMS 多版本并發(fā)控制 (MVCC) 實(shí)現(xiàn)所帶來(lái)的問題。其中包括版本復(fù)制、表膨脹、索引維護(hù)和真空管理。本文將探討針對(duì)每個(gè)問題優(yōu)化 PostgreSQL 的方法。

盡管 PostgreSQL 的 MVCC 實(shí)現(xiàn)是 Oracle 和 MySQL 等其他廣泛使用的數(shù)據(jù)庫(kù)中最差的，但它仍然是我們最喜歡的 DBMS，而且我們?nèi)匀幌矚g它！通過分享我們的見解，我們希望幫助用戶釋放這個(gè)強(qiáng)大的數(shù)據(jù)庫(kù)系統(tǒng)的全部潛力。好消息是 OtterTune 會(huì)自動(dòng)為您解決許多此類問題（但不是全部?。?/p>

問題#1：版本復(fù)制

當(dāng)查詢修改元組時(shí)，無(wú)論更新其一列還是所有列，PostgreSQL 都會(huì)通過復(fù)制其所有列來(lái)創(chuàng)建新版本。這種復(fù)制可能會(huì)導(dǎo)致大量數(shù)據(jù)重復(fù)并增加存儲(chǔ)需求，特別是對(duì)于具有許多列和較大行大小的表。

優(yōu)化：不幸的是，如果不對(duì) PostgreSQL 的內(nèi)部結(jié)構(gòu)進(jìn)行重大重寫（這會(huì)造成破壞），就沒有解決此問題的解決方法。這不像在情景喜劇中替換了一個(gè)沒有人注意到的角色。正如我們?cè)谏弦黄恼轮刑岬降?，EnterpriseDB 在 2013 年通過 zheap 項(xiàng)目開始了這條道路，但該項(xiàng)目的最后一次更新是在 2021 年。其他人已經(jīng)對(duì) PostgreSQL 代碼進(jìn)行了硬分叉，以取代其 MVCC 實(shí)現(xiàn)。著名的例子包括 OrioleDB 和 YugabyteDB。但對(duì)這些系統(tǒng)的更改永遠(yuǎn)不會(huì)合并回主 PostgreSQL 代碼庫(kù)。所以我們暫時(shí)只能使用 PostgreSQL 的append-only MVCC。

Problem #2: Table Bloat?問題#2：表膨脹

PostgreSQL 將過期版本（死元組）和活動(dòng)元組存儲(chǔ)在同一頁(yè)面上。盡管 PostgreSQL 的 autovacuum 工作程序最終會(huì)刪除這些死元組，但寫入繁重的工作負(fù)載可能會(huì)導(dǎo)致它們累積的速度快于真空處理的速度。此外，自動(dòng)清理僅刪除死元組以供重用（例如，存儲(chǔ)新版本），并且不會(huì)回收未使用的存儲(chǔ)空間。在查詢執(zhí)行期間，PostgreSQL 將死元組加載到內(nèi)存中（因?yàn)?DBMS 將它們與活元組混合在頁(yè)面上），從而增加磁盤 IO 并損害性能，因?yàn)?DBMS 檢索無(wú)用的數(shù)據(jù)。如果您正在運(yùn)行 Amazon 的 PostgreSQL Aurora，這將增加 DBMS 的 IOPS，并導(dǎo)致您給 Jeff Bezos(amazon的老板) 更多的錢！

優(yōu)化：我們建議監(jiān)控 PostgreSQL 的表膨脹，然后定期回收未使用的空間。 內(nèi)置pgstattuple模塊可以準(zhǔn)確計(jì)算數(shù)據(jù)庫(kù)中的可用空間，但它需要全表掃描，這對(duì)于生產(chǎn)環(huán)境中的大表來(lái)說不實(shí)用。

$ psql -c "CREATE EXTENSION pgstattuple" -d $DB_NAME
$ psql -c "SELECT * FROM pgstattuple('$TABLE_NAME')" -d $DB_NAME

或者，可以使用一次性查詢或腳本來(lái)估計(jì)表的未使用空間；它們比 pgstattuple 更快、更輕量，因?yàn)樗鼈兲峁┝吮砼蛎浀拇致怨烙?jì)。如果未使用的空間量很大，則 pg_repack 擴(kuò)展會(huì)從臃腫的表和索引中刪除并回收頁(yè)面。它在線工作，不需要在處理過程中對(duì)表進(jìn)行獨(dú)占鎖定（與?VACUUM FULL?不同）。

以下命令將把 pg_repack 擴(kuò)展安裝到自我管理的 DBMS 中（請(qǐng)參閱 Amazon 的 PostgreSQL RDS 說明），然后壓縮單個(gè)表。

$ psql -c "CREATE EXTENSION pg_repack" -d $DB_NAME
$ pg_repack -d $DB_NAME --table $TABLE_NAME

為了最大限度地減少對(duì)數(shù)據(jù)庫(kù)性能的潛在影響，OtterTune 建議我們的客戶在流量較低的非高峰時(shí)段啟動(dòng)此過程。

問題#3：二級(jí)索引維護(hù)

當(dāng)應(yīng)用程序?qū)Ρ韴?zhí)行?UPDATE?查詢時(shí)，PostgreSQL 還必須更新該表的所有索引以將條目添加到新版本。這些索引更新增加了 DBMS 的內(nèi)存壓力和磁盤 I/O，特別是對(duì)于具有大量索引的表（一位 OtterTune 客戶在單個(gè)表上有 90 個(gè)索引?。ｋS著表中索引數(shù)量的增加，更新元組時(shí)產(chǎn)生的開銷也會(huì)增加。 PostgreSQL 避免更新僅堆元組 (HOT) 更新的索引，其中 DBMS 將新版本存儲(chǔ)在與先前版本相同的頁(yè)面上。但正如我們?cè)谏弦黄恼轮刑岬降模琌tterTune 客戶的 PostgreSQL 數(shù)據(jù)庫(kù)僅對(duì) 46% 的更新操作使用 HOT 優(yōu)化。

優(yōu)化：減少 PostgreSQL 索引寫入放大的明顯解決方法是減少每個(gè)表的索引數(shù)量。但這說起來(lái)容易做起來(lái)難。我們建議從表中重復(fù)和未使用的索引開始。人們可以通過檢查數(shù)據(jù)庫(kù)的模式來(lái)識(shí)別重復(fù)索引，以查看兩個(gè)索引是否以相同的順序引用相同的列并使用相同的數(shù)據(jù)結(jié)構(gòu)（例如，B+樹與哈希表）。對(duì)于未使用的索引，PostgreSQL 維護(hù)索引級(jí)指標(biāo)（例如，pg_stat_all_indexes.idx_scan），用于跟蹤在索引上啟動(dòng)的索引掃描的數(shù)量。如果索引的該值為零，則所有應(yīng)用程序的查詢都不會(huì)使用該索引。確保忽略未使用的主鍵或唯一索引，因?yàn)?DBMS 使用它們對(duì)表強(qiáng)制執(zhí)行完整性約束。

下面的屏幕截圖顯示了 OtterTune 的類似檢查，用于自動(dòng)查找不必要的索引。

OtterTune’s Unused and Duplicate index dashboard.
OtterTune 的未使用和重復(fù)索引儀表板。

一旦確定要?jiǎng)h除的索引，下一步就是刪除它們。但是，如果您的應(yīng)用程序使用對(duì)象關(guān)系映射 (ORM) 框架來(lái)管理其數(shù)據(jù)庫(kù)架構(gòu)，那么您不希望手動(dòng)刪除索引，因?yàn)?ORM 可能會(huì)在將來(lái)的架構(gòu)遷移期間重新創(chuàng)建索引。在這種情況下，有必要更新應(yīng)用程序代碼中的架構(gòu)。如果應(yīng)用程序未使用 ORM，則可以使用?DROP INDEX?命令。

問題#4：真空管理

PostgreSQL 的性能在很大程度上取決于其 autovacuum 清理過時(shí)數(shù)據(jù)和修剪 MVCC 方案中版本鏈的有效性。然而，由于其復(fù)雜性，配置自動(dòng)清理以正確運(yùn)行并及時(shí)刪除這些數(shù)據(jù)具有挑戰(zhàn)性。默認(rèn)的全局自動(dòng)清理設(shè)置不適合大型表（數(shù)百萬(wàn)到數(shù)十億的元組），因?yàn)橛|發(fā)清理可能需要很長(zhǎng)時(shí)間。此外，如果每個(gè) autovacuum 調(diào)用需要很長(zhǎng)時(shí)間才能完成或被長(zhǎng)時(shí)間運(yùn)行的事務(wù)阻塞，DBMS 將積累死元組并遭受陳舊統(tǒng)計(jì)數(shù)據(jù)的影響。將自動(dòng)清理延遲太久會(huì)導(dǎo)致查詢隨著時(shí)間的推移逐漸變慢，需要手動(dòng)干預(yù)來(lái)解決該問題。

優(yōu)化：雖然在 PostgreSQL 中清理表很痛苦，但好消息是它是可以管理的。但正如我們現(xiàn)在所討論的，這有很多步驟，并且需要跟蹤很多信息。

控制 autovacuum 的第一步是監(jiān)視每個(gè)表的死元組數(shù)量。 PostgreSQL 的 ?pg_stat_all_tables視圖提供了監(jiān)控表的基本指標(biāo)，包括死元組 (?n_dead_tup?) 和活動(dòng)元組 (?n_live_tup?) 數(shù)量的估計(jì)。通過此類表級(jí)指標(biāo)，您可以確定每個(gè)表過期元組的百分比，并確定哪些表需要額外的清理工作。

對(duì)于具有大量死元組的表，您可以調(diào)整其設(shè)置以使 PostgreSQL 更頻繁地觸發(fā) autovacuum。 PostgreSQL 允許您在表級(jí)別微調(diào) autovacuum 參數(shù)，不同的表可能需要不同的最佳設(shè)置。最重要的旋鈕是 autovacuum_vacuum_scale_factor：它指定在 PostgreSQL 調(diào)用 autovacuum 之前表中必須存在的死元組的最小百分比。該旋鈕的默認(rèn)值為 20%。如果應(yīng)用程序的一個(gè)表有 10 億個(gè)元組，PostgreSQL 不會(huì)在該表上運(yùn)行清理，直到至少有 2 億個(gè)死元組。如果該表中的平均元組大小為 1KB，則 2 億個(gè)死元組將消耗 200GB 的磁盤存儲(chǔ)空間。這甚至不包括指向這些表的索引指針的額外存儲(chǔ)開銷！為了避免此問題，您應(yīng)該使用?ALTER TABLE?SQL 命令將大型表的比例因子旋鈕設(shè)置為小于 20%：

 ALTER TABLE table_name SET (autovacuum_ vacuum_scale_factor = 0.05);

接下來(lái)，您應(yīng)該檢查 autovacuum 是否被長(zhǎng)時(shí)間運(yùn)行的事務(wù)阻塞。再次，我們可以依靠 PostgreSQL 的內(nèi)部遙測(cè)來(lái)獲取這些信息。 pg_stat_activity 視圖提供每個(gè) PostgreSQL 工作線程（即進(jìn)程）當(dāng)前執(zhí)行狀態(tài)的實(shí)時(shí)數(shù)據(jù)。它顯示每個(gè)活動(dòng)事務(wù)已運(yùn)行多長(zhǎng)時(shí)間。如果事務(wù)已經(jīng)運(yùn)行了幾個(gè)小時(shí)，您應(yīng)該考慮將其終止，以便 autovacuum 可以完成其操作。下面的示例查詢查找所有運(yùn)行時(shí)間超過五分鐘的事務(wù)：

SELECT pid, NOW() - xact_start AS duration, query, state
  FROM pg_stat_activity
 WHERE (NOW() - xact_start) > INTERVAL '5 minutes';

然后，您可以使用 pg_cancel_backend 管理函數(shù)終止查詢：

SELECT pg_cancel_backend($PID_TO_KILL);

當(dāng)然，在街上刪除查詢可能會(huì)產(chǎn)生意想不到的后果，因此您必須確保殺死它們不會(huì)在您的應(yīng)用程序中造成問題。為了避免將來(lái)出現(xiàn)同樣的問題，請(qǐng)確保事務(wù)的查詢不必要地運(yùn)行更長(zhǎng)的時(shí)間，因?yàn)樗鼈兪褂玫氖堑托У牟樵冇?jì)劃。請(qǐng)參閱我們之前關(guān)于優(yōu)化查詢性能的文章，例如欺騙?ORDER BY...LIMIT?和運(yùn)行?ANALYZE?，了解如何使用 OtterTune 改善慢速查詢。如果您不需要它們是原子的，您還可以重構(gòu)您的應(yīng)用程序，將大型事務(wù)分解為較小的工作單元（但不可否認(rèn)，這并不總是容易做到）。

最后，您需要查看是否存在長(zhǎng)時(shí)間運(yùn)行的真空過程，然后調(diào)整其他旋鈕。與 pg_stat_activity 顯示 PostgreSQL 工作線程的狀態(tài)類似，pg_stat_progress_vacuum 視圖顯示活動(dòng) autovacuum 操作的狀態(tài)。通過此視圖，您可以確定真空是否需要幾個(gè)小時(shí)甚至幾天才能完成。如果您的 PostgreSQL DBMS 確實(shí)有長(zhǎng)時(shí)間運(yùn)行的 Vacuum，那么 OtterTune 建議調(diào)整三個(gè)旋鈕：

1. autovacuum_work_mem 參數(shù)指定 DBMS 在每次 autovacuum 調(diào)用中可以使用的最大內(nèi)存量。增加此參數(shù)可以加快清理速度，因?yàn)樗梢栽诿看握{(diào)用時(shí)修剪更多的死元組。
2. autovacuum_vacuum_cost_limit 參數(shù)控制在 PostgreSQL 強(qiáng)制自動(dòng)清理工作者暫時(shí)退出之前可以產(chǎn)生多少 I/O 活動(dòng)自動(dòng)清理工作者。該旋鈕的值越高意味著自動(dòng)清理將更加積極。
3. 與這種基于成本的控制機(jī)制相關(guān)，autovacuum_vacuum_cost_delay? 參數(shù)確定 autovacuum 工作線程在 DBMS 強(qiáng)制其退出后必須等待多長(zhǎng)時(shí)間。較短的延遲意味著自動(dòng)清理每次都會(huì)更快地恢復(fù)操作。

原文地址文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-579990.html

到了這里，關(guān)于PostgreSQL MVCC的弊端優(yōu)化方案的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！