• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. Setting Configuration Parameters
  5. Appendix B: GUC Parameter Description
  6. Resource Consumption
  7. Cost-based Vacuum Delay

Cost-based Vacuum Delay

This feature allows administrators to reduce the I/O impact of the VACUUM and ANALYZE commands on concurrent database activity. In most situations, it is not important to finish maintenance commands, such as VACUUM and ANALYZE, quickly; however, it is important that these commands do not interfere with the system to perform other database operations. Cost-based vacuum delay provides a way for administrators to achieve this purpose.

Certain operations hold critical locks and should be complete as quickly as possible. In DWS, cost-based vacuum delays do not take effect during such operations. To avoid uselessly long delays in such cases, the actual delay is calculated as follows and is the maximum value of the following calculation results:

  • vacuum_cost_delay*accumulated_balance/vacuum_cost_limit
  • vacuum_cost_delay*4

Context

During the execution of the ANALYZE and VACUUM commands, the system maintains an internal counter that keeps track of the estimated cost of the various I/O operations that are performed. When the accumulated cost reaches a limit (specified by vacuum_cost_limit), the process 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 value other than 0.

vacuum_cost_delay

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

Note that on many systems, the effective resolution of sleep delays is 10 milliseconds. Therefore, setting this parameter to a value that is not a multiple of 10 has the same effects as setting it to the next higher multiple of 10.

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

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

Value range: integer (ms). integer enables the Cost-based Vacuum Delay feature.

Default value: 0, indicating that the Cost-based Vacuum Delay feature is disabled.

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 parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: integer (ms)

Default value: 1

vacuum_cost_page_miss

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

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

Value range: integer (ms)

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 I/O required to flush the dirty block out to disk again.

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

Value range: integer (ms)

Default value: 20

vacuum_cost_limit

Parameter description: Specifies the cost limit. The cleanup process will sleep if this limit is exceeded.

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

Value range: integer (ms)

Default value: 200