Resource Management Parameters

This document describes the system configuration parameters in the Resource Management category.

Caution!
Manually modifying these parameters may affect system stability and security. Proceed with caution.

Resource Groups

gp_resgroup_memory_policy

Determines how memory is allocated among query operators when resource groups are used.

Note: The gp_resgroup_memory_policy server configuration parameter takes effect only when resource management is set to resource group.
When set to auto, YMatrix Database allocates memory to query operators based on resource group memory limits. Non-memory-intensive operators receive a fixed amount of memory, while the remaining memory is allocated to memory-intensive operators.
When set to eager_free, YMatrix Database improves memory allocation by reassigning memory released by completed operators to operators in subsequent query stages.

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

gp_resource_group_cpu_limit

Specifies the maximum percentage of CPU resources on each YMatrix Database segment node that can be allocated to resource groups.

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

Data Type	Default Value	Range	Category
floating point	0.9	0.1 ~ 1.0	segments; system; restart

gp_resource_group_cpu_priority

Sets the CPU scheduling priority of YMatrix processes relative to non-YMatrix processes when resource groups are enabled.

For example, setting this parameter to 10 sets the CPU resource ratio between YMatrix and non-YMatrix processes to 10:1.

Data Type	Default Value	Range	Category
int	10	1 ~ 256	segments; system; restart

gp_resource_group_queuing_timeout

Cancels transactions that have been queued in a resource group beyond the specified timeout (in milliseconds).

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

Data Type	Default Value	Range	Category
int	0	0 ~ INT_MAX	master; session; reload

gp_resource_manager

Specifies the resource management scheme currently enabled in the YMatrix Database cluster.

Data Type	Default Value	Range	Category
enum	queue	group / queue	segments; system; restart

memory_spill_ratio

Sets the memory usage threshold percentage for memory-intensive operators within a transaction.

When a transaction reaches this threshold, it spills to disk.
The default memory_spill_ratio percentage is defined by the value assigned to the current active role's resource group.
You can set memory_spill_ratio at the session level to selectively adjust this limit per query. For example, if a specific query spills to disk and requires more memory, you can set a higher memory_spill_ratio to increase initial memory allocation.
When setting memory_spill_ratio at the session level, YMatrix Database does not validate the new value semantically until the next query execution.
The memory_spill_ratio server configuration parameter takes effect only when resource management is set to resource group.

Data Type	Default Value	Range	Category
int	20	0 ~ 100	master; session; reload

mx_cgroup_v1_base

The default base v1 cgroup path used by the resource group feature.

The default is /ymatrix/EmviwvEQDzobZxQi3pX8Co/postgres if deployed via GUI; otherwise, it is an empty string for command-line deployment.

Data Type	Default Value	Category
string		segments; system; restart; superuser

Resource Queues

gp_resqueue_memory_policy

Enables YMatrix memory management functionality.

Note: The gp_resqueue_memory_policy server configuration parameter takes effect only when resource management is set to resource queue.
The eager_free allocation algorithm leverages the fact that not all operators execute simultaneously.
Query plans are divided into multiple stages. YMatrix Database quickly releases memory allocated to completed stages and reallocates it to new stages.
When set to none, memory management behaves as in Greenplum versions prior to 4.1. When set to auto, query memory usage is primarily controlled by the statement_mem parameter and queue memory limits.

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

gp_resqueue_priority

Enables or disables query prioritization.

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

Data Type	Default Value	Category
boolean	on	segments; system; restart

gp_resqueue_priority_cpucores_per_segment

Specifies the number of CPU units allocated per segment instance.

The master host typically runs only the master instance, so the value should reflect the total available CPU cores on the host.
Incorrect settings may lead to underutilization of CPU or improper query prioritization.
Note: The gp_resqueue_priority_cpucores_per_segment server configuration parameter takes effect only when resource management is set to resource queue.

Data Type	Default Value	Range	Category
floating point	4.0	0.1 ~ 512.0	segments; system; restart

gp_resqueue_priority_sweeper_interval

Specifies the interval (in milliseconds) at which the sweeper process evaluates 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 takes effect only when resource management is set to resource queue.

Data Type	Default Value	Range	Category
int	1000	500 ~ 15000	segments; system; restart

gp_vmem_protect_limit

Sets the total amount of memory (in MB) that all postgres processes on an active segment instance can consume.

Note: The gp_vmem_protect_limit server configuration parameter takes effect only when resource management is set to resource queue.
If a query exceeds this limit, memory allocation fails and the query is terminated.
Specify only the numeric value. For example, use 4096 for 4096MB. Do not include the unit "MB" in the value.
To prevent excessive memory allocation, use the following calculations to estimate a safe gp_vmem_protect_limit value. First, compute gp_vmem, which represents the YMatrix Database memory available on the host:

gp_vmem = ((SWAP + RAM) – (7.5GB + 0.05 * RAM)) / 1.7

where SWAP and RAM are in GB.

Next, compute max_acting_primary_segments, the maximum number of primary segments that could run on a single host if mirrors are activated due to a cluster failure. For example, in a 4-host block with 8 primaries per host, a single host failure activates 2–3 mirrors on each surviving host.
For this configuration, max_acting_primary_segments is 11 (8 primaries + 3 activated mirrors).
Then compute gp_vmem_protect_limit (convert to MB):

gp_vmem_protect_limit = gp_vmem / acting_primary_segments

For workloads generating many workfiles, use this adjusted formula:

gp_vmem = ((SWAP + RAM) – (7.5GB + 0.05 * RAM - (300KB * total_#_workfiles))) / 1.7

Based on the gp_vmem value, compute the kernel parameter vm.overcommit_ratio:

vm.overcommit_ratio = (RAM - (0.026 * gp_vmem)) / RAM

Note: The default value of kernel parameter vm.overcommit_ratio in Red Hat Enterprise Linux is 50.
If deployed via GUI, this parameter is automatically calculated based on hardware. If deployed via command line, the default is 8192.

Data Type	Default Value	Range	Category
int	11429	0 ~ (INT_MAX/2)	segments; system; restart

max_resource_portals_per_transaction

Sets the maximum number of user-defined cursors that can be open simultaneously per transaction.

Note: Open cursors hold active query slots in resource queues for resource management.
Note: The max_resource_portals_per_transaction server configuration parameter takes effect only when resource management is set to resource queue.

Data Type	Default Value	Range	Category
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 (as are roles), so they apply to all databases in the system.

Data Type	Default Value	Range	Category
int	9	0 ~ INT_MAX	master; system; restart

max_statement_mem

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

Setting statement_mem too high may help avoid out-of-memory errors on segment hosts during query processing.
When gp_resqueue_memory_policy=auto, query memory usage is controlled by statement_mem and resource queue memory limits.
Consider the configuration of a single segment host when calculating this value:

(seghost_physical_memory) / (average_number_concurrent_queries)

When changing max_statement_mem and statement_mem, you must first modify max_statement_mem, or list it first in the postgresql.conf file.
Note: The max_statement_mem server configuration parameter takes effect only when resource management is set to resource queue.

Data Type	Default Value	Range	Category
int	2048000	32768 ~ INT_MAX	master; session; reload; superuser

resource_cleanup_gangs_on_wait

When a statement is submitted via a resource queue, this parameter clears all idle query execution gangs before acquiring the resource queue lock.

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

Data Type	Default Value	Category
boolean	on	master; system; restart

resource_select_only

Determines the types of queries managed by resource queues.

If set to on, only SELECT, SELECT INTO, and CREATE TABLE AS SELECT和DECLARE CURSOR commands are evaluated. If set to off, INSERT, UPDATE, and DELETE commands are also evaluated.
Note: The resource_select_only server configuration parameter takes effect only when resource management is set to resource queue.

Data Type	Default Value	Category
boolean	off	master; system; restart

runaway_detector_activation_percent

Sets the percentage of YMatrix Database vmem usage that triggers query termination.

If vmem usage on a YMatrix Database segment exceeds this percentage, the system terminates queries based on memory consumption, starting with the most memory-intensive. Queries are terminated until vmem usage drops below the specified threshold.
A value of 0 disables automatic query termination based on vmem usage.
Note: The runaway_detector_activation_percent server configuration parameter takes effect only when resource management is set to resource queue.

Data Type	Default Value	Range	Category
int	90	0 ~ 100	segments; system; restart

statement_mem

Allocates memory on segment hosts per query (in KB).

The memory allocated via this parameter must not exceed max_statement_mem or the memory limit of the resource queue submitting the query.
When gp_resqueue_memory_policy =auto, query memory usage is controlled by statement_mem and resource queue memory limits.
If additional memory is required, temporary spill files are used on disk.
Use the following formula to estimate a reasonable value:

( gp_vmem_protect_limitGB * .9 ) / max_expected_concurrent_queries

For example, with gp_vmem_protect_limit set to 8192MB（8GB） and up to 40 concurrent queries (including 10% buffer):

(8GB * .9) / 40 = .18GB = 184MB

When changing max_statement_mem and statement_mem, you must first modify max_statement_mem, or list it first in the postgresql.conf file.
The statement_mem server configuration parameter takes effect only when resource management is set to resource queue.

Data Type	Default Value	Range	Category
int	128000	50 ~ INT_MAX	master; session; reload

← Previous

Lock management

Cluster

English Русский 简体中文