Resource Management Category Parameters

This document describes the relevant parameters in the resource management category of the system configuration parameters.

Caution!
To ensure system stability and security, please exercise caution when manually modifying the relevant parameters.

Resource Group

gp_resgroup_memory_policy

Used by resource groups to manage memory allocation for query operators.

Note: The gp_resgroup_memory_policy server configuration parameter only takes effect when resource management is set to resource group.
When set to auto, the YMatrix database uses the resource group memory limit to allocate memory between query operators, allocating a fixed amount of memory to non-memory-intensive operators and the remaining memory to memory-intensive operators.
When you specify eager_free, the YMatrix database improves memory allocation between operators by reallocating memory released by completed operators to subsequent query phase operators.

Data Type	Default Value	Value Range	Setting Category
enum	eager_free	eager_free / auto	segments；system；restart/reload；superuser

gp_resource_group_cpu_limit

The maximum percentage of system CPU resources to be allocated to each YMatrix database Segment node's resource group.

Note: The gp_resource_group_cpu_limit server configuration parameter only takes effect when resource management is set to resource group.

Data type	Default value	Value range	Setting category
floating point	0.9	0.1 ~ 1.0	segments；system；restart

gp_resource_group_cpu_priority

When resource groups are enabled, set the CPU priority of YMatrix processes relative to non-YMatrix processes.

For example, setting this parameter to 10 sets the ratio of CPU resources allocated to YMatrix processes to non-YMatrix processes to 10:1.

Data type	Default value	Value range	Setting category
int	10	1 ~ 256	segments；system；restart

gp_resource_group_queuing_timeout

Cancel transactions queued in a resource group that have exceeded the wait time (in milliseconds).

The timeout applies to each transaction individually.
The default value is 0; transactions queue indefinitely and never time out.
Note: The gp_resource_group_queuing_timeout server configuration parameter only takes effect when resource management is set to resource group.

Data type	Default value	Value range	Setting category
int	0	0 ~ INT_MAX	master；session；reload

gp_resource_manager

Identifies the resource management scheme currently enabled in the YMatrix database cluster.

Data type	Default value	Value range	Setting category
enum	queue	group / queue	segments；system；restart

memory_spill_ratio

Set the memory usage threshold percentage for memory-intensive operators in a transaction.

When a transaction reaches this threshold, it will spill to disk.
The default memory_spill_ratio percentage is defined for the resource group assigned to the current active role.
You can set memory_spill_ratio at the session level to selectively apply this limit on a per-query basis. For example, if you have a specific query that spills to disk and requires more memory, you can choose to set a larger memory_spill_ratio to increase the initial memory allocation.
When setting memory_spill_ratio at the session level, the YMatrix database does not perform semantic validation on the new value until the next query is executed.
The memory_spill_ratio server configuration parameter only takes effect when resource management is set to resource group.

Data type	Default value	Value range	Setting category
int	20	0 ～ 100	master；session；reload

mx_cgroup_v1_base

The default basic v1 cgroup path for resource group functionality.

If you used a graphical interface for deployment, the default value is /ymatrix/EmviwvEQDzobZxQi3pX8Co/postgres; if you used a command line for deployment, the default value is an empty string.

Data type	Default value	Setting category
string		segments；system；restart；superuser

Resource Resqueue

gp_resqueue_memory_policy

Enable YMatrix memory management.

Note: The gp_resqueue_memory_policy server configuration parameter only takes effect when resource management is set to resource queue.
The eager_free allocation algorithm takes advantage of the fact that not all operators execute simultaneously.
The query plan is divided into several phases. YMatrix quickly releases memory allocated to the previous phase when a phase completes, then allocates the released memory to the new phase.
When set to none, memory management behaves the same as in Greenplum 4.1 and earlier releases; when set to auto, memory usage for queries is primarily controlled by the statement_mem parameter and queue memory limits.

Data Type	Default Value	Value Range	Setting Category
enum	eager_free	eager_free / auto / none	segments；system；restart/reload

gp_resqueue_priority

Enable or disable query priority.

When this parameter is disabled, existing priority settings are not calculated during query execution.
Note: The gp_resqueue_priority server configuration parameter only takes effect when resource management is set to resource queue.

Data type	Default value	Setting category
boolean	on	segments；system；restart

gp_resqueue_priority_cpucores_per_segment

Specify the number of CPU units allocated to each Segment instance.

Master hosts typically only run Master instances, so the host value should reflect the usage of all available CPU cores.
Incorrect settings may result in insufficient CPU utilization or query priorities not functioning as designed.
Note: The gp_resqueue_priority_cpucores_per_segment server configuration parameter only takes effect when resource management is set to resource queues.

Data type	Default value	Value range	Setting category
floating point	4.0	0.1 ～ 512.0	segments；system；restart

gp_resqueue_priority_sweeper_interval

Specify the time interval (in milliseconds) at which the cleanup process evaluates the current CPU usage.

When a new statement becomes active, its priority is evaluated, and its CPU share is determined at the next interval.
Note: The gp_resqueue_priority_sweeper_interval server configuration parameter only takes effect when resource management is set to resource queue.

Data type	Default value	Value range	Setting category
int	1000	500 ～ 15000	segments；system；restart

gp_vmem_protect_limit

Set the amount of memory (in MB) that all postgres processes can use for the Segment node instance.

Note: The gp_vmem_protect_limit server configuration parameter only takes effect when resource management is set to resource queue.
If a query exceeds this parameter limit, memory will not be allocated and the query will fail.
When setting this parameter, specify only the numerical value. For example, to specify 4096 MB, use the value 4096; do not add the unit MB to this value.
To prevent excessive memory allocation, these calculations can estimate a safe gp_vmem_protect_limit value. First, calculate the value gp_vmem. This is the available YMatrix database memory on the host.
```
gp_vmem = ((SWAP + RAM) – (7.5GB + 0.05 * RAM)) / 1.7
```
Where SWAP is the host switch space, and RAM is the RAM on the host, in GB. Next, calculate max_acting_primary_segments. This is the maximum number of Primary that can run on the host when Mirror is activated due to cluster failure. For example, if the Mirror is arranged in 4 host blocks with 8 primary each, a single Segment host failure activates two or three Mirrors on each remaining host in the failed host block. The max_acting_primary_segments value for this configuration is 11 (8 Primary plus 3 Mirrors activated on failure). This is the calculation of gp_vmem_protect_limit. This value should be converted to MB.
```
gp_vmem_protect_limit = gp_vmem / acting_primary_segments
```
For cases where a large number of working files are generated, this is the calculation of gp_vmem of the working file.
```
gp_vmem = ((SWAP + RAM) – (7.5GB + 0.05 * RAM - (300KB * total_#_workfiles))) / 1.7
```
Based on the gp_vmem value, you can calculate the value of the vm.overcommit_ratio operating system kernel parameters. This parameter is set when configuring each YMatrix database host.
```
vm.overcommit_ratio = (RAM - (0.026 * gp_vmem)) / RAM
```
**Note: The default value of the kernel parameter vm.overcommit_ratio in Red Hat Enterprise Linux is 50.
If you use a graphic interface to deploy, this parameter will calculate the adaptation based on the server's hardware resources; if you use the command line to deploy, the default is 8192.

| Data Type | Default Value | Value Range | Set Classification | | --- | --- | | int | 11429 | 0 ～ (INT_MAX/2) | segments; system; restart |

max_resource_portals_per_transaction

Sets the maximum number of user declaration cursors allowed to be opened at the same time per transaction.

Note: Opened cursors will retain active query slots in the resource queue for resource management.
Note: The max_resource_portals_per_transaction server configuration parameter will take effect only when resource management is set to Resource Queue**.

| Data Type | Default Value | Value Range | Set Classification | | --- | --- | | int | 64 | 0 ~ INT_MAX | master; system; restart |

max_resource_queues

Sets the maximum number of resource queues that can be created in the YMatrix database system.

Note: Resource queues are system-wide (and so are roles), so they are suitable for all databases in the system.

| Data Type | Default Value | Value Range | Set Classification | | --- | --- | | int | 9 | 0 ～ INT_MAX | master; system; restart |

max_statement_mem

Sets the maximum memory limit (KB) for the query.

Since setting statement_mem too high helps avoid out-of-memory errors on the Segment host during query processing.
When gp_resqueue_memory_policy=auto, statement_mem and resource queue memory limit control query memory usage.
Considering the configuration of a single Segment host, calculate this setting as follows:
```
(seghost_physical_memory) / (average_number_concurrent_queries)
```
When changing max_statement_mem and statement_mem, you must first change max_statement_mem, or list it in the postgresql.conf file.
Note: The max_statement_mem server configuration parameter will take effect only when resource management is set to the resource queue.

| Data Type | Default Value | Value Range | Set Classification | | --- | --- | | int | 2048000 | 32768 ~ INT_MAX | master; session; reload; superuser |

resource_cleanup_gangs_on_wait

If a statement is submitted through a resource queue, this parameter clears all idle query executor worker processes before locking the resource queue.

Note: The resource_cleanup_gangs_on_wait server configuration parameter will take effect only when resource management is set to Resource Queue**.

| Data Type | Default | Set Category | | --- | --- | | boolean | on | master; system; restart |

resource_select_only

Sets the query type for resource queue management.

If set to on, the SELECT, SELECT INTO, CREATE TABLE AS SELECT, and DECLARE CURSOR commands are evaluated; if set to off, the INSERT, UPDATE and DELETE commands are also evaluated.
Note: The resource_select_only server configuration parameter will take effect only when resource management is set to Resource Queue**.

| Data Type | Default | Set Category | | --- | --- | | boolean | off | master; system; restart |

runaway_detector_activation_percent

Sets the percentage of the vmem memory of the YMatrix database that triggers the query to terminate.

If the vmem memory percentage used for the YMatrix database segment exceeds the specified value, the YMatrix database terminates the query based on memory usage, starting with a query that consumes the maximum amount of memory. The query will terminate until the percentage of vmem used is lower than the specified percentage.
Value 0 Disables automatic termination of query based on percentage of vmem used.
Note: The runaway_detector_activation_percent server configuration parameter will take effect only when resource management is set to the resource queue.

| Data Type | Default Value | Value Range | Set Classification | | --- | --- | | int | 90 | 0 ~ 100 | segments; system; restart |

statement_mem

Allocate Segment host memory (KB) for each query.

The amount of memory allocated with this parameter cannot exceed the memory limit of max_statement_mem or resource queues that submitted the query.
When gp_resqueue_memory_policy =auto, statement_mem and resource queue memory limit control query memory usage.
If the query requires extra memory, use temporary overflow files on disk.
This calculation can be used to estimate reasonable values for various situations.
```
( gp_vmem_protect_limitGB * .9 ) / max_expected_concurrent_queries
```
Set gp_vmem_protect_limit to 8192MB (8GB) and assume that there are up to 40 concurrent queries with 10% buffering.
```
(8GB * .9) / 40 = .18GB = 184MB
```
When changing max_statement_mem and statement_mem, you must first change max_statement_mem, or list it in the postgresql.conf file.
The statement_mem server configuration parameter will take effect only when resource management is set to a resource queue.

| Data Type | Default Value | Value Range | Set Classification | | --- | --- | | int | 128000 | 50 ~ INT_MAX | master; session; reload |