Autovacuum

autovacuum

Parameter description: Specifies whether to start the autovacuum thread in the database. Ensure that the track_counts parameter is set to on before starting the autovacuum thread.

• Set the autovacuum parameter to on to automatically vacuum two-phase transactions after the system recovers from faults.
• If autovacuum is set to on and autovacuum_max_workers to 0, the autovacuum process is started only when the system recovers from faults to clean up abnormal two-phase transactions.
• If autovacuum is set to on and the value of autovacuum_max_workers is greater than 0, the system will automatically vacuum the two-phase transactions and processes after recovering from faults.

Parameter type: Boolean.

Unit: none

Value range:

• on indicates that the autovacuum thread is started.
• off indicates that the autovacuum thread is not started.

Default value: on

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

autovacuum_mode

Parameter description: Specifies whether the autoanalyze and autovacuum function are enabled. This parameter is valid only when autovacuum is set to on.

Value type: enumerated type

Unit: none

Value range: analyze, vacuum, mix, and none

• analyze indicates that only autoanalyze is performed.
• vacuum indicates that only autovacuum is performed.
• mix indicates that both autoanalyze and autovacuum are performed.
• none indicates that neither autoanalyze nor autovacuum is performed.

Default value: mix

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value.

autoanalyze_timeout

Parameter description: Specifies the timeout interval of autoanalyze. If the duration of autoanalyze on a table exceeds the value of autoanalyze_timeout, the autoanalyze operation is automatically canceled.

Parameter type: integer.

Unit: second

Value range: 0 to 2147483. The value 0 indicates that no timeout occurs.

Default value: 300

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: none.

autovacuum_io_limits

Parameter description: Specifies the upper limit of I/Os triggered by the autovacuum thread per second.

Parameter type: integer.

Unit: none

Value range: –1 to 1073741823. –1 indicates that the default Cgroup is used.

Default value: –1

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

log_autovacuum_min_duration

Parameter description: Records each step performed by the autovacuum process to the server log when the execution time of the autovacuum process is greater than or equal to a certain value. This parameter helps track the autovacuum behavior.

A setting example is as follows:

Set the log_autovacuum_min_duration parameter to 250 ms to record the actions of autovacuum if it runs for 250 ms or longer.

Value range: an integer ranging from –1 to 2147483647. The unit is ms.

• 0 indicates that all autovacuum actions are recorded in the log.
• –1 indicates that all autovacuum actions are not recorded in the log.
• A value other than –1 indicates that a message is recorded when an autovacuum action is skipped due to a lock conflict. (The reason for skipping autovacuum is recorded for audit.)

Default value: –1

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: The default value is –1. If the autovacuum operation needs to be recorded in logs, set this parameter to a value other than –1.

autovacuum_max_workers

Parameter description: Specifies the maximum number of autovacuum worker threads that can run at the same time. The upper limit of this parameter is related to the values of max_connections and job_queue_processes.

Parameter type: integer.

Unit: none

Value range: an integer. The minimum value is 0, indicating that autovacuum is not enabled. The theoretical maximum value is 262143, but the actual maximum value is a dynamic value calculated by the following formula: 262143 – max_inner_tool_connections – max_connections job_queue_processes – max_concurrent_autonomous_transactions – Number of auxiliary threads – Number of autovacuum launcher threads – 1. The number of auxiliary threads and the number of autovacuum launcher threads are specified by two macros. Their default values are 20 and 2 respectively.

Default value: 3

Adjustment suggestion: If this parameter is set to a larger value, more autovacuum processes are created and more CPU and memory resources are occupied. Therefore, you are advised not to set this parameter to a large value. Otherwise, memory cannot be allocated or too many CPU resources are occupied, causing database startup errors or affecting services.

Setting method: This is a POSTMASTER parameter. Set it based on instructions provided in Table 1.

autovacuum_naptime

Parameter description: Specifies the interval between activity rounds for the autovacuum process.

Parameter type: integer.

Unit: second

Value range: 1 to 2147483.

Default value: 10min (that is, 600s)

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1. For example, if the value is 600 without a unit, autovacuum_naptime indicates 600s. If the value is 10min, autovacuum_naptime indicates 10 minutes. If the unit is required, the value must be s, min, h, or d.

Setting suggestion: Retain the default value.

autovacuum_vacuum_threshold

Parameter description: Used to calculate the threshold for triggering VACUUM. The formula is as follows: autovacuum_vacuum_threshold + reltuples (number of tuples in a table) x autovacuum_vacuum_scale_factor. When the number of deleted or updated records in a table exceeds the threshold, the VACUUM operation is executed on this table.

Parameter type: integer.

Unit: none

Value range: 0 to 2147483647

Default value: 50

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value.

autovacuum_analyze_threshold

Parameter description: Used to calculate the threshold for triggering the ANALYZE operation. The formula is as follows: autovacuum_analyze_threshold + reltuples (number of tuples in a table) x autovacuum_analyze_scale_factor. When the number of deleted, inserted, or updated records in a table exceeds the threshold, the ANALYZE operation is executed on this table.

Parameter type: integer.

Unit: none

Value range: 0 to 2147483647

Default value: 50

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value.

autovacuum_vacuum_scale_factor

Parameter description: The scale factor for vacuuming a table, which is used to calculate the threshold for triggering VACUUM. The formula is as follows: autovacuum_vacuum_threshold + reltuples (number of tuples in a table) x autovacuum_vacuum_scale_factor. When the number of deleted or updated records in a table exceeds the threshold, the VACUUM operation is executed on this table.

Parameter type: floating-point.

Unit: none

Value range: 0.0 to 100.0

Default value: 0.2

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: A larger value makes it harder to trigger VACUUM, which has less impact on performance.

autovacuum_analyze_scale_factor

Parameter description: Specifies the scale factor for analyzing a table, which is used to calculate the threshold for executing ANALYZE. The formula is as follows: autovacuum_analyze_threshold + reltuples (number of tuples in the table) x autovacuum_analyze_scale_factor. When the number of deleted, inserted, or updated records in a table exceeds the threshold, the ANALYZE operation is executed on this table.

Parameter type: floating-point.

Unit: none

Value range: 0.0 to 100.0

Default value: 0.1

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: A larger value makes it harder to trigger ANALYZE, which has less impact on performance.

autovacuum_freeze_max_age

Parameter description: Specifies the maximum age (in transactions) that an Astore table's pg_class.relfrozenxid field can retain before a VACUUM operation is forcibly performed.

• The old files under the subdirectory of pg_clog/ can also be deleted by the VACUUM operation.
• Even if the autovacuum thread is not started, the system will call the thread.

Parameter type: long integer

Unit: none

Value range: 100000 to 576460752303423487

Default value: 4000000000

Setting method: This is a POSTMASTER parameter. Set it based on instructions provided in Table 1.

autovacuum_vacuum_cost_delay

Parameter description: Specifies the value of the cost delay used in the autovacuum operation.

Parameter type: integer.

Unit: ms

Value range: –1 to 100. –1 indicates that the regular vacuum cost delay is used.

Default value: 20

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

autovacuum_vacuum_cost_limit

Parameter description: Sets the value of the cost limit used in the autovacuum operation.

Parameter type: integer.

Unit: none

Value range: –1 to 10000. –1 indicates that the regular vacuum cost limit is used.

Default value: –1

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

defer_csn_cleanup_time

Parameter description: Specifies the local recycling interval.

This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Value range: an integer ranging from 0 to 2147483647. The unit is ms.

Default value: 5s (5000 ms)