Help Center > > Developer Guide> Setting GUC Parameters> Workload Management

Workload Management

Updated at: Jul 15, 2020 GMT+08:00

If database resource usage is not controlled, concurrent tasks easily preempt resources. As a result, the OS will be overloaded and cannot respond to user tasks; or even crash and cannot provide any services to users. The DWS workload management function balances the database workload based on available resources to avoid database overloading.

cgroup_name

Parameter description: Specifies the name of the Cgroup in use. It can be used to change the priorities of jobs in the queue of a Cgroup.

If you set cgroup_name and then session_respool, the Cgroups associated with session_respool take effect. If you reverse the order, Cgroups associated with cgroup_name take effect.

If the Workload Cgroup level is specified during the cgroup_name change, the database does not check the Cgroup level. The level ranges from 1 to 10.

Type: USERSET

You are not advised to set cgroup_name and session_respool at the same time.

Value range: a string

Default value: DefaultClass:Medium

DefaultClass:Medium indicates the Medium Cgroup belonging to the Timeshare Cgroup under the DefaultClass Cgroup.

enable_cgroup_switch

Parameter description: Specifies whether the database automatically switches to the TopWD group when executing statements by group type.

Type: USERSET

Value range: Boolean

  • on: The database automatically switches to the TopWD group when executing statements by group type.
  • off: The database does not automatically switch to the TopWD group when executing statements by group type.

Default value: off

memory_tracking_mode

Parameter description: Specifies the memory information recording mode.

Type: USERSET

Valid value:

  • none: Memory statistics is not collected.
  • normal: Only memory statistics is collected in real time and no file is generated.
  • executor: The statistics file is generated, containing the context information about all allocated memory used by the execution layer.
  • fullexec: The generated file includes the information about all memory contexts requested by the execution layer.

Default value: none

memory_detail_tracking

Parameter description: Specifies the sequence number of the memory background information distributed in the needed thread and plannodeid of the query where the current thread is located.

Type: USERSET

Value range: a string

Default value: empty

It is recommended that you retain the default value for this parameter.

user_metric_retention_time

Parameter description: Specifies the retention time of the user historical resource monitoring data. This parameter is valid only when enable_user_metric_persistent is set to on.

Type: USERSET

Value range: An integer ranging from 0 to 3650. The unit is day.

  • If this parameter is set to 0, user historical resource monitoring data is permanently stored.
  • If the value is greater than 0, user historical resource monitoring data is stored for the specified number of days.

Default value: 7

enable_instance_metric_persistent

Parameter description: Specifies whether the instance resource monitoring dumping function is enabled. When this function is enabled, the instance monitoring data is saved to the system catalog GS_WLM_INSTANCE_HISTORY.

Type: SIGHUP

Value range: Boolean

  • on indicates that the instance resource monitoring dumping function is enabled.
  • off: Specifies that the instance resource monitoring dumping function is disabled.

Default value: on

instance_metric_retention_time

Parameter description: Specifies the retention time of the instance historical resource monitoring data. This parameter is valid only when enable_instance_metric_persistent is set to on.

Type: USERSET

Value range: An integer ranging from 0 to 3650. The unit is day.

  • If this parameter is set to 0, instance historical resource monitoring data is permanently stored.
  • If the value is greater than 0, the instance historical resource monitoring data is stored for the specified number of days.

Default value: 7

resource_track_level

Parameter description: Specifies the resource monitoring level of the current session. This parameter is valid only when enable_resource_track is set to on.

Type: USERSET

Value range: enumerated values

  • none: Resources are not monitored.
  • query: Resources used at the query level are monitored.
  • operator: Resources used at query and operator levels are monitored.

Default value: query

resource_track_cost

Parameter description: Specifies the minimum execution cost for resource monitoring on statements in the current session. This parameter is valid only when enable_resource_track is set to on.

Type: USERSET

Value range: an integer ranging from –1 to INT_MAX

  • –1 indicates that resource monitoring is disabled.
  • A value ranging from 0 to 9 indicates that statements whose execution cost is greater than or equal to 10 will be monitored.
  • A value greater than or equal to 10 indicates that statements whose execution cost exceeds this value will be monitored.

Default value: 100000

resource_track_duration

Parameter description: Specifies the minimum statement execution time that determines whether information about jobs of a statement recorded in the real-time view (see Table 1) will be dumped to a historical view after the statement is executed. Job information will be dumped from the real-time view (with the suffix statistics) to a historical view (with the suffix history) if the statement execution time is no less than this value.

Type: USERSET

Value range: an integer ranging from 0 to INT_MAX. The unit is s.

  • 0 indicates that information about all statements recorded in the real-time resource monitoring view (see Table 1) will be archived into historical views.
  • If the value is greater than 0, information about statements recorded in the real-time resource monitoring view (see Table 1), whose execution time exceeds this value will be archived into historical views.

Default value: 1 min

disable_memory_protect

Parameter description: Stops memory protection. To query system views when system memory is insufficient, set this parameter to on to stop memory protection. This parameter is used only to diagnose and debug the system when system memory is insufficient. Set it to off in other scenarios.

Type: USERSET

Value range: Boolean

  • on indicates that memory protection stops.
  • off indicates that memory is protected.

Default value: off

query_band

Parameter description: Specifies the job type of the current session.

Type: USERSET

Value range: a string

Default value: empty

bbox_dump_count

Parameter description: Specifies the maximum number of core files that are generated by DWS and can be stored in the path specified by bbox_dump_path. If the number of core files exceeds this value, old core files will be deleted. This parameter is valid only if enable_bbox_dump is set to on.

Type: USERSET

Value range: an integer ranging from 1 to 20

Default value: 8

When core files are generated during concurrent SQL statement execution, the number of files may be larger than the value of bbox_dump_count.

io_limits

Parameter description: Specifies the upper limit of IOPS triggered.

Type: USERSET

Value range: an integer ranging from 0 to 1073741823

Default value: 0

io_priority

Parameter description: Specifies the I/O priority for jobs that consume many I/O resources. It takes effect when the I/O usage reaches 90%.

Type: USERSET

Value range: enumerated values

  • None indicates no control.
  • Low indicates that the IOPS is reduced to 20% of the original value.
  • Medium indicates that the IOPS is reduced to 50% of the original value.
  • High indicates that the IOPS is reduced to 80% of the original value.

Default value: None

session_respool

Parameter description: Specifies the resource pool associated with the current session.

Type: USERSET

If you set cgroup_name and then session_respool, the Cgroups associated with session_respool take effect. If you reverse the order, Cgroups associated with cgroup_name take effect.

If the Workload Cgroup level is specified during the cgroup_name change, the database does not check the Cgroup level. The level ranges from 1 to 10.

You are not advised to set cgroup_name and session_respool at the same time.

Value range: a string. This parameter can be set to the resource pool configured through create resource pool.

Default value: invalid_pool

enable_transaction_parctl

Parameter description: whether to control transaction block statements and stored procedure statements.

Type: USERSET

Value range: Boolean

  • on: Transaction block statements and stored procedure statements are controlled.
  • off: Transaction block statements and stored procedure statements are not controlled.

Default value: on

transaction_pending_time

Parameter description: maximum queuing time of transaction block statements and stored procedure statements if enable_transaction_parctl is set to on.

Type: USERSET

Value range: an integer ranging from –1 to INT_MAX. The unit is s.

  • –1 or 0: No queuing timeout is specified for transaction block statements and stored procedure statements. The statements can be executed when resources are available.
  • Value greater than 0: If transaction block statements and stored procedure statements have been queued for a time longer than the specified value, they are forcibly executed regardless of the current resource situation.

Default value: 0

This parameter is valid only for internal statements of stored procedures and transaction blocks. That is, this parameter takes effect only for the statements whose enqueue value (for details, see PG_SESSION_WLMSTAT) is Transaction or StoredProc.

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel