Workload Management

use_workload_manager

Parameter description: Specifies whether to enable the resource management function. This parameter must be set to a same value on CNs and DNs.

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

Value range: Boolean

• on indicates that the resource management function is enabled.
• off indicates that the resource management function is disabled.
  

  • If method 2 in Table 1 is used to change the parameter value, the new value takes effect only for the threads that are started after the change. In addition, the new value does not take effect for new jobs that are executed by backend threads and reused threads. You can make the new value take effect for these threads by using kill session or restarting the node.
  • After the value of use_workload_manager changes from off to on, statistics about storage resources when use_workload_manager was off are not collected. To collect statistics about such resources, run the following statement:

    1	select gs_wlm_readjust_user_space(0);

Default value: on

enable_control_group

Parameter description: Specifies whether to enable the Cgroups. This parameter must be set to a same value on CNs and DNs.

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

Value range: Boolean

• on indicates that the Cgroups are enabled.
• off indicates that the Cgroups are disabled.

Default value: on

If method 2 in Table 1 is used to change the parameter value, the new value takes effect only for the threads that are started after the change. In addition, the new value does not take effect for new jobs that are executed by backend threads and reused threads. You can make the new value take effect for these threads by using kill session or restarting the node.

enable_backend_control

Parameter description: Specifies whether to move database permanent threads to the DefaultBackend control group. This parameter must be set to a same value on CNs and DNs.

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

Value range: Boolean

• on indicates that permanent threads are moved to the DefaultBackend control group.
• off indicates that permanent threads are not moved to the DefaultBackend control group.

Default value: on

enable_vacuum_control

Parameter description: Specifies whether to move the autovacuum worker thread to the Vacuum control group. This parameter must be set to a same value on CNs and DNs.

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

Value range: Boolean

• on indicates that the autovacuum worker thread is moved to the Vacuum control group.
• off indicates that the autovacuum worker thread is not moved to the Vacuum control group.

Default value: on

enable_perm_space

Parameter description: Specifies whether to enable the perm space function. This parameter must be set to a same value on CNs and DNs.

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

Value range: Boolean

• on indicates that the perm space function is enabled.
• off indicates that the perm space function is disabled.

Default value: on

enable_verify_active_statements

Parameter description: Specifies whether to enable the background calibration during static self-adaptive workload balancing. This parameter must be used on CNs.

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

Value range: Boolean

• on indicates that the background calibration is enabled.
• off indicates that the background calibration is disabled.

Default value: on

max_active_statements

Parameter description: Specifies the maximum number of concurrent jobs in each CN. This parameter can be used in only CNs.

The database administrator should set the value of this parameter based on system resources (for example, CPU, I/O, and memory resources) to ensure that the system resources can be fully utilized and the system will not be crashed by too many concurrent jobs.

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

Value range: an integer ranging from –1 to 2147483647 The values –1 and 0 indicate that the number of concurrent jobs is not limited.

parctl_min_cost

Parameter description: Specifies the execution cost threshold of a statement. If the execution cost of a statement exceeds the specified value, the statement is subject to the concurrent limit of a resource pool. (The current feature is a lab feature. Contact Huawei engineers for technical support before using it.)

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

Value range: an integer ranging from –1 to 2147483647

• If the value is –1 or the cost of executing a statement is less than 10, the statement is not subject to the concurrency limit of the resource pool.
• If the value is greater than or equal to 0, enable_dynamic_workload is set to off, and the cost of executing a statement exceeds the value and is greater than or equal to 10, the statement is subject to the concurrency limit of the resource pool.

Default value: 100000

cgroup_name

Parameter description: Specifies the name of the Cgroup in use or changes the priority of items in the queue of the Cgroup.

If you set cgroup_name and then session_respool, the Cgroup associated with session_respool takes effect. If you reverse the order, the Cgroup associated with cgroup_name takes 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.

This parameter is a USERSET parameter. Set it based on method 3 in Table 1.

You are advised not 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 that belongs to the Timeshare Cgroup under the DefaultClass Cgroup.

cpu_collect_timer

Parameter description: Specifies how frequently CPU data is collected during statement execution on DNs.

The database administrator should set a proper collection frequency based on system resources (for example, CPU, I/O, and memory resources). A too small value will affect the execution efficiency and a too large value will reduce the accuracy of exception handling.

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

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

Default value: 30

enable_cgroup_switch

Parameter description: Specifies whether a statement is automatically switched to the TopWD group when the statement is executed by control group type.

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

Value range: Boolean

• on indicates that a statement is automatically switched to the TopWD group when the statement is executed by control group type.
• off indicates that a statement is not automatically switched to the TopWD group when the statement is executed by control group type.

Default value: off

memory_tracking_mode

Parameter description: Specifies the memory information recording mode.

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

Value range:

• none indicates that memory statistics are not collected.
• peak indicates that statistics on the peak value of the query level memory are collected. The value is recorded in the database log and can also be output by using explain analyze.
• normal indicates that memory statistics are collected in real time but no file is generated.
• executor indicates that a statistics file is generated, containing the context information of all allocated memory used on the execution layer.
• fullexec indicates that a statistics file is generated, containing the information about all memory contexts requested by the execution layer.

Default value: none

memory_detail_tracking

Parameter description: Specifies the memory context allocation priority of a thread and the plannodeid of the query for which the current thread is running. This parameter is only suitable for the DEBUG version.

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

Value range: a string

Default value: –1 for Memory Context Sequent Count and Plan Nodeid, indicating an empty value

You are advised to retain the default value for this parameter.

enable_resource_track

Parameter description: Specifies whether the real-time resource monitoring is enabled. This parameter must be set to a same value on CNs and DNs.

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

Value range: Boolean

• on indicates that the resource monitoring is enabled.
• off indicates that the resource monitoring is disabled.

Default value: on

enable_resource_record

Parameter description: Specifies whether resource monitoring records are archived. If this parameter is set to on, records in the history views (GS_WLM_SESSION_HISTORY and GS_WLM_OPERATOR_HISTORY) are archived to the corresponding info views (GS_WLM_SESSION_INFO and GS_WLM_OPERATOR_INFO) at an interval of 3 minutes. After being archived, the records are deleted from the history views. This parameter must be set to a same value on CNs and DNs.

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

Value range: Boolean

• on indicates that the resource monitoring records are archived.
• off indicates that the resource monitoring records are not archived.

Default value: off

enable_logical_io_statistics

Parameter description: Specifies whether to enable the logical I/O statistics function during resource monitoring. If this function is enabled, the read_kbytes, write_kbytes, read_counts, write_counts, read_speed, and write_speed fields in the PG_TOTAL_USER_RESOURCE_INFO view will collect statistics on the byte count, number of times, and speed of logical read and write. Fields related to logical read and write in the system catalogs GS_WLM_USER_RESOURCE_HISTORY and GS_WLM_INSTANCE_HISTORY will collect statistics on the logical read and write of related users and instances.

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

Value range: Boolean

• on indicates that the function is enabled.
• off indicates that the function is disabled.

Default value: on

enable_user_metric_persistent

Parameter description: Specifies whether the historical monitoring data of user resources is dumped. If this parameter is set to on, data in the PG_TOTAL_USER_RESOURCE_INFO view is periodically sampled and saved to the system catalog GS_WLM_USER_RESOURCE_HISTORY.

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

Value range: Boolean

on indicates that the historical monitoring data of user resources is dumped.

off indicates that the historical monitoring data of user resources is not dumped

Default value: on

user_metric_retention_time

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

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

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

If this parameter is set to 0, the historical monitoring data of user resources is permanently stored.

If the value is greater than 0, the historical monitoring data of user resources is stored for the specified number of days.

Default value: 7

enable_instance_metric_persistent

Parameter description: Specifies whether the instance resource monitoring data is dumped. When this parameter is set to on, the instance monitoring data is saved to the system catalog GS_WLM_INSTANCE_HISTORY.

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

Value range: Boolean

• on indicates that the instance resource monitoring data is dumped.
• off indicates that the instance resource monitoring data is not dumped.

Default value: on

instance_metric_retention_time

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

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

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

• If this parameter is set to 0, the historical monitoring data of instance resources is permanently stored.
• If the value is greater than 0, the historical monitoring data of instance resources 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.

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

Value range: enumerated values

• none indicates that resources are not monitored.
• query indicates that resources used at the query level are monitored.
• operator indicates that 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.

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

Value range: an integer ranging from –1 to 2147483647

• –1 indicates that resource monitoring is disabled.
• If the value is greater than or equal to 0:
  • 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 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. This parameter is valid only when enable_resource_track is set to on.

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

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

• 0 indicates that historical information about all statements recorded in the real-time resource monitoring view are archived.
• If the value is greater than 0, historical information about a statement whose execution time exceeds this value will be archived.

Default value: 1min

dynamic_memory_quota

Parameter description: Specifies the memory quota in adaptive workload scenarios, that is, the proportion of maximum available memory to total system memory.

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

Value range: an integer ranging from 1 to 100

Default value: 80

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.

This parameter is a USERSET parameter and is valid only for the current session. Set this parameter following the method 3 in Table 1.

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.

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

Value range: a string

Default value: empty

enable_bbox_dump

Parameter description: Specifies whether the black box function is enabled. The core files can be generated even when the core dump mechanism is not configured in the system. This function is valid only for CNs or DNs. For CMA, CMS, GTM, and fenced UDF, the system core mechanism must be configured to capture core files.

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

Value range: Boolean

• on indicates that the black box function is enabled.
• off indicates that the black box function is disabled.

Default value: on

The generation of core files by the black box function depends on the open ptrace interface of the operating system. If the permission is insufficient (errno = 1), ensure that the /proc/sys/kernel/yama/ptrace_scope configuration is correct.

enable_ffic_log

Parameter description: Specifies whether to enable the first failure information capture (FFIC) function. This function is valid only for CNs or DNs. For CMA, CMS, GTM, and fenced UDF, the system core mechanism must be configured to capture core files.

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

Value range: Boolean

• on indicates that the FFIC function is enabled.
• off indicates that the FFIC function is disabled.

Default value: on

enable_dynamic_workload

Parameter description: Specifies whether to enable the dynamic workload management function.

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

Value range: Boolean

• on indicates that the dynamic workload management function is enabled.
• off indicates that the dynamic workload management function is disabled.

• If memory adaptation is enabled, you do not need to use work_mem to optimize the operator memory usage. The system will generate a plan for each statement based on the current workload, estimating the memory used by each operator and by the entire statement. In a concurrency scenario, statements are queued based on the system workload and their memory usage.
• In some cases, the optimizer cannot accurately estimate the number of rows and thereby underestimates or overestimates memory usage. If the memory usage is underestimated, the allocated memory will be automatically increased during statement running. If the memory usage is overestimated, system memory resources will not be fully used, and the number of statements waiting in a queue will increase, which probably results in low performance. To improve performance, identify the statements whose estimated memory usage is much greater than the peak memory of the DN and adjust the value of query_mem accordingly. For details, see Configuring Key Parameters for SQL Tuning.
• Importing column-store partitioned tables consumes many memory resources and is performance-sensitive. Dynamic workload management is not recommended for such an import.

enable_acceleration_cluster_wlm

Due to specification changes, the current version no longer supports the current feature. Do not use this feature.

Parameter description: Specifies whether to enable the dynamic workload management function to accelerate the cluster. This parameter is valid only for computing resource pools. If this parameter is set to on in the cluster, the related logic is not enabled.

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

Value range: Boolean

• on indicates that the dynamic workload management function is enabled for the cluster.
• off indicates that the dynamic workload management function is disabled for the cluster.

Default value: off

enable_dywlm_adjust

Parameter description: Specifies whether inaccurate resource values will be dynamically adjusted. This parameter must be set to a same value on CNs and DNs.

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

Value range: Boolean

• on indicates that inaccurate resource values will be dynamically adjusted.
• off indicates that inaccurate resource values will not be dynamically adjusted.

Default value: on

enable_force_memory_control

Parameter description: Specifies whether to control simple queries based on memory usage when the concurrency control is enabled in a resource pool. (The current feature is a lab feature. Contact Huawei engineers for technical support before using it.) This parameter must be set to a same value on CNs and DNs.

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

Value range: Boolean

• on indicates that simple queries are controlled.
• off indicates that simple queries are not controlled.

Default value: off

enable_reaper_backend

Parameter description: Specifies whether the signal sent by a subthread when it exits is collected by a separate thread.

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

Value range: Boolean

• on indicates that the signal is collected by a separate thread.
• off indicates that the signal is not collected by a separate thread.

Default value: on

memory_fault_percent

Parameter description: Specifies the percentage of memory application failures during the memory fault test. This parameter is used only in the DEBUG version.

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

Value range: an integer ranging from 0 to 2147483647

Default value: 0

bbox_dump_count

Parameter description: Specifies the maximum number of core files that are generated by GaussDB 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 when enable_bbox_dump is set to on.

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

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.

bbox_dump_path

Parameter description: Specifies the path where the black box core files are generated. This parameter is valid only when enable_bbox_dump is set to on.

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

Value range: a string

Default value: empty The default path where the black box core files are generated is /proc/sys/kernel/core_pattern. If the path is not a directory or you do not have the write permission on the directory, black box core files will be generated under the data directory of the database.

bbox_blanklist_items

Parameter description: Specifies the anonymized data items of black box core files. This parameter is valid only when enable_bbox_dump is set to on.

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

Value range: a string of sensitive data items separated by commas (,).

Default value: empty which indicates that all supported sensitive data items of the core files generated by the black box are anonymized.

Currently, the following data items can be anonymized:

• SHARED_BUFFER: data buffer
• XLOG_BUFFER: redo log buffer
• DW_BUFFER: doublewrite data buffer
• XLOG_MESSAGE_SEND: buffer for sending primary/standby replication logs
• WALRECIVER_CTL_BLOCK: buffer for receiving primary/standby replication logs
• DATA_MESSAGE_SEND: buffer for sending primary/standby replication data
• DATA_WRITER_QUEUE: buffer for receiving primary/standby replication data

bypass_workload_manager

Parameter description: Specifies whether to enable I/O control. This parameter must be set to a same value on CNs and DNs.

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

If use_workload_manager is not set to on, this parameter can be used to enable the I/O control independently. After the I/O control is enabled, you can set io_limits or io_priority to configure control details.

Value range: Boolean

• on indicates that the I/O control is enabled.
• off indicates that the I/O control is disabled.

io_limits

Parameter description: Specifies the upper limit of Input/output operations per second (IOPS).

This parameter is a USERSET parameter. Set it based on method 3 in Table 1.

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

This parameter is a USERSET parameter. Set it based on method 3 in Table 1.

Value range: enumerated values

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

Default value: None

io_control_unit

Parameter description: Specifies the unit used to count the number of I/Os during I/O control in row-store scenarios. This parameter must be set to a same value on CNs and DNs.

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

Set a certain number of I/Os as one unit. This unit is used during the I/O control.

Value range: an integer ranging from 1000 to 1000000

Default value: 6000

session_respool

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

This parameter is a USERSET parameter. Set it based on method 3 in Table 1.

If you set cgroup_name and then session_respool, the Cgroup associated with session_respool takes effect. If you reverse the order, the Cgroup associated with cgroup_name takes 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: Specifies whether to control transaction block statements and stored procedure statements.

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

Value range: Boolean

• on indicates that transaction block statements and stored procedure statements are controlled.
• off indicates that transaction block statements and stored procedure statements are not controlled.

Default value: on

session_statistics_memory

Parameter description: Specifies the memory size of a real-time query view.

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

Value range: an integer ranging from 5 x 1024 to 50% of max_process_memory. The unit is KB.

Default value: 5 MB

topsql_retention_time

Parameter description: Specifies the retention period of historical TopSQL data in the gs_wlm_session_query_info_all and gs_wlm_operator_info tables.

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

Value range: an integer ranging from 0 to 3650. The unit is day. If it is set to 0, the data is stored permanently. If the value is greater than 0, the data is stored for the specified number of days.

Default value: 0

session_history_memory

Parameter description: Specifies the memory size of a historical query view.

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

Value range: an integer ranging from 10 x 1024 to 50% of max_process_memory. The unit is KB.

Default value: 10 MB

node_group_mode

Parameter description: Displays the current node group mode.

This parameter is a fixed INTERNAL parameter and cannot be modified.

Value range: a string

Default value: "node group"

current_logic_cluster

Parameter description: Displays the name of the current logical cluster. (The current feature is a lab feature. Contact Huawei engineers for technical support before using it.)

This parameter is a fixed INTERNAL parameter and cannot be modified.

Value range: a string

Default value: empty

transaction_pending_time

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

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

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

• –1 or 0 indicates that no queuing timeout is specified for transaction block statements and stored procedure statements. The statements can be executed when resources are available.
• A value greater than 0 indicates that 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 is Transaction or StoredProc in PG_SESSION_WLMSTAT.