Updated on 2025-05-29 GMT+08:00

Workload Management

If database resource usage is not controlled, concurrent tasks may 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 GaussDB workload management balances the database workload based on available resources to prevent database overloads.

use_workload_manager

Parameter description: Specifies whether to enable the resource management function. This parameter must be enabled for the multi-tenant database feature (enable_mtd).

Parameter type: Boolean.

Unit: none

Value range:

  • on: The resource management function is enabled.
  • off: The resource management function is disabled.

Default value: on

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

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

  • 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 SQL statement:
    1
    SELECT gs_wlm_readjust_user_space(0);
    

enable_control_group

Parameter description: Specifies whether to enable the Cgroup function. This parameter must be enabled for the multi-tenant database feature (enable_mtd).

Parameter type: Boolean.

Unit: none

Value range:

  • on: The Cgroups function is enabled.
  • off: The Cgroups function is disabled.

Default value: on

Setting method: This parameter is a SUSET parameter. Set it based on the corresponding setting method in Table 1. When you change the parameter value using method 2 in the table, you need to restart the node for the Cgroups function to take effect.

Setting suggestion: Retain the default value. Enable this parameter when a multi-tenant database is used.

Risks and impacts of improper settings: If this parameter and the resource management function (use_workload_manager) are enabled at the same time, the user resource management function takes effect, deteriorating the performance of jobs executed by users.

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 can be set at the PDB level.

Parameter type: Boolean.

Unit: none

Value range:

  • on: A statement is automatically switched to the TopWD group when the statement is executed by control group type.
  • off: A statement is not automatically switched to the TopWD group when the statement is executed by control group type.

Default value: off. In the PDB scenario, if this parameter is not set, the global setting is inherited.

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

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

This parameter is valid only when enable_control_group is set to on.

cgroup_name

Parameter description: Specifies the name of the Cgroup in use and 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.

Parameter type: string.

Unit: none

Value range: name of an existing Cgroup.

Default value: "InvalidGroup"

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

Setting suggestion: Do not use both cgroup_name and session_respool.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

enable_backend_control

Parameter description: Specifies whether the database permanent thread is bound to the DefaultBackend Cgroup. This parameter must be enabled for the multi-tenant database feature (enable_mtd).

Parameter type: Boolean.

Unit: none

Value range:

  • on: Permanent threads are bound to the DefaultBackend Cgroup.
  • off: Permanent threads are not bound to the DefaultBackend Cgroup.

Default value: off

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

Setting suggestion: This parameter is disabled by default. If this parameter is enabled, the backend thread CPU control function is enabled. You can enable this parameter to limit the CPUs used by backend threads if the CPU usage is high.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

This parameter is valid only when enable_control_group is set to on.

enable_vacuum_control

Parameter description: Specifies whether autovacuum worker in the database permanent thread is bound to the Vacuum Cgroup. This parameter must be enabled for the multi-tenant database feature (enable_mtd).

Parameter type: Boolean.

Unit: none

Value range:

  • on: The autovacuum worker thread is bound to the Vacuum Cgroup.
  • off: The autovacuum worker thread is not bound to the Vacuum Cgroup.

Default value: off

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

Setting suggestion: This parameter is disabled by default. If this parameter is enabled, the CPU control function of the backend VACUUM thread is enabled. You can enable this parameter to limit the CPUs used by the VACUUM thread if the CPU usage is high.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

This parameter is valid only when enable_control_group is set to on.

cpu_collect_timer

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

Parameter type: integer.

Unit: second

Value range: 1 to 2147483647

Default value: 30

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1. The value cannot contain a unit.

Setting suggestion: A 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.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

memory_tracking_mode

Parameter description: Specifies the memory information recording mode. This parameter can be set at the PDB level.

Parameter type: enumerated type

Unit: none

Value range:

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

Default value: none. In the PDB scenario, if this parameter is not set, the global setting is inherited.

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

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

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 used only in the debug version. This parameter can be set at the PDB level.

Parameter type: string.

Unit: none

Value range: a string.

Default value: "". In the PDB scenario, if this parameter is not set, the global setting is inherited.

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

Setting suggestion: Retain the default value. You are not allowed to set this parameter.

Risks and impacts of improper settings: none

enable_resource_track

Parameter description: Specifies whether to enable the real-time resource monitoring function.

Parameter type: Boolean.

Unit: none

Value range:

  • on: The resource monitoring is enabled.
  • off: The resource monitoring is disabled.

Default value: on

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

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

enable_resource_record

Parameter description: Specifies whether resource monitoring records are archived.

Parameter type: Boolean.

Unit: none

Value range:

  • on: The resource monitoring records are archived.
  • off: The resource monitoring records are not archived.

Default value: off

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

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

enable_logical_io_statistics

Parameter description: Specifies whether to enable the logical I/O statistics function during resource monitoring. If this function is enabled, fields in the PG_TOTAL_USER_RESOURCE_INFO view such as read_kbytes, write_kbytes, read_counts, write_counts, read_speed, and write_speed collect statistics on the number of logical read/write bytes, number of read/write times, and the read/write speed.

Parameter type: Boolean.

Unit: none

Value range:

  • on: The logical I/O statistics function is enabled.
  • off: The logical I/O statistics function is disabled.

Default value: on

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

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

enable_user_metric_persistent

Parameter description: Specifies whether to dump the historical monitoring data of user resources. If this function is enabled, data in the PG_TOTAL_USER_RESOURCE_INFO view is periodically sampled and saved to the GS_WLM_USER_RESOURCE_HISTORY system catalog. For details, see "System Catalogs and System Views > System Views > GS_WLM_USER_RESOURCE_HISTORY" in Developer Guide.

Parameter type: Boolean.

Unit: none

Value range:

  • on: The historical monitoring data of user resources is dumped.
  • off: The historical monitoring data of user resources is not dumped.

Default value: on

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

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

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.

Parameter type: integer.

Unit: day

Value range: 0–3650

  • If this parameter is set to 0, the historical monitoring data of user resources 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

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1. The unit must be d if required.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

enable_instance_metric_persistent

Parameter description: Specifies whether the instance resource monitoring data is dumped. If this function is enabled, instance monitoring data is saved to the GS_WLM_INSTANCE_HISTORY system catalog. For details, see "System Catalogs and System Views > System Catalogs > GS_WLM_INSTANCE_HISTORY" in Developer Guide.

Parameter type: Boolean.

Unit: none

Value range:

  • on: The instance resource monitoring data is dumped.
  • off: The instance resource monitoring data is not dumped.

Default value: on

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

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

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.

Parameter type: integer.

Unit: day

Value range: 0–3650

  • 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

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1. The unit must be d if required.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

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 can be set at the PDB level.

Parameter type: enumerated type

Unit: none

Value range:

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

Default value: query. In the PDB scenario, if this parameter is not set, the global setting is inherited.

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

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

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 can be set at the PDB level.

Parameter type: integer.

Unit: none

Value range: –1 to 2147483647

  • –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. In the PDB scenario, if this parameter is not set, the global setting is inherited.

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

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

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 can be set at the PDB level.

Parameter type: integer.

Unit: second

Value range: 0 to 2147483647

  • 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: 60. In the PDB scenario, if this parameter is not set, the global setting is inherited.

Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1. For example, if the value is 3600 without a unit, resource_track_duration indicates 3600s. If the value is 1h, resource_track_duration indicates 1 hour. If the unit is required, the value must be s, min, h, or d.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

disable_memory_protect

Parameter description: Specifies whether to stop 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 can be set at the PDB level.

Parameter type: Boolean.

Unit: none

Value range:

  • on: Memory protection is disabled.
  • off: Memory protection is enabled.

Default value: off. In the PDB scenario, if this parameter is not set, the global setting is inherited.

Setting method: This parameter is a USERSET parameter and is valid only for the current session. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

query_band

Parameter description: Specifies the user-defined job type of the current session. This parameter can be set at the PDB level.

Parameter type: string.

Unit: none

Value range: a string.

Default value: "". In the PDB scenario, if this parameter is not set, the global setting is inherited.

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

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

memory_fault_percent

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

Parameter type: integer.

Unit: none

Value range: 0 to 2147483647

Default value: 0

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

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

enable_bbox_dump

Parameter description: Specifies whether to enable the black box function and whether to generate core files when the core mechanism is not configured in the system.

Parameter type: Boolean.

Unit: none

Value range:

  • on: The black box function is enabled.
  • off: The black box function is disabled.

Default value: on

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

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

The generation of core files by the black box depends on the open ptrace API of the OS. If the permission is insufficient (errno = 1), ensure that /proc/sys/kernel/yama/ptrace_scope is configured properly.

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.

When core files are generated concurrently, the number of files may be larger than the value of bbox_dump_count.

Parameter type: integer.

Unit: none

Value range: 1 to 20.

Default value: 8

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

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

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.

Parameter type: string.

Unit: none

Value range: valid file path.

Default value: "". 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. Alternatively, use the directory specified during installation.

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

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

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.

Parameter type: string.

Unit: none

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

Default value: "", 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: dual-write data buffer.
  • XLOG_MESSAGE_SEND: buffer for sending primary/standby replication logs.
  • WALRECEIVER_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.
  • BUCKET_XLOG_MESSAGE_SEND: buffer for sending replay logs during bucket scale-out.

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

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

enable_ffic_log

Parameter description: Specifies whether to enable the first failure information capture (FFIC) function.

Parameter type: Boolean.

Unit: none

Value range:

  • on: The FFIC function is enabled.
  • off: The FFIC function is disabled.

Default value: on

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

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

io_limits

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

Parameter type: integer.

Unit: none

Value range: 0 to 1073741823

Default value: 0

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

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

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

Parameter type: enumerated type

Unit: none

Value range:

  • None: no limitation.
  • Low: The IOPS is reduced to 10% of the original value.
  • Medium: The IOPS is reduced to 20% of the original value.
  • High: The IOPS is reduced to 50% of the original value.

Default value: None

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

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

io_control_unit

Parameter description: Specifies the unit used to count the number of I/Os during I/O control in row-store scenarios.

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

Parameter type: integer.

Unit: none

Value range:1000 to 1000000

Default value: 6000

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

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

session_respool

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

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.

Parameter type: string.

Unit: none

Value range: name of the resource pool set by running create resource pool.

Default value: "invalid_pool"

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

Setting suggestion: Do not use both cgroup_name and session_respool.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

session_statistics_memory

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

Parameter type: integer.

Unit: KB

Value range: 5120 to 2147483647

Default value: 5120 (that is, 5 MB)

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1. For example, if the value is 16384 without a unit, session_statistics_memory indicates 16384 KB. If the value is 16MB, session_statistics_memory indicates 16 MB. The unit must be KB, MB, or GB if required.

Setting suggestion: You are advised to set this parameter to a value less than or equal to 50% of the value of max_process_memory.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

session_history_memory

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

Parameter type: integer.

Unit: KB

Value range: 10240 to 2147483647

Default value: 10240 (that is, 10 MB)

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1. For example, if the value is 16384 without a unit, session_history_memory indicates 16384 KB. If the value is 16MB, session_history_memory indicates 16 MB. The unit must be KB, MB, or GB if required.

Setting suggestion: You are advised to set this parameter to a value less than or equal to 50% of the value of max_process_memory.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

topsql_retention_time

Parameter description: Specifies the retention period of historical TopSQL data in the gs_wlm_operator_info table.

Parameter type: integer.

Unit: day

Value range: 0–3650

  • 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

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1. The value cannot contain a unit.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

transaction_pending_time

Parameter description: Specifies the maximum queuing time of transaction block statements and stored procedure statements. This parameter can be set at the PDB level.

Parameter type: integer.

Unit: second

Value range: –1 to 1073741823.

  • –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. In the PDB scenario, if this parameter is not set, the global setting is inherited.

Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1. The value cannot contain a unit.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

current_logic_cluster

Parameter description: Displays the name of the current logical database instance.

Parameter type: string.

Unit: none

Value range: logical database instance name.

Default value: ""

Setting method: This is a fixed INTERNAL parameter. It can be viewed but cannot be modified.

Setting suggestion: This parameter can only be viewed.

Risks and impacts of improper settings: none

resilience_ctrlslot_available_maxpercent

Parameter description: Specifies the maximum percentage of threads in the thread pool that can be occupied by slow SQL statements. This parameter is valid only for SELECT statements executed by non-SYSADMIN/MONADMIN users.

Parameter type: integer.

Unit: none

Value range: 0 to 100

Default value: 100, indicating that slow SQL statements can occupy all threads in the thread pool.

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

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

resilience_ctrlstmt_detect_timelimit

Parameter description: Specifies the execution time of a normal SQL statement after which it is marked as a slow SQL statement. The value 0 indicates that slow SQL statements are not identified. A value greater than 0 indicates that the SQL statement is marked as a slow SQL statement when its execution time exceeds the value of this parameter. This parameter is valid only for SELECT statements executed by non-SYSADMIN/MONADMIN users.

Parameter type: integer.

Unit: millisecond

Value range: 0 to 2147483647. 0 indicates that the slow SQL detection function is disabled.

Default value: 0

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1. For example, if the value is 6000 without a unit, resilience_ctrlstmt_detect_timelimit indicates 6000 ms. If the value is 6s, resilience_ctrlstmt_detect_timelimit indicates 6s. The unit must be ms, s, min, h, or d if required.

Setting suggestion: Set this parameter based on the average SQL execution duration of the real-time database.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

resilience_ctrlstmt_control_iopslimit

Parameter description: Specifies the maximum IOPS that can be used by slow SQL statements after normal SQL statements are marked as slow SQL statements. This parameter is valid only for SELECT statements executed by non-SYSADMIN/MONADMIN users.

Parameter type: string.

Unit: none

Value range: "Low", "Medium", "High", "None", or a value ranging from 0 to 2147483647. The lower the level, the stricter the control.

  • "Low": low level.
  • "Medium": medium level.
  • "High": high level.
  • "None": Logical I/O control for slow SQL statements is disabled.
  • 0 to 2147483647: A smaller value indicates stricter and a larger value indicates looser.

Default value: "None"

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1. If this parameter is set to a number, the value must be a character string, for example, resilience_ctrlstmt_control_iopslimit = '1024'.

Setting suggestion: Set this parameter to a small value when I/O access is full and to a large value when I/O access is idle.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

session_max_dynamic_memory

Parameter description: Specifies the upper limit of the session-level memory. If the session memory usage exceeds the value of this parameter, an error is reported and the session is interrupted. This parameter is valid only when enable_memory_limit is set to on. This parameter can be set at the PDB level.

Parameter type: integer.

Unit: KB

Value range: –1 to 2147483647

Default value: –1, indicating no control. In the PDB scenario, if this parameter is not set, the global setting is inherited.

Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1. The value cannot contain a unit.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: The parameter cannot be set to a small value. If the value is too small, the session fails to execute statements due to insufficient memory. If the parameter is set to a value by using method 3 in Table 2 but the value cannot be set larger due to insufficient memory, you can only exit the session to eliminate the restriction. If the value is less than 16 MB, a message is displayed.

max_concurrency

Parameter description: Specifies the upper limit of global concurrency.

Parameter type: integer.

Unit: none

Value range: –1 to 2147483647

Default value: –1, indicating no control.

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

Setting suggestion: A database administrator needs to adjust the value of this parameter based on the usage of system resources (such as CPU, I/O, and memory resources) to maximize the number of concurrent jobs and prevent system breakdown caused by excessive concurrent jobs.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

enable_workload_rule

Parameter description: Specifies whether to enable the SQL statement concurrency control.

Parameter type: Boolean.

Unit: none

Value range:

  • on indicates that the SQL statement concurrency control is enabled.
  • off indicates that the SQL statement concurrency control is disabled.

Default value: on

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

Setting suggestion: If the SQL statement concurrency control function is not required, set this parameter to off.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

workload_uncontrolled_users

Parameter description: Specifies the user whitelist for workload management. Workload management is not performed on users in the whitelist. Currently, this parameter takes effect only for SQL statement concurrency control.

Parameter type: string.

Unit: none

Value range: a list of users separated by commas (,), for example, user1, user2, user3.

Default value: ""

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

Setting suggestion: Set this parameter based on service requirements. If the users without the SYSADMIN permission do not want to be restricted by workload management when running SQL statements, they can add this parameter.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.