CREATE RESOURCE GROUP
CREATE RESOURCE GROUP
Defines a new resource group.
CREATE RESOURCE GROUP name WITH (group_attribute=value [, ... ])
where group_attribute is:
CPU_RATE_LIMIT=integer MEMORY_LIMIT=integer [ CONCURRENCY=integer ] [ MEMORY_SHARED_QUOTA=integer ] [ MEMORY_SPILL_RATIO=integer ]
Creates a new resource group for Greenplum Database resource management. A resource group is assigned to one or more roles and identifies concurrent transaction, memory, and CPU limits for the role when resource groups are enabled.
You must have SUPERUSER privileges to create a resource group. The maximum number of resource groups allowed in your Greenplum Database cluster is 100.
Greenplum Database pre-defines two default resource groups: admin_group and default_group. These group names, as well as the group name none, are reserved.
To set appropriate limits for resource groups, the Greenplum Database administrator must be familiar with the queries typically executed on the system, as well as the users/roles executing those queries.
- The name of the resource group.
- CONCURRENCY integer
- The maximum number of concurrent transactions, including active and idle transactions, that are permitted for this resource group. 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
- Required. The percentage of CPU resources to allocate to this resource group. The minimum CPU percentage you can specify for a resource group is 1. The maximum is 100. The sum of the CPU_RATE_LIMIT values specified for all resource groups defined in the Greenplum Database cluster must be less than or equal to 100.
- MEMORY_LIMIT integer
- Required. The total percentage of Greenplum Database memory resources to allocate to this resource group. The minimum memory percentage you can specify for a resource group is 1. The maximum is 100. The sum of the MEMORY_LIMIT values specified for all resource groups defined in the Greenplum Database cluster must be less than or equal to 100.
- MEMORY_SHARED_QUOTA integer
- The quota of shared memory in the resource group. Resource groups with a MEMORY_SHARED_QUOTA threshold set aside a percentage of memory allotted to the resource group to share across transactions. This shared memory is allocated on a first-come, first-served basis as available. A transaction may use none, some, or all of this memory. The minimum memory shared quota percentage you can specify 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. When the transaction reaches this threshold, it spills to disk. The minimum memory spill ratio percentage you can specify for a resource group is 0. The maximum is 100. The default MEMORY_SPILL_RATIO value is 20.
You cannot submit a CREATE RESOURCE GROUP command in an explicit transaction or sub-transaction.
Use the gp_toolkit.gp_resgroup_config system view to display the limit settings of all resource groups:
SELECT * FROM gp_toolkit.gp_resgroup_config;
Create a resource group with CPU and memory limit percentages of 35:
CREATE RESOURCE GROUP rgroup1 WITH (CPU_RATE_LIMIT=35, MEMORY_LIMIT=35);
Create a resource group with a concurrent transaction limit of 30, a memory limit of 15, and a CPU limit of 25:
CREATE RESOURCE GROUP rgroup2 WITH (CONCURRENCY=20, MEMORY_LIMIT=15, CPU_RATE_LIMIT=25);
CREATE RESOURCE GROUP is a Greenplum Database extension. There is no provision for resource groups or resource management in the SQL standard.