Chapter 21. 日常資料庫維護工作

Table of Contents
21.1. 日常清理
21.1.1. 恢復磁盤空間
21.1.2. 更新規劃器統計
21.1.3. 避免交易 ID 重疊造成的問題
21.2. 經常重建索引
21.3. 日誌文件維護

為了保持所安裝的 PostgreSQL 伺服器平穩執行, 我們必須做一些日常性的維護工作。我們在這裡討論的這些工作都是經常重複的事情, 可以很容易地使用標準的 Unix 工具,比如cron 腳本來實現。 不過,設置合適的腳本以及檢查它們是否成功執行則是資料庫管理員的責任,

一件很明顯的維護工作就是經常性地建立資料的備份拷貝。 如果沒有最近的備份,那麼您就沒有從災難中恢復的機會(比如磁盤壞了,失火,誤刪了資料表等等)。 可以在PostgreSQL 裡面使用的備份和恢復機制在 Chapter 22 裡面有比較詳細的討論。

其它主要的維護範疇的工作包括週期性的 "vacuuming" (清理)資料庫。 這個工作我們在 Section 21.1裡討論。

其它需要週期性注意的東西是日誌文件的管理。 我們在 Section 21.3 裡討論了這個問題。

PostgreSQL 和其它資料庫產品比較起來是低維護量的。 但是,適當在這些任務上放一些注意將更加能夠確保我們的愉快工作和獲取對這個系統富有成效的經驗。

21.1. 日常清理

由於以下幾個原因,必須週期性執行 PostgreSQLVACUUM 命令︰

  1. 恢復那些由已更新的或已刪除的行佔據的磁盤空間.

  2. 更新 PostgreSQL 查詢規劃器使用的資料統計訊息.

  3. 避免因為交易 ID 重疊造成的老舊資料的丟失.

對上面每個條件進行 VACUUM 操作的頻率和範圍因 不同的節點而不同.因此,資料庫管理員必須理解這些問題並且 開發出合適的維護策略.本節的重點就放在解釋這些高級別的問題; 至於命令語法的細節,請參閱 VACUUM 命令手冊頁.

PostgreSQL 7.2 開始, VACUUM 的標準形式可以和普通的資料庫操作 (selects, inserts, updates, deletes, 但不包括資料表定義的修改)。 因此日常的清理也不再像以前的版本那樣具有干擾性, 也不再那麼特別要求安排在每天的低使用的時間裡進行.

PostgreSQL 8.0 開始,有一些配置參數可以設置, 用來進一步減小後端清理的的性能影響。參閱 Section 16.4.3.4

21.1.1. 恢復磁盤空間

在正常的 PostgreSQL 操作裡, 對一行的UPDATEDELETE並未立即刪除舊版本的資料行。 這個方法對於獲取多版本並行控制的好處是必要的(參閱 Chapter 12): 如果一個行的版本仍有可能被其它交易看到,那麼您就不能刪除它。 但到了最後,不會有任何交易對過期的或者已經刪除的資料感興趣。 而它佔據的空間必須為那些新的資料使用而回收, 以避免對磁盤空間增長的無休止的需求。這件事是透過執行 VACUUM 實現的。

很明顯,那些經常更新或者刪除資料的資料表需要比那些較少更新的資料表清理的更頻繁一些。 所以,設置一個週期性的 cron 任務 VACUUM 那些選定的資料表,而忽略那些已經知道變化比較少的資料表. 這個方法只是在您擁有大量更新頻繁的資料表和大量很少更新的資料表的時候有意義 — 清理一個小資料表的額外開銷根本不值得擔心.

VACUUM 命令有兩個變種。第一種形式,叫做"懶漢 vacuum"或者只是 VACUUM, 在資料表和索引中標記過期的資料為將來使用;它並試圖立即恢復這些過期資料使用的空間。 因此,資料表文件不會縮小,並且任何文件中沒有使用的空間都不會返回給操作系統。 這個變種的 VACUUM 可以和通常的資料庫操作並發執行。

第二種形式是 VACUUM FULL 命令。 這個形式使用一種更加激進的算法來恢復過期的的行版本佔據的空間。 任何 VACUUM FULL 釋放的空間都立即返回給操作系統。 但是,這個形式的 VACUUM 命令在進行 VACUUM FULL 一個資料表的時候在其上要求一個排他鎖。 因此,經常使用 VACUUM FULL 會對並發資料庫查詢有著非常糟糕的影響。

標準形式的 VACUUM 最適合用於維護相當程度的磁盤用量的穩定狀態。 如果您需要把磁盤空間歸還給操作系統,那麼您可以使用 VACUUM FULL — 不過如果釋放的磁盤空間又會很快再次被分配又怎樣? 如果維護更新頻繁的資料表,那麼中等頻率的多次標準 VACUUM 執行方法比很低頻率的 VACUUM FULL 更好。

對於大多數節點而言,我們推薦的習慣是在一天中的低使用的時段安排一次整個資料庫的 VACUUM, 必要時外加對更新頻繁的資料表的更經常的清理。 (有些環境下,對那些更新非常頻繁的資料表可能會每幾分鐘就 VACUUM 一次。) 如果您的集群中有多個資料庫,別忘記對每個庫進行清理;vacuumdb 腳本可能會幫您的忙。

提示: contrib/pg_autovacuum 可以用於高頻率的清理操作。

如果您知道自己剛刪除掉一個資料表中大部分的行,那麼我們建議使用VACUUM FULL, 這樣該資料表的穩定態尺寸可以因為VACUUM FULL更富侵略性的方法而顯著減小。 日常的磁盤空間清理,請使用 VACUUM,而不是 VACUUM FULL

如果您有一個資料表,它的內容經常被完全刪除,那麼可以考慮用 TRUNCATE,而不是後面跟著 VACUUMDELETETRUNCATE 立即刪除整個資料表的內容, 而不要求隨後的 VACUUM 或者VACUUM FULL 來恢復現在沒有用的磁盤空間。

21.1.2. 更新規劃器統計

PostgreSQL 的查詢規劃器依賴一些有關資料表內容的統計訊息用以為查詢生成好的規劃。 這些統計是透過ANALYZE 命令獲得的,您可以直接調用這條命令, 也可以把它當做 VACUUM 裡的一個可選步驟來調用。 擁有合理準確的統計是非常重要的,否則,選擇了惡劣的規劃很可能降低資料庫的性能。

和為了回收空間做清理一樣,經常更新統計訊息也是對更新頻繁的資料表更有用。 不過,即使是更新非常頻繁的資料表,如果它的資料的統計分佈並不經常改變,那麼也不需要更新統計訊息。 一條簡單的拇指定律就是想想資料表中字串的最大很最小值改變的幅度。 比如,一個包含行更新時間的 timestamp 字串將是隨著行的追加和更新穩定增長最大值的; 這樣的字串可能需要比那些包含訪問網站的 URL 的字串更頻繁一些更新統計訊息。 那些 URL 字串可能改變得一樣頻繁,但是其數值的統計分佈的改變相對要緩慢得多。

我們可以在特定的資料表,甚至是資料表中特定的字串上執行 ANALYZE, 所以如果您的應用有需求的話,我們是可以對某些訊息更新得比其它訊息更頻繁的。 不過,在實際中,這種做法的有用性是值得懷疑的。 從 PostgreSQL 7.2 開始, ANALYZE 是一項相當快的操作,即時在大資料表上也很快, 因為它使用了統計學上的隨機採樣的方法進行行採樣, 而不是把每一行都讀取進來。因此,每隔一段時間對整個資料庫執行一便這條命令可能更簡單。

提示: 儘管用 ANALYZE 按字串進行挖掘的方式可能不是很實用, 但您可能還是會發現值得按字串對 ANALYZE 蒐集的統計訊息的詳細級別進行調整。 那些經常在WHERE子句裡使用的字串如果有非常不規則的資料分佈, 那麼就可能需要比其它字串更細緻的資料圖資料表.參閱 ALTER TABLE SET STATISTICS

我們對大多數節點都建議在每天的低使用時段安排一次資料庫範圍的 ANALYZE: 這個任務可以有效地和每天的 VACUUM 組合在一起。 不過,這對那些資料表統計訊息改變相對緩慢的節點可能會過於誇張, 而且少一些的 ANALYZE 也足夠了。

21.1.3. 避免交易 ID 重疊造成的問題

PostgreSQL 的 MVCC 交易語意依賴於比較交易 ID(XID)的數值: 一條帶有大於目前交易的 XID 的插入 XID 的行版本是"屬於未來的", 並且不應為目前交易可見。但是因為交易 ID 的大小有限(在我們寫這些的時候是 32 位),如果一次集群如果執行的時間很長(大於 4 千兆次交易), 那麼它就要受到交易 ID 重疊的折磨︰XID 計數器回到零位, 然後突然間所有以前的交易就變成看上去是在將來的 --- 這意味著它們的輸出將變得可見。 簡而言之,可怕的資料丟失,(實際上資料仍然在那裡,但是如果您無法獲取資料,這麼說也只是幸災樂禍。)

PostgreSQL 7.2 之前, 防禦 XID 重疊的唯一辦法就是至少每4千兆交易就重新做一次initdb。 這種做法對高流量的節點而言當然不是令人滿意的做法,所以我們設計了更好的方法。 新的方法允許某個伺服器仍然保持執行狀態,不需要 initdb 或者任何類型的重啟。 代價就是下面這樣的維護要求: 資料庫中的每個資料表都必須在每十億次交易中至少清理一次

從實際角度出發,這個要求不算一個很繁重的要求, 但是因為如果我們沒能滿足這個要求的後果是全部資料的丟失(而不僅僅是磁盤空間的浪費或者性能的下降), 我們製作了一些特殊的東西來幫助資料庫管理員跟蹤自上次VACUUM 以來的時間。本節剩餘的部分給出這些細節。

XID 比較的新方法剝離出兩個特殊的 XID,數字 1 和 2 (BootstrapXIDFrozenXID)。 這兩個 XID 總是被認為資料表任何普通的 XID 舊。普通的 XID(那些大於 2 的)使用模-231運算進行比較。 這就意味著對於每個普通的 XID,總是有二十億個 XID 是"更舊"以及二十億個 XID"更新"; 資料表達這個意思的另外一個方法是普通的 XID 空間是沒有終點的環。 因此,一旦一條資料帶著特定的普通 XID 建立出來,那麼該資料 將在以後的二十億次交易中資料表現得是"在過去",而不管我們說的是哪個普通 XID。 如果該資料在超過二十億次交易之後仍然存在, 那麼它就會突然變成在將來的資料。為了避免資料丟失,老的資料必須在到達二十億次交易的年齡之前的某個時候賦予 XID FrozenXID。 一旦它被賦予了這個特殊的 XID,那麼它們在所有普通交易面前資料表現為 "在過去",而不管交易 ID 是否重疊, 因此這樣的資料直到刪除之前都會完好,不管要保存多長時間.這個 XID 的重新賦值是VACUUM 控制的.

VACUUM 的正常策略是給任何其普通 XID 有超過十億次已過去交易行版本重新賦值為 FrozenXID。 這個策略保留了原來的插入 XID 直到該數值不再令人感興趣為止。 (實際上,大多數行版本將可能在還沒有"凍結"之前就完成生存和消亡了)。 在這個策略下,任何資料表在兩次 VACUUM 執行之間的最大的安全間隔是十億次交易: 如果您等的時間更長,那麼最後就可能就會有一條不夠老的行版本在重新賦值時變成比二十億次交易更老, 並因此重疊到了未來 — 也就是說,您失去它了。(當然,它在另外二十億次交易之後會重新出現,不過那樣也無濟於事。)

因為上面的原因,我們需要週期性地執行 VACUUM, 所以很難有哪個資料表會到十億次交易還沒有清理過。但是,為了幫助管理員確保滿足了這個要求, VACUUM 在系統資料表pg_database 裡儲存了交易 ID 統計。 尤其是一個資料庫的 pg_database 行中的 datfrozenxid 字串在任何資料庫範圍的 VACUUM 操作(也就是沒有聲明任何資料表的VACUUM)之後將會被更新。 這個字串裡儲存的數值是該 VACUUM 命令使用的凍結終止的 XID。 系統保證在該資料庫中所有比這個終止 XID 老的普通 XID 都被 FrozenXID 代替。 檢查這個訊息的一個便利的方法是執行下面的查詢

SELECT datname, age(datfrozenxid) FROM pg_database;

age 字串用於測量從終止 XID 到目前交易的 XID 的數目。

使用了這種標準的凍結策略,對一個剛清理過的資料庫而言, age 字串將從十億處開始。當age到達二十億次的時候, 資料庫必須再次清理以避免交易標識重疊造成的問題。 我們建議的策略是至少每半個十億次(5億次)交易 VACUUM 一次資料庫, 這樣就可以保證足夠的安全邊界範圍.為了幫助滿足這條規則, 如果有任何 pg_database 記錄顯示出超過15億次交易的 age, 那麼每次資料庫範圍的VACUUM 都會自動發出一條警告,比如:

play=# VACUUM;
WARNING:  Some databases have not been vacuumed in 1613770184 transactions.
        Better vacuum them within 533713463 transactions,
        or you may have a wraparound failure.
VACUUM

帶著 FREEZE 選項的 VACUUM 使用了更大膽的凍結策略: 如果行版本已經老得被所有打開的交易看做是良好的, 那麼就都凍結.特別是如果在一個空閒的資料庫上執行 VACUUM FREEZE,那麼就保證該資料庫中所有的行版本都被凍結。 因此,只要該資料庫沒有其它的變化,那麼它就不需要後續的清理以避免交易 ID 重疊問題。 這個技巧被 initdb 用於準備 template0資料庫。 我們也應該用這個方法對所有在 pg_database資料表裡標記著 datallowconn = false的資料庫進行初始化, 因為我們還沒有任何便利的方法 VACUUM 一個您無法連線的資料庫。 請注意,VACUUM 將忽略那些pg_database 記錄裡有 datallowconn = false 條件的有關未清理資料庫的自動警告訊息。 以避免給出關於這些資料庫的虛假的警告訊息; 因此,確保這樣的資料庫的正常凍結是您的責任。

Warning

要想確保避免交易重疊的問題, 我們必須至少每十億次交易清理一次每個資料庫中的每個資料表,包括系統資料表。 我們已經有過因為沒有做整個資料庫範圍的 vacuum 命令,而只是清理用戶自己活躍的用戶資料表而導致的資料丟失的例子。 只清理用戶資料資料表看上去可行,但只是一段時間裡如此。