Load Management

If database resource usage is not controlled, concurrent tasks easily preempt resources. As a result, the OS will be overloaded and cannot respond to user tasks; or even crash and cannot provide any services to users. The MogDB 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 GUC parameters.

Value range: Boolean

• on indicates the resource management function is enabled.

• off indicates the resource management function is disabled.

  NOTE:

  • If method 2 in Table 1 GUC parameters 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 is 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 Cgroups associated with session_respool take effect. If you reverse the order, Cgroups associated with cgroup_name take effect.

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

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

You are not advised 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 node.

The database administrator changes the value of this parameter based on system resources (for example, CPU, I/O, and memory resources) so that the system fully supports the concurrency tasks and avoids too many concurrency tasks resulting in system crash.

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

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 GUC parameters.

Value range:

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

Default value: none

memory_detail_tracking

Parameter description: Sets 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 GUC parameters.

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 GUC parameters.

Value range: Boolean

• on indicates the resource monitoring function is enabled.
• off indicates the resource monitoring function 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 GUC parameters.

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 the resource monitoring logical I/O statistics function is enabled. 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 GUC parameters.

Value range: Boolean

• on indicates that the resource monitoring logical I/O statistics function is enabled.
• off indicates that the resource monitoring logical I/O statistics 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 function is enabled, data in view PG_TOTAL_USER_RESOURCE_INFO is periodically sampled and saved to system catalog GS_WLM_USER_RESOURCE_HISTORY.

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

Value range: Boolean

on indicates that the user historical resource monitoring dumping function is enabled.

off indicates that the user historical resource monitoring dumping function is disabled.

Default value:on

user_metric_retention_time

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

This parameter is a USERSET parameter. Set it based on methods 1 and 2 provided in Table 2 Methods for setting GUC parameters.

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

If this parameter is set to 0, user historical resource monitoring data is permanently stored.

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

Default value: 7

enable_instance_metric_persistent

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

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

Value range: Boolean

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

Default value: on

instance_metric_retention_time

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

This parameter is a USERSET parameter. Set it based on methods 1 and 2 provided in Table 2 Methods for setting GUC parameters.

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

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

Default value: 7

resource_track_level

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

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

Valid value: enumerated values

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

Default value: query

resource_track_cost

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

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

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 GUC parameters.

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 GUC parameters.

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 GUC parameters.

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 through 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 GUC parameters.

Value range: Boolean

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

Default value: off

bbox_dump_count

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

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 GUC parameters.

Value range: a string

Default value: empty The default path where the black box core files are generated is /proc/sys/MogDB/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 the FFIC (First Failure Info Capture) function is enabled.

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

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 IOPS triggered.

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

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 GUC parameters.

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 GUC parameters.

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 GUC parameters.

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

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

You are advised not 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 GUC parameters.

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 GUC parameters.

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 GUC parameters.

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

Default value: 10MB

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 GUC parameters.

Value range: an integer ranging from -1 to INT_MAX/2. The unit is s.

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

Default value: 0