Label
PostgreSQL, garbage collection, parameters, freeze
background
What parameters or factors will affect or control the garbage collection of PostgreSQL?
parameter
Global parameters
1. Control the sleep time of VACUUM command. When the stage COST of vacuum is larger than vacuum_cost_limit, sleep will continue after a period of time.
vacuum_cost_delay = 0 # 0-100 milliseconds
2. The cost of different data blocks (hit, missed, dirty blocks in SHARED BUFFER) in VACUUM
#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 Stage COST Threshold
#vacuum_cost_limit = 200 # 1-10000 credits
4. Setting timeout time for read-only transactions to prevent bloating caused by LONG SQL
#old_snapshot_threshold = -1 # 1min-60d; -1 disables; 0 is immediate # (change requires restart)
5. Does the dirty block generated or changed for the first time after CKPT record the content of the whole data block into WAL?
full_page_writes = off # recover from partial page writes
6. Whether to Turn on WAL Compression
#wal_compression = off # enable compression of full-page writes
7. Automated garbage collection process
#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------
Whether to turn on automatic garbage collection
#autovacuum = on # Enable autovacuum subprocess? 'on'
# requires track_counts to also be on.
AUTO VACUUM whose run time exceeds the threshold will be recorded
#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.
Maximum number of garbage collection WORKER processes allowed to work at the same time
autovacuum_max_workers = 9 # max number of autovacuum subprocesses
# (change requires restart)
The polling query finishes the cycle of whether all databases have objects that need garbage collection.
autovacuum_naptime = 1min # time between autovacuum runs
Threshold 1: Minimum number of affected records
#autovacuum_vacuum_threshold = 50 # min number of row updates before
# vacuum
#autovacuum_analyze_threshold = 50 # min number of row updates before
# analyze
Threshold for judging the need for garbage collection and statistical information collection 2: Percentage of affected records
autovacuum_vacuum_scale_factor = 0.00002 # fraction of table size before vacuum
autovacuum_analyze_scale_factor = 0.00001 # fraction of table size before analyze
Whether autovacuum is turned on or not, freeze is forced to trigger when age reaches this threshold.
#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)
Sleep time for automatic garbage collection
autovacuum_vacuum_cost_delay = 0 # default vacuum cost delay for
# autovacuum, in milliseconds;
# -1 means use vacuum_cost_delay
COST threshold before sleep in automatic garbage collection
autovacuum_vacuum_cost_limit = 0 # default vacuum cost limit for
# autovacuum, -1 means use
# vacuum_cost_limit
Main library parameters
Whether garbage collection is delayed or not may lead to inflation, but it can reduce the possibility of query conflicts in read-only standby. However, it may lead to frequent garbage collection in the main repository, and can not be recycled. #vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is delayed
Standby parameters
Whether or not the backup repository sends the snapshot of the transaction back to the main repository, the main repository can not recover the dirty data needed by the firms in the operation of the backup repository, which may lead to the expansion of the main repository. Even lead to frequent garbage collection in the main repository, and can not be recycled.
#hot_standby_feedback = off # send info from standby to prevent
# query conflicts
Client parameters
When vacuum is executed or autovacuum is triggered, records younger than vacuum_freeze_min_age are not FREEZE. When the table is older than vacuum_freeze_table_age, VACUUM scans the entire table and executes FREEZE. #vacuum_freeze_min_age = 50000000 #vacuum_freeze_table_age = 150000000 The following is for the age of the multixact transaction number #vacuum_multixact_freeze_min_age = 5000000 #vacuum_multixact_freeze_table_age = 150000000
Table level parameters
Table PAGE How much space is allocated to INSERT,COPY Request space, left to UPDATE，Realize as far as possible 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. //Whether to open the automatic garbage collection table 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. //Functions similar to global parameters 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.
Factor
1. The database can not recycle the garbage generated by LONG SQL transaction snapshots. That is to say, the oldest transaction in the database determines the upper limit of garbage that the database can recycle. The garbage generated after this transaction snapshot cannot be recycled.
2. snapshot too old. When this parameter is turned on, the permissible time of read-only transaction exceeds this time, it will be rolled back automatically. Prevent bloating caused by LONG SQL.
3. When hot_standby_feedback = on is opened in the standby, if the standby runs LONG SQL, the garbage collection in the main library will be limited. Cause expansion. At the same time, if naptime is very small, it may lead to frequent invalid VACUUM, resulting in database IO, CPU soaring.
4. freeze is the action of scanning the whole table. If freeze occurs in the large table, it may cause a large number of data files to read and write IO, as well as WAL to write IO. Through wal log analysis, we can find the clues of FREEZE.
5. If the main library set vacuum_defer_cleanup_age greater than 0, it may lead to expansion, and if naptime is small, it may lead to frequent invalid VACUUM, resulting in database IO, CPU soaring.