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 autovacuum to on if you expect the system to automatically clean up two-phase transactions after recovering from faults.
• If autovacuum is set to on and autovacuum_max_workers to 0, the system does not automatically perform autovacuum. Instead, the system automatically cleans up two-phase transactions after recovering from faults.
• If autovacuum is set to on and autovacuum_max_workers to a value greater than 0, the system not only automatically cleans up two-phase transactions but also automatically performs autovacuum 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 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 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.

The timeout check cannot be completely accurate. In principle, the statistics on each CN must be consistent. Therefore, the synchronization between CNs will not be interrupted even if the synchronization times out. As a result, the actual execution time may exceed the user-defined time.

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 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 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.

For example, set the log_autovacuum_min_duration parameter to 250ms to record the information about the autovacuum commands running longer than or equal to 250 ms.

Parameter type: integer.

Unit: millisecond

Value range: –1 to 2147483647.

• 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 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: 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 – Value of max_inner_tool_connections – Value of max_connections – Value of max_concurrent_autonomous_transactions – Value of job_queue_processes – 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 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 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 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. If a global secondary index exists in a table, the ANALYZE operation is performed on the global secondary index only when the number of deleted, inserted, and updated records in the table exceeds the threshold 20 times.

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 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 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 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 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 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 in Table 1.

twophase_clean_workers

Parameter description: Specifies the maximum number of concurrent cleanup operations that can be performed by the gs_clean tool.

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

Value range: an integer ranging from 1 to 10

Default value: 3

defer_csn_cleanup_time

Parameter description: Specifies the local recycling interval.

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

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

Default value: 5s (5000 ms)