PG資料庫為什麼要用autovacuum

很多 P G 的老使用者都對 A UTOVACUUM 存有不好的印象，因為 V AVCUUM 導致的系統問題也讓運維人員對 P G 有十分不好的印象，甚至認為 PostgreSQL   不適合於 7*24的高併發負載場景。實際上，任何資料庫技術都會有不堪的童年，就算是現在的王炸Oracle，當年也讓D BA 痛恨不已。二十多年前，我從使用 R DB 資料庫轉向使用 O RACLE 的時候，第一印象是，這是個什麼玩意，咋這麼難用呢。等用慣了 Oracle之後回頭再看看R DB ，就會對 R DB 百般看不慣了。記得二十年前， Oracle  OPS 剛出來的時候，也讓好多人吃過苦頭，甚至很多人十多年過去了，還不太敢用 O RACLE RAC 。記得差不多 15年前，Oracle   負責售前的總監還多次和我探討在國內，如何去推廣 Oracle  RAC ，讓客戶的核心繫統敢於遷移到 Oracle  RAC 上，為此，我還寫了《 Oracle  RAC 日記》這本書。

針對 PostgreSQL的V ACUUM ，十多年前我和一個客戶還一起寫過一篇關於 M VCC 實現技術對 P G 應用場景限制的論文，其主要問題也是因為 V ACUUM 存在的問題。不過如果現在有人問我要不要啟用 A UTOVACUUM 的時候，我會毫不猶豫地說，一定要用 A UTOVACUUM ，而且只有用好了 A UTOVACUUM ，才算你用好了 P G 資料庫。

有些朋友擔心 V ACUUM 會鎖定資料庫表，影響 D ML 操作，實際上 V ACUUM 並不會阻塞 S ELECT/INSERT/UPDATE/DELETE 等操作，它只會在表上獲取一個 SHARE UPDATE EXCLUSIVE 鎖，這個鎖只會阻塞一些特殊情況的操作。比如：

l  其他的 SHARE UPDATE EXCLUSIVE 鎖請求，比如一個執行 V ACUUM FULL 操作、 A NALYZE 或者 C REATE INDEX CONCURRENTLY, REINDEX CONCURRENTLY, CREATE STATISTICS 或者 A LTER TABLE 、 A LTER INDEX 等 操作的會話；

l  其他是由 S HARE 鎖的操作，比如 C REATE INDEX ；

l  SHARE ROW EXCLUSIVE 鎖請求， CREATE TRIGGER 和部分 ALTER TABLE 操作；

l  EXCLUSIVE 鎖請求，比如執行  REFRESH MATERIALIZED VIEW CONCURRENTLY 的會話；

l  ACCESS EXCLUSIVE  鎖請求，比如執行 DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL 、 REFRESH MATERIALIZED VIEW 。以及一些需要這個鎖級別的  ALTER INDEX 和  ALTER TABLE 操作。

除此之外，如果系統中存在長時間的事務或者大查詢， M VCC 也需要訪問一些老版本的資料，這樣也會導致 VACUUM 執行時跳過對一些死元組的清理。從而引起一些很長時間以前的死元組無法正常清理。

從上面的情況分析來看， V ACUUM 對實際的應用影響並沒有想象的那麼大，對於絕大多數應用場景來說都是可以接受的。雖然如此，很多吃過 AUTOVACUUM 苦頭的 D BA 出於慣性，都會選擇關閉 A UTOVACUUM 而採用手工 V ACUUM ，等業務不忙的時候去手工執行 V ACUUM 操作，這是因為業務高峰期的 V ACUUM 會導致大量的元組被鎖定，整個系統的負載變大，交易效能急劇下降。

這種關閉 A UTOVACUUM 的做法，一般來說是透過設定一個定時任務，在晚上業務比較少的時候統一做 V ACUUM 。這種做法對於一些應用場景有一定的作用，可以避免不確定的 V ACUUM 操作在特殊高併發場景下引起嚴重的效能問題。但是在應用系統的場景中，這樣操作容易引發一些更為嚴重的問題，這是因為 V ACUUM 不僅僅是做死元組的回收工作，還會做表分析操作。

一些老 Oracle   dba可能還有印象，當時從Oracle   8i升級到9 i 之後，很多 S QL 的執行計劃都出錯了。分析了好長時間才定位，這是因為表的統計資料不夠準確，或者部分表沒有做統計，導致了一些 S QL 的執行計劃出現了問題。 P G 資料庫的 C BO 最佳化器也類似與 Oracle   ，不過因為 PG 的最佳化器在演算法上還是無法與 Oracle媲美，因此P G 最佳化器對統計資訊的準確性依賴度很高。一旦某些表的統計資料不夠新了，無法反映出資料的實際情況，那麼很可能會出現執行計劃錯誤。每天定時做一次 V ACUUM 存在兩個弊端，第一個是，如果 V ACUUM 操作進行時，如果出現了前面我們討論過的集中鎖衝突的情況，那麼就會跳過這張表的 V ACUUM 操作，從而沒有完成死元組的清理。這樣就會導致死元組積壓，下一次 V ACUUM 的時候，需要處理的時間更長，對系統的影響更大。而如果使用 A UTOVACUUM ，那麼 V ACUUM 操作會被分散在一天的任何時段中，當表中的資料變化積累到一定量的時候， A UTOVACUUM 就自動觸發了，這種 V ACUUM 比積累了大量變更資料時更為輕便，完成的時間也更短，對應用系統的影響也會越低。

A UTOVACUUM 的另外一個好處是，表上的統計資訊可以得到更為迅速的更新，表統計資訊也會更為準確。 PostgreSQL的C BO 最佳化器十分依賴於精準的表統計資訊，如果統計資訊更為精準，那麼 S QL 的執行計劃也會更為準確。在 Oracle   9 i 時代，我經常處置的就是因為統計資訊不準確而導致的執行計劃錯誤問題。甚至在給一個快遞公司做一個執行計劃穩定的最佳化的時候，我發現因為每天有幾次大批次的資料寫入，經常會導致某些時段中的表統計資料必然會導致錯誤的執行計劃出現，因為應用中存在應用中動態拼湊 S QL ，導致所需的執行計劃無法透過 H INT 固定，而白天業務高峰期經常對超大型的表做分析又風險太大。最後我只能透過白天定期透過估算值寫入估算的統計資訊，晚上業務不忙時再做一次分析的方式解決了這個問題。

類似這個問題的情況，在 PostgreSQL資料庫中也會出現，而A UTOVACUUM 可以在表的資料變化達到某個閾值的時候自動觸發 V ACUUM 和 A NALYZE 操作，從而讓表的統計資訊得到糾正，從而確保執行計劃的合理。

既然 A UTOVACUUM 如此重要，那麼如何做好 A UTOVACUUM 就更為重要了。實際上，現在的伺服器硬體水平和經常出 VACUUM 事情時代的 P G 8 /9時代已經不一樣了，V ACUUM 的演算法也得到了極大的改善，因此現在的 A UTOVACUUM 的風險已經大大降低了。雖然如此，對於 A UTOVACUUM 仍然需要最佳化。

最佳化 A UTOVACUUM 的第一步是未 V ACUUM 操作分配足夠的工作記憶體， maintenance_work_mem 引數對應 V ACUUM 的效能有著至關重要的作用，對應較大型的資料庫系統，擁有超過 10 GB 的單表的資料庫，我們一般至少要給這個引數 1 GB 以上的值。對實體記憶體比較充裕的系統，將 maintenance_work_mem 設定為 2 GB 是我比較常用的。另外一個和 V ACUUM 操作相關的引數 autovacuum_work_mem 可以設定為 -1，這樣這個引數就會參考 maintenance_work_mem 引數。

第二個十分重要的引數是 autovacuum_max_workers ，這取決於有多少張表的 V ACUUM 操作可以並行執行，如果你的系統中有大量的小表，那麼就建議把這個引數設定大一些。當然如果你的 C PU 資源不是很多，那麼就不要設定太大的值了。

今天篇幅有限，對於 A UTOVACUUM 的其他一些引數就不一一討論了，最後我再談一個 V ACUUM 的使用心得。那就是對於大型或者超大型的系統，不能僅僅採用全域性統一的 AUTOVACUUM ，對於某些特殊的大表，要採取特殊的 AUTOVACUUM 策略，也就是說需要設定表級的 AUTOVACUUM 策略。這一點和我們在運維 O RACLE 資料庫的時候，也會經常遇到，對於一些大表的表分析，不能採用系統預設的方式，而要採取特殊的自定義分析方式，否則很容易出問題。

 

我們可以看到，一個系統中的有些表的死元組比例十分高，有些表則很低。對於一些死元組比例比較高的大表，我們需要根據其業務特點來設定獨立的策略。一個比較簡單和常用的做法是，首先列出你的系統中的超過 1 GB 的大表（這個閾值根據你的系統的特點去調整，我這裡只是舉例），然後分析其行數和死元組的數量，比例。同時監控其上次表分析和 V ACUUM 的時間，看看是否合理。如果經常出現某張表很長時間沒有做 V ACUUM 和 A NALYZE ，那麼這張表的預設 AUTOVACUUM 引數有可能不夠合理，需要調整表上的這些引數。另外如果你確實反省自動 V ACUUM 導致了某些業務不正常，那麼也有可能是相關表上的 AUTOVACUUM 引數設定不合理導致的，那麼你可以透過調整這些引數來解決這個問題，而不要不分青紅皂白就關掉 AUTOVACUUM 。