ALTER RESOURCE GROUP

Change the limits for a resource group.

Summary

ALTER RESOURCE GROUP name SET group_attribute value;

Where group_attribute is as follows:

CONCURRENCY integer
CPU_RATE_LIMIT integer 
CPUSET tuple 
MEMORY_LIMIT integer
MEMORY_SHARED_QUOTA integer
MEMORY_SPILL_RATIO integer

describe

ALTER RESOURCE GROUP Changes the limits for a resource group. Only superusers can modify resource groups.

Users can set or reset the parallelism limit for resource groups that control the maximum activity parallelism of the role. Users can also control the total amount of memory or CPU that all queries submitted through the resource group can be used on each Segment host by resetting memory or CPU resources for the resource group.

When a user changes the mode or limit of the resource group CPU resource, the new mode or limit will take effect immediately.

When the user changes the memory limit for the resource group created by the role, the new resource limit is applied immediately if the current resource usage is less than or equal to the new value and there are no running transactions in the resource group. If the current resource usage exceeds the new memory limit value, or if there is a running transaction in another resource group that holds certain resources, the YMatrix database delays allocating a new limit until the resource usage falls within the range of the new value.

When the user promotes the memory limit of the resource group created for external components, the new resource limit takes effect as the resource becomes available. If the user lowers the memory limit for resource groups created for external components, the performance is based on the specific component. For example, if the user reduces the memory limit for resource groups created for the PL/Container runtime, the query in the running container may fail with an out of memory error.

Users can change the type of restriction in a single ALTER RESOURCE GROUP call.

Parameters

name

The name of the resource group to be changed.

CONCURRENCY integer

The maximum number of transaction parallelisms allowed by the user to the resource group, including active and idle transactions. Any transaction to be submitted after the limit value of CONCURRENCY is reached will enter the queue. When a running transaction is completed, the earliest queued transaction will be executed.
The CONCURRENCY value must be an integer in the range [0.. max_connections]. The default CONCURRENCY value of resource groups created by the user for the role is 20.
Note: The user cannot set the CONCURRENCY value of admin_group to zero (0).

CPU_RATE_LIMIT integer

Percentage of CPU resources allocated for the current resource group. The minimum percentage of CPU resources for a resource group is 1 and the maximum is 100. The CPU_RATE_LIMIT values for all resource rentals set for the YMatrix database cluster cannot exceed 100 in total.
If the user modify CPU_RATE_LIMIT for the resource group that has been set before, CPUSET will be disabled, and the reserved CPU core is returned to the YMatrix database, and CPUSET is set to -1.

CPUSET tuple

CPU cores reserved for the current resource group. The CPU cores specified by the user in the tuple must be available on the system, and the user cannot overlay the CPU cores specified in other resource groups to the current resource group.
tuple is a comma-separated single core number or core interval list. You have to enclose the tuple in single quotes, for example '1,3-4'.
If the user modify CPUSET for the resource group that has previously set CPU_RATE_LIMIT, CPU_RATE_LIMIT will be disabled, reserved CPU resources will be returned to the YMatrix database, and then CPU_RATE_LIMIT will be set to -1.
The user can only modify CPUSET for resource groups in a YMatrix database cluster with group management resources enabled.

MEMORY_LIMIT integer

Percentage of memory resources allocated for the current resource group. The minimum percentage of memory resources for a resource group is 0 and the maximum is 100. When set to 0, YMatrix does not allocate any reserved memory for the group, but uses global shared memory. And MEMORY_SPILL_RATIO must also be set to 0.
The sum of MEMORY_LIMIT definitions for all resource groups in the YMatrix database cluster cannot exceed 100. When the sum is less than 100, the remaining part is global shared memory.

MEMORY_SHARED_QUOTA integer

The percentage of memory resources that are allocated to the current resource group that are shared between transactions. The minimum shared memory allocation ratio in the resource group is 0, the maximum is 100, and the default MEMORY_SHARED_QUOTA value is 80.

MEMORY_SPILL_RATIO integer

Memory usage threshold for memory-intensive operators in transactions issued in resource groups. The minimum memory overflow percentage of the resource group is 0. The maximum is 100. The default value of MEMORY_SPILL_RATIO is 20.

Notice

Use CREATE ROLE or ALTER ROLE to assign a specified resource group to a role (user).

Users cannot submit ALTER RESOURCE GROUP commands in explicit transactions or subtransactions.

Example

Change the active transaction limits for resource groups:

ALTER RESOURCE GROUP rgroup1 SET CONCURRENCY 13;

Update CPU limits for resource groups:

ALTER RESOURCE GROUP rgroup2 SET CPU_RATE_LIMIT 45;

Update memory limits for resource groups:

ALTER RESOURCE GROUP rgroup3 SET MEMORY_LIMIT 30;

Improve the memory overflow rate of resource groups from the default values:

ALTER RESOURCE GROUP rgroup4 SET MEMORY_SPILL_RATIO 25;

Reserving CPU cores for resource groups 1:

ALTER RESOURCE GROUP rgroup5 SET CPUSET '1';

compatibility

The ALTER RESOURCE GROUP statement is an extension of the YMatrix database and does not have this command in the PostgreSQL standard.