PostgreSQL Daily Maintenance - vacuum

postgresql資料庫日常維護需要維護哪些東西, 和資料庫中的業務類型有莫大的關系.

postgresql的并發控制簡單來說是通過多tuple版本, tuple infomask資訊, 事務送出狀态以及事務snapshot來實作的.

當删除一條記錄時, 并不是馬上回收被删除的空間, 因為有可能其他事務還會用到它, 當更新一條記錄是, 老的記錄會保留, 然後插入新的記錄.

例如 :

多次删除插入後, ctid以及變成3了, 因為前面的兩條并為删除. 

update也是如此 :

老的tuple在0号block的itemid=3的位置, 新的tuple是後面插入的在0号block的4号槽.

那麼這些垃圾資料是怎麼回收的呢, postgresql的vacuum程序就是幹這個事情的.

1. vacuum 資料清理.

以上測試表在執行vacuum後的輸出如下 : 

移除了3個版本.

重新插入資料, 此時那些被垃圾占用的槽位就可以被利用了.

一個表有多少條垃圾資料, 多少條活躍資料在系統表pg_stat_all_tables中可以查詢.

n_live_tup        | 2表示有2條活躍資料, 

n_dead_tup        | 0表示有0條垃圾資料.

執行以下删除後, 會發生變化 :

n_live_tup        | 0表示有0條活躍資料, 

n_dead_tup        | 2表示有2條垃圾資料.

vacuum 後活躍資料和垃圾資料都會變成0

2. 自動垃圾回收的配置.

對于一個dml頻繁的資料庫, 如果靠手動來回收垃圾是不太靠譜的事情, postgresql提供了自動的垃圾回收配置.

相關參數如下 :

簡單介紹一下參數的含義 : 

autovacuum, 自動垃圾回收的開關

log_autovacuum_min_duration, 在什麼情況下記錄autovacuum日志輸出. 0表示記錄所有的autovacuum, -1表示不記錄, 其他為時間門檻值, 大于或等于這個時長的autovacuum才記錄.

autovacuum_max_workers, 指最大允許多少個autovacuum子程序同時工作. 因為vacuum會帶來io上的開銷, 還會消耗記憶體. 這個就不要配太大了. 

autovacuum_vacuum_threshold表示autovacuum的vacuum操作所需的最小變更數, 如果這個表的update/delete的tuple總數小于這個數字則不會觸發autovacuum的vacuum操作.

和autovacuum_analyze_threshold表示autovacuum的analyze操作所需的最小變更數, 如果這個表的insert/update/delete的tuple總數小于這個數字則不會觸發autovacuum的analyze操作.

autovacuum_vacuum_scale_factor, 表示autovacuum的vacuum操作所需的變更量門檻值,當這個表的update/delete的tuple總數大于(pg_class.reltuples*autovacuum_vacuum_scale_factor+autovacuum_vacuum_threshold)時, 觸發vacuum操作.

autovacuum_analyze_scale_factor, 表示autovacuum的analyze操作所需的變更量門檻值,當這個表的insert/update/delete的tuple總數大于(pg_class.reltuples*autovacuum_analyze_scale_factor+autovacuum_analyze_threshold)時, 觸發analyze操作.

autovacuum_freeze_max_age, 即使autovacuum未開啟, 為了防止wrapped xid導緻資料不可見, 也會自動觸發的vacuum操作. 表示一個表中存在的最早的事務資訊到現在為止經曆的事務數. 超出則強制vacuum. 防止xid wrapped.

autovacuum_vacuum_cost_delay, 因為vacuum會帶來一定的io開銷, 是以postgresql允許管理者指定當vacuum達到一定的門檻值後進入随眠狀态, 然後再喚醒繼續vacuum. 具體的計算需要配置項cost-based vacuum delay決定.

接下來主要舉例說明幾個threshold參數的作用 : 

檢視目前的門檻值 :

修改naptime, 以及log_autovacuum_min_duration 便于從日志中或者統計表中觀察結果 : 

建立測試表 :

計算插入多少條資料後會觸發analyze : 

是以插入51條資料後會發生analyze.

記錄pg_stat_all_tables的tbl資訊, 注意last_autovacuum , 和last_autoanalyze 的值.

插入50條測試資料 :

stat資訊, 未觸發analyze.

再插入1條記錄.

觸發analyze :

是以插入56條資料後會觸發analyze.

再插入1條即可觸發analyze.

計算update/delete多少條資料後會觸發vacuum :

是以更新或删除共計72條資料後會觸發vacuum, 如果中間發生了analyze, 導緻pg_class.reltuples發生變化, 這個值也會變化.

發生62次insert,update,delete後會觸發analyze.

總共發生了35+19+17=71超出62次dml, 發生analyze, 

analyze後, pg_class.reltuples變成90,

是以觸發vacuum的值變成了多少呢?

是以隻需要69次update/delete即可觸發vacuum, 而上一次vacuum到現在已經發生了71次update/delete, 是以會觸發vacuum.