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

    NOTE:

    • 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:
    select gs_wlm_readjust_user_space(0);
    

Default value: off

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 take effect. If you reverse the order, the cgroup 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.

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: InvalidGroup

cpu_collect_timer

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

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 *INT*MAX_. The unit is second.

Default value: 30

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

Value range: a string

Default value: empty

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

enable_resource_track

Parameter description: Specifies whether the real-time resource monitoring function is enabled.

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.

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

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 user historical resource monitoring dumping function is enabled. 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 period of the user historical resource monitoring data. This parameter is valid only when enable_user_metric_persistent is set to on.

This parameter is a SIGHUP parameter. Set it based on methods 1 and 2 provided in Table 2.

Value range: an integer ranging from 0 to 730. 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 methods 1 and 2 provided in Table 2.

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.

Valid value: 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 *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 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 *INT*MAX_. The unit is second.

  • 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

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 it based on instructions provided 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

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

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

NOTICE:

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

bbox_dump_count

Parameter description: Specifies the maximum number of core files that are generated by openGauss 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

NOTE: 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 for the directory, the black box core files will be generated in the data directory of the database. Alternatively, use the directory specified during installation.

enable_ffic_log

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

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

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 instructions provided 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 instructions provided in Table 1.

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

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

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: 5MB

topsql_retention_time

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

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

Value range: an integer ranging from 0 to 730. 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

transaction_pending_time

Parameter description: maximum queuing time of transaction block statements and stored procedure statements.

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

Value range: an integer ranging from –1 to *INT*MAX/2_. The unit is second.

  • –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

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

current_logic_cluster

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

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

Value range: a string

Default value: empty

Feedback
编组 3备份
    openGauss 2024-04-15 00:46:08
    cancel