475
影響或控製PostgreSQL垃圾回收的參數或因素
標簽
PostgreSQL , 垃圾回收 , 參數 , freeze
背景
哪些參數或因素將影響或控製PostgreSQL的垃圾回收呢？
參數
全局參數
1、控製VACUUM命令的睡眠時間，當vacuum的階段性COST大於vacuum_cost_limit時，睡眠一段時間後繼續。
vacuum_cost_delay = 0 # 0-100 milliseconds
2、VACUUM時，不同數據塊（在SHARED BUFFER中命中的塊、未命中的塊、髒塊）的成本
#vacuum_cost_page_hit = 1 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
3、VACUUM階段性COST閾值
#vacuum_cost_limit = 200 # 1-10000 credits
4、對隻讀事務設置的超時時間，防止LONG SQL帶來的膨脹
#old_snapshot_threshold = -1 # 1min-60d; -1 disables; 0 is immediate
# (change requires restart)
5、在CKPT後第一次產生或變更的髒塊，是否記錄整個數據塊的內容到WAL中。
full_page_writes = off # recover from partial page writes
6、是否開啟WAL壓縮
#wal_compression = off # enable compression of full-page writes
7、自動垃圾回收相關進程
#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------
是否開啟自動垃圾回收
#autovacuum = on # Enable autovacuum subprocess? 'on'
# requires track_counts to also be on.
運行時長超過閾值的AUTO VACUUM將被記錄下來
#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and
# their durations, > 0 logs only
# actions running at least this number
# of milliseconds.
最多允許多少個垃圾回收WORKER進程同時工作
autovacuum_max_workers = 9 # max number of autovacuum subprocesses
# (change requires restart)
輪詢查詢完所有數據庫是否有需要進行垃圾回收的對象的周期。
autovacuum_naptime = 1min # time between autovacuum runs
判斷是否需要進行垃圾回收、收集統計信息的閾值1：最小被影響記錄數
#autovacuum_vacuum_threshold = 50 # min number of row updates before
# vacuum
#autovacuum_analyze_threshold = 50 # min number of row updates before
# analyze
判斷是否需要進行垃圾回收、收集統計信息的閾值2：被影響記錄數占比
autovacuum_vacuum_scale_factor = 0.00002 # fraction of table size before vacuum
autovacuum_analyze_scale_factor = 0.00001 # fraction of table size before analyze
不管有沒有開啟autovacuum，當年齡達到這個閾值，都會強製觸發freeze
#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
# (change requires restart)
#autovacuum_multixact_freeze_max_age = 400000000 # maximum multixact age
# before forced vacuum
# (change requires restart)
自動垃圾回收的睡眠時間
autovacuum_vacuum_cost_delay = 0 # default vacuum cost delay for
# autovacuum, in milliseconds;
# -1 means use vacuum_cost_delay
自動垃圾回收睡眠前的COST閾值
autovacuum_vacuum_cost_limit = 0 # default vacuum cost limit for
# autovacuum, -1 means use
# vacuum_cost_limit
主庫參數
是否延遲回收垃圾，可能導致膨脹，但是可以降低隻讀備庫的查詢衝突的可能性。但是可能導致主庫頻繁進行垃圾回收，並回收不掉。
#vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is delayed
備庫參數
備庫是否把事務快照發回給主庫，主庫不能回收備庫運行中事務所需的髒數據，可能導致主庫膨脹。甚至導致主庫頻繁進行垃圾回收，並回收不掉。
#hot_standby_feedback = off # send info from standby to prevent
# query conflicts
客戶端參數
執行vacuum或觸發autovacuum時，年齡小於vacuum_freeze_min_age的記錄，不會被FREEZE。
當表的年齡大於vacuum_freeze_table_age時，VACUUM會掃描全表，並執行FREEZE。
#vacuum_freeze_min_age = 50000000
#vacuum_freeze_table_age = 150000000
以下則針對multixact事務號的年齡
#vacuum_multixact_freeze_min_age = 5000000
#vacuum_multixact_freeze_table_age = 150000000
表級參數
表的PAGE中，分配多少空間給INSERT、COPY時請求的空間，剩餘的留給UPDATE，盡量實現HOT。
fillfactor (integer)
The fillfactor for a table is a percentage between 10 and 100.
100 (complete packing) is the default.
When a smaller fillfactor is specified,
INSERT operations pack table pages only to the indicated percentage;
the
remaining space on each page is reserved for updating rows on that page.
This gives UPDATE a chance to place the updated copy of a row on the same page as the original,
which is more efficient than placing it on a
different page. For a table whose entries are never updated,
complete packing is the best choice,
but in heavily updated tables smaller fillfactors are appropriate.
This parameter cannot be set for TOAST tables.
是否開啟表的自動垃圾回收
autovacuum_enabled, toast.autovacuum_enabled (boolean)
Enables or disables the autovacuum daemon for a particular table.
If true, the autovacuum daemon will perform automatic VACUUM and/or
ANALYZE operations on this table following the rules discussed in
Section 24.1.6. If
false, this table will not be autovacuumed, except to prevent
transaction ID wraparound. See Section 24.1.5 for more about
wraparound prevention. Note that the autovacuum daemon does not
run at all (except to prevent
transaction ID wraparound) if the autovacuum parameter is false;
setting individual tables' storage parameters does not override that.
Therefore there is seldom much point in explicitly setting this storage parameter to
true, only to false.
與全局參數類似功能
autovacuum_vacuum_threshold, toast.autovacuum_vacuum_threshold (integer)
Per-table value for autovacuum_vacuum_threshold parameter.
autovacuum_vacuum_scale_factor, toast.autovacuum_vacuum_scale_factor (float4)
Per-table value for autovacuum_vacuum_scale_factor parameter.
autovacuum_analyze_threshold (integer)
Per-table value for autovacuum_analyze_threshold parameter.
autovacuum_analyze_scale_factor (float4)
Per-table value for autovacuum_analyze_scale_factor parameter.
autovacuum_vacuum_cost_delay, toast.autovacuum_vacuum_cost_delay (integer)
Per-table value for autovacuum_vacuum_cost_delay parameter.
autovacuum_vacuum_cost_limit, toast.autovacuum_vacuum_cost_limit (integer)
Per-table value for autovacuum_vacuum_cost_limit parameter.
autovacuum_freeze_min_age, toast.autovacuum_freeze_min_age (integer)
Per-table value for vacuum_freeze_min_age parameter.
Note that autovacuum will ignore per-table autovacuum_freeze_min_age
parameters that are larger than half the system-wide autovacuum_freeze_max_age setting.
autovacuum_freeze_max_age, toast.autovacuum_freeze_max_age (integer)
Per-table value for autovacuum_freeze_max_age parameter.
Note that autovacuum will ignore per-table autovacuum_freeze_max_age
parameters that are larger than the system-wide setting (it can only be set smaller).
autovacuum_freeze_table_age, toast.autovacuum_freeze_table_age (integer)
Per-table value for vacuum_freeze_table_age parameter.
autovacuum_multixact_freeze_min_age, toast.autovacuum_multixact_freeze_min_age (integer)
Per-table value for vacuum_multixact_freeze_min_age parameter.
Note that autovacuum will ignore per-table autovacuum_multixact_freeze_min_age
parameters that are larger than half the system-wide
autovacuum_multixact_freeze_max_age setting.
autovacuum_multixact_freeze_max_age, toast.autovacuum_multixact_freeze_max_age (integer)
Per-table value for autovacuum_multixact_freeze_max_age parameter.
Note that autovacuum will ignore per-table autovacuum_multixact_freeze_max_age
parameters that are larger than the system-wide setting (it can only be
set smaller).
autovacuum_multixact_freeze_table_age, toast.autovacuum_multixact_freeze_table_age (integer)
Per-table value for vacuum_multixact_freeze_table_age parameter.
log_autovacuum_min_duration, toast.log_autovacuum_min_duration (integer)
Per-table value for log_autovacuum_min_duration parameter.
因素
1、數據庫不能回收LONG SQL事務快照後產生的垃圾，也即是說，數據庫中最老的事務，決定了數據庫可以回收的垃圾上限。在此事務快照後產生的垃圾都無法被回收。
2、snapshot too old，開啟這個參數後，隻讀事務的允許時間超過這個時間後，將自動回滾。防止LONG SQL引起的膨脹。
3、備庫開啟了hot_standby_feedback = on時，備庫如果運行LONG SQL，也會導致主庫垃圾回收受限。導致膨脹。同時如果naptime很小，則可能導致頻繁的無效VACUUM發生，導致數據庫的IO，CPU飆高。
4、freeze是掃描全表的動作，如果大表發生freeze，可能導致大量的數據文件讀寫IO，以及WAL的寫IO。通過wal日誌分析，可以找到FREEZE的蛛絲馬跡。
5、如果主庫設置了vacuum_defer_cleanup_age 大於 0 ，可能導致膨脹，同時如果naptime很小，則可能導致頻繁的無效VACUUM發生，導致數據庫的IO，CPU飆高。
最後更新：2017-07-14 09:04:05