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