PostgreSQL的AutoVacuum原理及autovacuum不工作問題解析

数据库集中营發表於2024-07-09
SQL

1、AutoVacuum概述

PostgreSQL資料庫是有資料清理的，有人工執行清理，也有自動清理，但是這2種的清理方式對效能是有不同的影響，特別是OLTP環境中，每次不管是人工清理還是自動清理dead tuple，都會對資料庫的IO有明顯的影響，基於PostgreSQL 的原理每個表中的行會存在多個版本的資料，為了完成資料庫的MVCC 多版本控制，以及資料的UNDO 的功能在這些過期版本的行被棄用後，會產生眾多的死行dead tuple.在這樣的情況下，如果不及時的將這些dead tuple 進行清理，輕則影響磁碟空間的在利用，重則影響資料庫的效能，在測試中發現，在每次進行百萬級別的dead tuple清理，資料庫會有7%-10%的效能下降，多次往復亦是如此，同時會導致本來一張表3G的表就能儲存，但最終由於dead tuple可能達到80G甚至更大的空間。

2、AutoVacuum引數說明

autovacuum

  • 預設：autovacuum = on (布林型)
  • 表示是否開起autovacuum。當需要凍結xid（事務ID）時，儘管此值為off，PG也會進行vacuum。

log_autovacuum_min_duration

  • 預設：log_autovacuum_min_duration = -1(數字型) 單位ms
  • 在規定時長內未完成的vacuum予以記錄日誌，-1表示禁用，0表示所有的，大於0僅記錄超過時間的。

autovacuum_max_workers

  • 預設：autovacuum_max_workers = 3(數字型)
  • autovacuum最大工作清理程序數,CPU核多IO好的情況下，可以增加，但是注意記憶體消耗，有多少個worker就會有多少倍的autovacuum_work_mem記憶體使用，會消耗較多記憶體，重啟資料庫生效。

autovacuum_naptime

  • 預設：autovacuum_naptime = 1min(分鐘)
  • 兩次vacuum啟動的時間間隔。

autovacuum_analyze_threshold

  • 預設：autovacuum_analyze_threshold = 50(數字型)
  • 自動analyze操作的最小行數，有利於對SQL語句進行更精準匹配到最好的執行計劃。

autovacuum_vacuum_scale_factor

  • 預設：autovacuum_vacuum_scale_factor = 0.2(數字型)
  • autovacuum的vacuum操作所需的變更量閾值,這個表的update/delete的tuple總數大於(pg_class.reltuples*autovacuum_vacuum_scale_factor+autovacuum_vacuum_threshold)時, 觸發vacuum操作,建議不要太高頻率，否則會因為vacuum產生較多的XLOG。

autovacuum_analyze_scale_factor

  • 預設：autovacuum_analyze_scale_factor = 0.1(數字型)
  • 表示autovacuum的analyze操作所需的變更量閾值,當這個表的INSERT/update/delete的tuple總數大於(pg_class.reltuples*autovacuum_analyze_scale_factor+autovacuum_analyze_threshold)時, 觸發analyze操作。

autovacuum_freeze_max_age

  • 預設：autovacuum_freeze_max_age = 200000000(數字型)
  • 某表的pg_class.relfrozenxid的最大值，如果超出此值則重置xid,凍結表的記錄。

autovacuum_vacuum_cost_delay

  • 預設：autovacuum_vacuum_cost_delay = 20ms (數字型)
  • 當autovacuum程序執行時，對vacuum執行cost進行評估，如果超過autovacuum_vacuum_cost_limit的值時，則延遲這麼長的時間。

autovacuum_vacuum_cost_limit

  • 預設：autovacuum_vacuum_cost_limit = -1 (數字型)

  • autovacuum程序的評估閥值，-1表示使用vacuum_cost_limit值，如果在執行 autovacuum程序期間評估的cost超過autovacuum_vacuum_cost_limit,則autovacuum程序則會休眠。

3、autovacuum不工作問題解析

（1）分析過程

1.檢視OS主機整體資源使用情況。

2.看具體SQL執行計劃(explain)

3.檢視錶資料量以及表大小

4.檢視pg_stat_all_tables檢視的n_dead_tup、last_autovacuum列

5.檢視執行pg_log下的執行日誌

（2)產生原因

本次問題產生原因是物理複製槽無效後未被刪除導致。

可能產生該問題得到原因如下：

1.無效的複製槽未被刪除。

2.長事物。

3.本地迴環網路介面不可訪問。autovacuum launcher process和stats collector process程序需要透過本地迴環網路介面收集統計資訊。

4.大表導致autovacuum過慢，autovacuum包含vacuum操作和analyze操作對於大表兩個操作都會慢。

(3)解決方案

1.無效的複製槽未被刪除

select * from pg_replication_slots;#查詢複製槽狀態

select pg_drop_replication_slot('複製槽名');#刪除無效複製槽

2.長事物

設定引數

idle_in_transaction_session_timeout (integer)

終止任何已經閒置超過這個引數所指定的時間（以毫秒計）的開啟事務的會話。

這使得該會話所持有的任何鎖被釋放，並且其所持有的連線槽可以被重用，

它也允許只對這個事務可見的元組被清理。

透過命令殺掉,佔用長事物會話

select * from pg_stat_activity; #查詢會話狀態

pg_terminate_backend() #終止會話

3.本地迴環網路介面不可訪問。

autovacuum launcher process和stats collector process 程序需要透過本地迴環網路介面收集統計資訊。

pg_isready -h localhost -p 5432 #檢查localhost連通性

ifconfig檢查本地迴環網口是否正常

4.大表導致autovacuum過慢，autovacuum包含vacuum操作

和analyze操作對於大表兩個操作都會慢。

a.使用表分割槽,降低autovacuum操作的資料集大小。

#降低大表的比例因子大小

b.ALTER TABLE 表名 SET (autovacuum_analyze_scale_factor = 值);

c.業務非繁忙期,定時執行vacuum。

相關文章