ALTER RESOURCE GROUP

A newer version of this documentation is available. Use the version menu above to view the most up-to-date release of the Greenplum 5.x documentation.

ALTER RESOURCE GROUP

Changes the limits of a resource group.

Synopsis

ALTER RESOURCE GROUP name SET group_attribute value

where group_attribute is one of:

CONCURRENCY integer
CPU_RATE_LIMIT integer
MEMORY_LIMIT integer
MEMORY_SHARED_QUOTA integer
MEMORY_SPILL_RATIO integer

Description

ALTER RESOURCE GROUP changes the limits of a resource group. Only a superuser can alter a resource group.

You can set or reset the concurrency limit of a resource group to control the maximum number of active concurrent statements in that group. You can also reset the memory or CPU rate limit of a resource group to control the amount of memory or CPU resources that all queries submitted through the group can consume on each segment host.

The new resource limit is immediately applied if current resource usage is less than or equal to the new value and there are no running transactions in the resource group. If current resource usage exceeds the new limit value, or there are running transactions in other resource groups holding some of the resource, Greenplum Database will defer the new limit assignment until resource usage is within the range of the new value.

You can alter one limit type in a single ALTER RESOURCE GROUP call.

Parameters

name
The name of the resource group to alter.
CONCURRENCY integer
The maximum number of concurrent transactions, including active and idle transactions, that are permitted for this resource group. Any transactions submitted after the CONCURRENCY value limit is reached are queued. When a running transaction completes, the earliest queued transaction is executed.
The CONCURRENCY value must be an integer in the range [0 .. max_connections]. The default CONCURRENCY value is 20.
Note: You cannot set the CONCURRENCY value for the admin_group to zero (0).
CPU_RATE_LIMIT integer
The percentage of CPU resources to allocate to this resource group. The minimum CPU percentage for a resource group is 1. The maximum is 100. The sum of the CPU_RATE_LIMITs of all resource groups defined in the Greenplum Database cluster must not exceed 100.
MEMORY_LIMIT integer
The percentage of memory resources to allocate to this resource group. The minimum memory percentage for a resource group is 1. The maximum is 100. The sum of the MEMORY_LIMITs of all resource groups defined in the Greenplum Database cluster must not exceed 100.
MEMORY_SHARED_QUOTA integer
The percentage of memory resources to share among transactions in the resource group. The minimum memory shared quota percentage for a resource group is 0. The maximum is 100. The default MEMORY_SHARED_QUOTA value is 20.
MEMORY_SPILL_RATIO integer
The memory usage threshold for memory-intensive operators in a transaction issued in the resource group. The minimum memory spill ratio percentage for a resource group is 0. The maximum is 100. The default MEMORY_SPILL_RATIO value is 20.

Notes

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

You cannot submit an ALTER RESOURCE GROUP command in an explicit transaction or sub-transaction.

Examples

Change the active transaction limit for a resource group:

ALTER RESOURCE GROUP rgroup1 SET CONCURRENCY 13;

Update the CPU limit for a resource group:

ALTER RESOURCE GROUP rgroup2 SET CPU_RATE_LIMIT 45;

Update the memory limit for a resource group:

ALTER RESOURCE GROUP rgroup3 SET MEMORY_LIMIT 30;

Increase the memory spill ratio for a resource group from the default:

ALTER RESOURCE GROUP rgroup4 SET MEMORY_SPILL_RATIO 25;

Compatibility

The ALTER RESOURCE GROUP statement is a Greenplum Database extension. This command does not exist in standard PostgreSQL.