Help Center/ GaussDB/ Centralized_8.x/ Configuring Running Parameters/ GUC Parameters/ Resource Consumption/ Cost-based Vacuum Delay
Updated on 2024-06-03 GMT+08:00
View PDF

Cost-based Vacuum Delay

This feature allows administrators to reduce the I/O impact of the VACUUM and ANALYZE statements on concurrent database activities. It is often more important to prevent maintenance statements, such as VACUUM and ANALYZE, from affecting other database operations than to run them quickly. Cost-based vacuum delay provides a way for administrators to achieve this purpose.

Certain VACUUM operations hold critical locks and should be complete as quickly as possible. In GaussDB, cost-based vacuum delays do not take effect during such operations. To avoid uselessly long delays in such cases, the actual delay is the larger of the two calculated values:

  • vacuum_cost_delay x accumulated_balance/vacuum_cost_limit
  • vacuum_cost_delay x 4

Context

During the execution of the ANALYZE | ANALYSE and VACUUM statements, the system maintains an internal counter that keeps tracking the estimated cost of various I/O operations that are performed. For details about ANALYZE | ANALYSE and VACUUM, see "SQL Reference > SQL Syntax" in Developer Guide. When the accumulated cost reaches a limit (specified by vacuum_cost_limit), the thread performing the operation will sleep for a short period of time (specified by vacuum_cost_delay). Then, the counter resets and the operation continues.

By default, this feature is disabled. To enable this feature, set vacuum_cost_delay to a non-zero value.

vacuum_cost_delay

Parameter description: Specifies the length of time that the thread will sleep when vacuum_cost_limit has been exceeded.

Parameter type: integer.

Unit: ms

Value range: 0 to 100. The value 0 indicates that the cost-based vacuum delay is disabled, and a positive value indicates that the cost-based vacuum delay is enabled. In many systems, the effective resolution of the sleep time is 10 milliseconds. Therefore, setting vacuum_cost_delay to a value that is not an integer multiple of 10 has the same effect as setting it to the next higher multiple of 10.

Default value: 0

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

Setting suggestion: This parameter is usually set to a small value, such as 10ms or 20ms. Adjusting vacuum's resource consumption is best done by changing other vacuum cost parameters.

vacuum_cost_page_hit

Parameter description: Specifies the estimated cost for vacuuming a buffer found in the shared buffer. It represents the cost to lock the buffer pool, look up the shared hash table, and scan the content of the page.

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

Value range: an integer ranging from 0 to 10000.

Default value: 1

vacuum_cost_page_miss

Parameter description: Specifies the estimated cost for vacuuming a buffer read from the disk. It represents the cost to lock the buffer pool, look up the shared hash table, read the desired block from the disk, and scan the block.

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

Value range: an integer ranging from 0 to 10000.

Default value: 10

vacuum_cost_page_dirty

Parameter description: Specifies the estimated cost charged when vacuum modifies a block that was previously clean. It represents the extra cost required to update the dirty block out to the disk again.

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

Value range: an integer ranging from 0 to 10000

Default value: 20

vacuum_cost_limit

Parameter description: Specifies the cost limit. The vacuum thread will hibernate if this limit is exceeded.

Parameter type: integer.

Unit: none

Value range: 1 to 10000

Default value: 1000

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

Setting suggestion: Retain the default value. A larger value indicates a smaller I/O frequency limit of the VACUUM, a lower probability of falling into hibernation, more efficient VACUUM, and a greater impact on service I/Os.

Parent topic: Resource Consumption