數(shù)據(jù)庫有很多種（截至 2023 年 4 月有 897 個）。面對如此多的數(shù)據(jù)庫，很難知道該選擇什么！但有一個有趣的現(xiàn)象，互聯(lián)網(wǎng)集體決定新應(yīng)用程序的默認選擇。在 2000 年代，傳統(tǒng)觀點選擇 MySQL 是因為像 Google 和 Facebook 這樣的新興科技明星都在使用它。然后在 2010 年代，它是 MongoDB，因為非持久 non-durable writes 寫入使其成為“具有可擴展性和敏捷性的 Web-Scale”。在過去五年中，PostgreSQL 已成為互聯(lián)網(wǎng)上的寵兒 DBMS。并且有充分的理由！它可靠、功能豐富、可擴展，并且非常適合大多數(shù)操作工作負載。
盡管 OtterTune 非常喜歡 PostgreSQL，但它的某些方面并不是很好。因此，我們不想像其他人一樣再寫一篇博客文章來宣揚每個人最喜歡的大象主題(postgresql logo是一個大象) DBMS 的強大功能，而是想討論一件很糟糕的事情：PostgreSQL 如何實現(xiàn)多版本并發(fā)控制 (MVCC)。我們在卡內(nèi)基梅隆大學的研究以及在 Amazon RDS 上優(yōu)化 PostgreSQL 數(shù)據(jù)庫實例的經(jīng)驗表明，其 MVCC 實現(xiàn)相對于其他廣泛使用的關(guān)系 DBMS（包括 MySQL、Oracle 和 Microsoft SQL Server）中最差的。是的，亞馬遜的 PostgreSQL Aurora 仍然存在這些問題。
在本文中，我們將深入探討 MVCC：它是什么、PostgreSQL 是如何實現(xiàn)的，以及為什么它很糟糕。 OtterTune 的目標是減少您對數(shù)據(jù)庫的擔憂，因此我們對解決這個問題進行了很多思考。我們將在下周的后續(xù)文章中介紹 OtterTune 針對 RDS 和 Aurora 數(shù)據(jù)庫自動管理 PostgreSQL MVCC 問題的解決方案。
什么是多版本并發(fā)控制？
DBMS 中 MVCC 的目標是允許多個查詢同時讀取和寫入數(shù)據(jù)庫，而不會在可能的情況下相互干擾。 MVCC 的基本思想是 DBMS 永遠不會覆蓋現(xiàn)有的行。相反，對于每個（邏輯）行，DBMS 維護多個（物理）版本。當應(yīng)用程序執(zhí)行查詢時，DBMS 根據(jù)某些版本號排序（例如創(chuàng)建時間戳）確定要檢索哪個版本來滿足請求。這種方法的好處是多個查詢可以讀取舊版本的行，而不會被另一個更新它的操作阻止。當 DBMS 啟動該查詢的事務(wù)時，查詢會觀察數(shù)據(jù)庫的快照（快照隔離）。這種方法消除了顯式記錄鎖record locks的需要，顯式記錄鎖會在寫入者修改同一項目時阻止讀取者訪問數(shù)據(jù)。
David Reed 1978 年獲得麻省理工學院博士學位，學界認為他的論文“分布式數(shù)據(jù)庫系統(tǒng)中的并發(fā)控制”是第一篇描述 MVCC 的出版物。 MVCC 的第一個商業(yè) DBMS 實現(xiàn)是 20 世紀 80 年代的 InterBase。從那時起，過去二十年中創(chuàng)建的幾乎所有支持事務(wù)的新 DBMS 都實現(xiàn)了 MVCC。
系統(tǒng)工程師在構(gòu)建支持 MVCC 的 DBMS 時必須做出多項設(shè)計決策。從較高的層面來看，它可以歸結(jié)為以下幾點：
- 如何存儲對現(xiàn)有行的更新。
- 如何在運行時查找查詢的正確行版本。
- 如何刪除不再可見的過期版本。
這些決定并不相互排斥。就 PostgreSQL 而言，正是他們在 20 世紀 80 年代決定處理第一個問題的方式導(dǎo)致了我們今天仍然需要處理的另外兩個問題。
在下面討論中，我們將使用以下包含電影信息的表 作為示例。表中的每一行都包含電影名稱、發(fā)行年份、導(dǎo)演和作為主鍵的唯一 ID，以及電影名稱和導(dǎo)演的輔助索引。以下是創(chuàng)建該表的 DDL 命令：
CREATE TABLE movies (
id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
name VARCHAR(256) NOT NULL,
year SMALLINT NOT NULL,
director VARCHAR(128)
);
CREATE INDEX idx_name ON movies (name);
CREATE INDEX idx_director ON movies (director);
該表包含一個主索引 (?movies_pkey?) 和兩個B+Tree輔助索引 (?idx_name?、?idx_director?)。
PostgreSQL的多版本并發(fā)控制
正如 Stonebraker 1987 年的系統(tǒng)設(shè)計文檔中所討論的那樣，PostgreSQL 從一開始就是為了支持多版本而設(shè)計的。 PostgreSQL MVCC 方案的核心思想看似簡單：當sql更新表中的現(xiàn)有行時，DBMS 會復(fù)制該行并將更改應(yīng)用于此新版本，而不是覆蓋原始行。我們將這種方法稱為 僅附加(append-only) 版本存儲方案。但正如我們現(xiàn)在所描述的，這種方法對系統(tǒng)的其余部分有一些重要的影響。
Multi-Versioned Storage?多版本存儲
PostgreSQL 將所有 行版本 存儲在同一存儲空間的表中。要更新現(xiàn)有元組，DBMS 首先從表中獲取新行版本的空槽。然后，它將當前版本的行內(nèi)容復(fù)制到新版本，并將修改應(yīng)用于新分配的版本槽中的行。您可以在下面的示例中看到這個過程，當應(yīng)用程序?qū)﹄娪皵?shù)據(jù)庫執(zhí)行更新查詢以將“Shaolin and Wu Tang”的發(fā)行年份從 1985 年更改為 1983 年時：
當 UPDATE 語句更改表中的元組時，PostgreSQL 會復(fù)制該元組的原始版本，然后將更改應(yīng)用到新版本。在此示例中，表頁 #1 中沒有更多空間，因此 PostgreSQL 在表頁 #2 中創(chuàng)建新版本。
現(xiàn)在，兩個物理版本代表同一邏輯行，DBMS 需要記錄這些版本的沿襲歷史，以便知道將來如何找到它們。 MVCC DBMS 通過單鏈表創(chuàng)建版本鏈來實現(xiàn)這一點。版本鏈僅朝一個方向延伸，以減少存儲和維護開銷。這意味著 DBMS 必須決定使用什么順序：最新到最舊 (newest-to-oldest，N2O) 順序或最舊到最新 (oldest-to-newest，O2N)。對于 N2O 順序，每個元組版本都指向其先前版本，并且版本鏈的頭部始終是最新版本。對于O2N順序，每個元組版本都指向其新版本，而頭部是最舊的元組版本。 O2N 方法避免了 DBMS 在每次修改元組時更新索引以指向更新版本的元組。但是，DBMS 在查詢處理過程中可能需要更長的時間才能找到最新版本，可能會遍歷很長的版本鏈。大多數(shù) DBMS（包括 Oracle 和 MySQL）都實現(xiàn)了 N2O。但 PostgreSQL 在使用 O2N 方面是獨一無二的（除了 Microsoft 用于 SQL Server 的內(nèi)存 OLTP 引擎）。
下一個問題是 PostgreSQL 如何確定為這些 版本指針 記錄什么。 PostgreSQL 中每一行的標頭包含下一個版本的元組 id 字段 (t_tcid)（如果是最新版本，則包含其自己的元組 id）。因此，如下一個示例所示，當查詢請求行的最新版本時，DBMS 會遍歷索引，找到最舊的版本，然后跟蹤指針，直到找到所需的版本。
SELECT 查詢遍歷 索引 以查找 匹配請求的電影名稱的元組。索引條目 指向元組的 最舊版本，這意味著 PostgreSQL 遵循原始版本中嵌入的版本鏈來查找新版本。
PostgreSQL 開發(fā)人員很早就意識到其 MVCC 方案存在兩個問題。首先，每次更新時復(fù)制整個元組的新副本是昂貴的。其次，遍歷整個版本鏈只是為了找到最新版本（這是大多數(shù)查詢想要的）是浪費的。當然，還存在清理舊版本的問題，我們將在下面介紹。
為了避免遍歷整個版本鏈，PostgreSQL 為行的 每個物理版本 在表的索引中添加一個條目。這意味著如果邏輯行有五個物理版本，則索引中該元組將有（最多）五個條目！在下面的示例中，我們看到?idx_name?索引包含位于不同頁面上的每個“Shaolin and Wu Tang”行的條目。這樣就可以直接訪問元組的最新版本，而無需遍歷長版本鏈。
在此示例中，索引包含“Shaolin and Wu Tang”元組的多個條目（每個版本一個）。現(xiàn)在，PostgreSQL 使用索引來查找最新版本，然后立即從表頁#2 中檢索它，而無需遍歷從表頁#1 開始遍歷版本鏈。
PostgreSQL 嘗試通過在與 舊版本相同的磁盤頁面（塊）中創(chuàng)建新副本來減少磁盤 I/O，從而避免安裝多個索引條目并在多個頁面上存儲相關(guān)版本。這種優(yōu)化稱為僅堆元組 (heap-only tuple (HOT)) 更新。如果更新不修改 表索引 引用的任何列，并且新版本與舊版本存儲在 同一 數(shù)據(jù)頁上（如果該頁中有空間），那么 DBMS 將使用 HOT 方法?，F(xiàn)在在我們的示例中，更新后索引仍然指向舊版本，并且查詢通過遍歷版本鏈來檢索最新版本。在正常操作期間，PostgreSQL 通過刪除舊版本來修剪版本鏈來進一步優(yōu)化此過程。
版本清除(Version Vacuum?)
我們已經(jīng)確定，只要應(yīng)用程序更新行，PostgreSQL 就會創(chuàng)建行的副本。下一個問題是系統(tǒng)如何刪除舊版本（稱為“死元組”）。 20 世紀 80 年代的 PostgreSQL 原始版本并沒有刪除死元組。這個想法是，保留所有舊版本允許應(yīng)用程序執(zhí)行“時間旅行”查詢來檢查特定時間點的數(shù)據(jù)庫（例如，在數(shù)據(jù)庫狀態(tài)運行?SELECT?查詢）于上周末存在）。但從不刪除死元組意味著如果應(yīng)用程序刪除元組，表的大小永遠不會縮小。這也意味著頻繁更新的元組的版本鏈較長，這會減慢查詢速度，除非 PostgreSQL 添加了索引條目，允許查詢快速跳轉(zhuǎn)到正確的版本，而不是遍歷版本鏈。但現(xiàn)在，這意味著索引更大，速度更慢，并增加了額外的內(nèi)存壓力。希望您現(xiàn)在能夠理解為什么所有這些問題都是相互關(guān)聯(lián)的。
為了克服這些問題，PostgreSQL 使用 真空(vacuum)過程 來清理表中的死元組。真空對自上次運行以來修改的表頁執(zhí)行順序掃描并查找過期版本。如果某個版本對任何活動事務(wù)都不可見，則 DBMS 會認為該版本“已過期”。這意味著當前沒有事務(wù)正在訪問該版本，未來的事務(wù)將使用最新的“實時”版本。因此，刪除過期版本并回收空間以供重用是安全的。
PostgreSQL 根據(jù)其配置設(shè)置定期自動執(zhí)行此清理過程 (autovacuum)。除了影響所有表的vacuum頻率的全局設(shè)置之外，PostgreSQL還提供了在表級別配置autovacuum的靈活性，以微調(diào)特定表的過程。用戶還可以通過?VACUUM?SQL命令手動觸發(fā)vacuum以優(yōu)化數(shù)據(jù)庫性能。
為什么 PostgreSQL 的 MVCC 是最差的
我們會直言不諱：如果有人今天要構(gòu)建一個新的 MVCC DBMS，他們不應(yīng)該像 PostgreSQL 那樣做（例如，帶有 autovacuum 的append-only存儲）。在我們 2018 年的 VLDB 論文（又名“關(guān)于 MVCC 的最佳論文”）中，我們沒有發(fā)現(xiàn)另一個 DBMS 以 PostgreSQL 的方式執(zhí)行 MVCC。它的設(shè)計是 20 世紀 80 年代的遺物，在 20 世紀 90 年代log-structured系統(tǒng)模式激增之前。
我們來談?wù)?PostgreSQL 的 MVCC 出現(xiàn)的四個問題。我們還將討論為什么其他 MVCC DBMS（如 Oracle 和 MySQL）可以避免這些問題。
問題#1：版本復(fù)制
使用 MVCC 中的append-only存儲方案，如果查詢更新元組，DBMS 會將其所有列復(fù)制到新版本中。無論查詢更新單個列還是所有列，都會發(fā)生這種復(fù)制。正如您可以想象的那樣，僅附加 MVCC 會導(dǎo)致大量數(shù)據(jù)重復(fù)并增加存儲需求。這種方法意味著 PostgreSQL 比其他 DBMS 需要更多的內(nèi)存和磁盤存儲來存儲數(shù)據(jù)庫，這意味著查詢速度更慢，云成本更高。
MySQL 和 Oracle 不是為新版本復(fù)制整個元組，而是在新版本和當前版本之間存儲一個緊湊的增量（可以將其視為 git diff）。使用增量意味著，如果查詢僅更新具有 1000 列的表的元組中的單個列，則 DBMS 僅存儲包含對該列的更改的增量記錄。另一方面，PostgreSQL 創(chuàng)建一個新版本，其中包含查詢更改的一列和其他 999 個未更改的列。我們將忽略 TOAST 屬性，因為 PostgreSQL 以 ?不同的方式處理它們。
有人嘗試使 PostgreSQL 的版本存儲實現(xiàn)現(xiàn)代化。 EnterpriseDB于2013年啟動了zheap項目，以取代append-only存儲引擎以使用增量版本。不幸的是，最后一次官方更新是在 2021 年，據(jù)我們所知，這項努力已經(jīng)失敗。
問題#2：表膨脹
PostgreSQL 中的過期版本（即死元組）也比增量版本占用更多的空間。盡管 PostgreSQL 的 autovacuum 最終會刪除這些死元組，但寫入繁重的工作負載可能會導(dǎo)致它們累積的速度快于清理的速度，從而導(dǎo)致數(shù)據(jù)庫持續(xù)增長。 DBMS 必須在查詢執(zhí)行期間將死元組加載到內(nèi)存中，因為系統(tǒng)將死元組與頁中的活元組混合在一起。不受限制的膨脹會導(dǎo)致 DBMS 在表掃描期間產(chǎn)生更多的 IOPS 并消耗更多的內(nèi)存，從而降低查詢性能。此外，由死元組引起的不準確的優(yōu)化器統(tǒng)計信息可能會導(dǎo)致糟糕的查詢計劃。
假設(shè)我們的 電影表 有 1000 萬個活動元組和 4000 萬個死亡元組，使得表中 80% 的數(shù)據(jù)成為過時數(shù)據(jù)。還假設(shè)該表的列數(shù)比我們顯示的多得多，并且每個元組的平均大小為 1KB。在這種情況下，活動元組占用 10GB 存儲空間，而死元組占用約 40GB 存儲空間；表的總大小為 50GB。當查詢對此表執(zhí)行全表掃描時，PostgreSQL 必須從磁盤檢索所有 50GB 并將其存儲在內(nèi)存中，即使其中大部分已過時。盡管Postgres有一個保護機制來避免順序掃描污染其緩沖池緩存，但它無助于防止IO成本。
即使您確保 PostgreSQL 的 autovacuum 定期運行并且能夠跟上您的工作負載（這并不總是容易做到，請參見下文），autovacuum 也無法回收存儲空間。 autovacuum 僅刪除死元組并重新定位每個頁內(nèi)的活動元組，但不會從磁盤回收空頁。
當 DBMS 由于缺少任何元組而截斷最后一頁時，其他頁仍保留在磁盤上。在上面的示例中，即使 PostgreSQL 從 movie 表中刪除了 40GB 的死元組，它仍然保留了操作系統(tǒng)分配的 50GB 存儲空間（或者，在 RDS 的情況下，從 Amazon 分配）。要回收并返回此類未使用的空間，必須使用?VACUUM FULL?或 pg_repack 擴展將整個表重寫到不浪費存儲的新空間。如果不考慮對生產(chǎn)數(shù)據(jù)庫的性能影響，運行這些操作中的任何一個都不是一件容易的事。它們是資源密集型且耗時的操作，會降低查詢性能。下圖顯示了?VACUUM?和?VACUUM FULL?的工作原理。
通過 PostgreSQL 的常規(guī) VACUUM 操作，DBMS 僅從每個表頁中刪除死元組，并重新組織它以將所有活動元組放在頁的末尾。使用 VACUUM FULL，PostgreSQL 會從每個頁面中刪除死元組，將剩余的活動元組合并并壓縮到新頁面（表第 #3 頁），然后刪除不需要的頁面（表第 #1 / #2 頁）。
問題#3：二級索引維護
對元組的單次更新需要 PostgreSQL 更新該表的所有索引。更新所有索引是必要的，因為 PostgreSQL 在主索引和輔助索引中都使用版本的確切物理位置。除非 DBMS 將新版本存儲在與先前版本相同的頁面中（熱更新），否則系統(tǒng)會對每次更新執(zhí)行此操作。
回到我們的?UPDATE?查詢示例，PostgreSQL 通過像以前一樣將原始版本復(fù)制到新頁面來創(chuàng)建新版本。但它還會在表的主鍵索引 (?movies_pkey?) 和兩個輔助索引 (?idx_director?、?idx_name?) 中插入指向新版本的條目。
使用非 HOT 更新的 PostgreSQL 索引維護操作示例。 DBMS 在表第 #2 頁中創(chuàng)建元組的新版本，然后在所有表的索引中插入指向該版本的新條目。
PostgreSQL 需要在每次更新時修改表的所有索引，這會對性能產(chǎn)生一些影響。顯然，這會使更新查詢變慢，因為系統(tǒng)必須做更多的工作。 DBMS 需要額外的 I/O 來遍歷每個索引并插入新條目。訪問索引會在索引和 DBMS 的內(nèi)部數(shù)據(jù)結(jié)構(gòu)（例如緩沖池的頁表）中引入鎖/閂鎖爭用。同樣，PostgreSQL 會對表的所有索引執(zhí)行此維護工作，即使查詢永遠不會使用它們（順便說一下，OtterTune 會自動查找數(shù)據(jù)庫中未使用的索引）。這些額外的讀寫操作在根據(jù) IOPS 向用戶收費的 DBMS（例如 Amazon Aurora）中是有問題的。
如上所述，如果 PostgreSQL 可以執(zhí)行熱寫入，其中新版本與當前版本位于同一頁上，則可以避免每次更新索引。我們對 OtterTune 客戶的 PostgreSQL 數(shù)據(jù)庫的分析表明，平均大約 46% 的更新使用 HOT 優(yōu)化。盡管這是一個令人印象深刻的數(shù)字，但這仍然意味著超過 50% 的更新要付出這種代價。
有很多用戶在 PostgreSQL MVCC 實現(xiàn)的這一方面遇到困難的例子。最著名的證明是 Uber 2016 年關(guān)于他們?yōu)楹螐?Postgres 轉(zhuǎn)向 MySQL 的博客文章。他們的寫入繁重的工作負載在具有許多二級索引的表上遇到了嚴重的性能問題。
Oracle和MySQL在MVCC實現(xiàn)中不存在這個問題，因為它們的二級索引不存儲新版本的物理地址。相反，它們存儲一個邏輯標識符（例如，元組 ID、主鍵），然后 DBMS 使用該標識符來查找當前版本的物理地址?，F(xiàn)在，這可能會使二級索引讀取速度變慢，因為 DBMS 必須解析邏輯標識符，但這些 DBMS 在其 MVCC 實現(xiàn)中具有其他優(yōu)勢，可以減少開銷。
旁注：Uber 的博客文章?中有關(guān) PostgreSQL 版本存儲的錯誤。具體來說，PostgreSQL 中的每個元組都存儲一個指向新版本的指針，而不是之前的版本，如博客中所述。這會導(dǎo)致 O2N 版本鏈排序，而不是 Uber 錯誤聲明的 N2O 版本鏈。
問題#4：真空管理
PostgreSQL 的性能在很大程度上依賴于 autovacuum 刪除過時數(shù)據(jù)和回收空間的有效性（這就是為什么 OtterTune 在您第一次連接數(shù)據(jù)庫時立即檢查 autovacuum 的健康狀態(tài)）。無論您運行的是 RDS、Aurora 還是 Aurora Serverless，都沒有關(guān)系； PostgreSQL 的所有變體都有相同的 autovacuum 問題。
但由于其復(fù)雜性，確保 PostgreSQL 的 autovacuum 盡可能最佳地運行是很困難的。 PostgreSQL 用于調(diào)整 autovacuum 的默認設(shè)置并不適合所有表，尤其是大型表。例如，配置旋鈕的默認設(shè)置是 20%，該旋鈕控制在 autovacuum 啟動之前 PostgreSQL 必須更新的表的百分比 (autovacuum_vacuum_scale_factor)。此閾值意味著，如果表有 1 億個元組，則 DBMS 不會觸發(fā) autovacuum，直到查詢更新至少 2000 萬個元組。因此，PostgreSQL 可能不必要地在表中長時間保留大量死元組（從而產(chǎn)生 IO 和內(nèi)存成本）。
PostgreSQL 中 autovacuum 的另一個問題是它可能會被長時間運行的事務(wù)阻塞，這可能會導(dǎo)致更多死元組和過時統(tǒng)計信息的積累。未能及時清理過期版本會導(dǎo)致許多性能問題，導(dǎo)致更多長時間運行的事務(wù)阻塞自動清理過程。這變成了一個惡性循環(huán)，需要人類通過終止長時間運行的事務(wù)來手動干預(yù)。
考慮下圖，該圖顯示了 OtterTune 客戶數(shù)據(jù)庫中兩周內(nèi)死元組的數(shù)量：
PostgreSQL Amazon RDS 數(shù)據(jù)庫中一段時間??內(nèi)失效元組的數(shù)量。
圖表中的鋸齒圖案顯示 autovacuum 大約每天執(zhí)行一次主要清理工作。例如，2 月 14 日，DBMS 清理了 320 萬個死元組。該圖實際上是一個不健康的 PostgreSQL 數(shù)據(jù)庫的示例。該圖表清楚地顯示了死亡元組數(shù)量的上升趨勢，因為自動清理無法跟上。
在 OtterTune，我們經(jīng)常在客戶的數(shù)據(jù)庫中看到這個問題。一個 PostgreSQL RDS 實例由于批量插入后的過時統(tǒng)計信息而導(dǎo)致查詢長時間運行。此查詢阻止了 autovacuum 更新統(tǒng)計信息，從而導(dǎo)致更多長時間運行的查詢。 OtterTune 的自動運行狀況檢查發(fā)現(xiàn)了問題，但管理員仍然必須手動終止查詢并在批量插入后運行 ANALYZE。好消息是，長查詢的執(zhí)行時間從 52 分鐘縮短到了 34 秒。
結(jié)束語
在構(gòu)建 DBMS 時，總是需要做出一些艱難的設(shè)計決策。這些決策將導(dǎo)致任何 DBMS 在不同的工作負載上表現(xiàn)不同。對于 Uber 特定的寫入密集型工作負載，PostgreSQL 由于 MVCC 導(dǎo)致的索引寫入放大是他們改用 MySQL 的原因。但請不要誤解我們的謾罵意味著我們認為你不應(yīng)該使用 PostgreSQL。盡管它的 MVCC 實現(xiàn)方式是錯誤的，但 PostgreSQL 仍然是我們最喜歡的 DBMS。熱愛某件事就是愿意克服它的缺陷（參見丹·薩維奇的《入場的代價》）。
那么如何解決 PostgreSQL 的怪癖呢？好吧，您可以花費大量的時間和精力自己進行調(diào)整。祝你好運。
我們將在下一篇文章中詳細介紹我們可以做什么。文章來源：http://www.zghlxwxcb.cn/news/detail-591720.html
原文地址文章來源地址http://www.zghlxwxcb.cn/news/detail-591720.html
到了這里，關(guān)于PostgreSQL MVCC的弊端的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
