Defines a new resource group.
CREATE RESOURCE GROUP <name> WITH (<group_attribute>=<value> [, ... ])
where group_attribute is:
CPU_RATE_LIMIT=<integer> | CPUSET=<master_cores>;<segment_cores>
[ MEMORY_LIMIT=<integer> ]
[ CONCURRENCY=<integer> ]
[ MEMORY_SHARED_QUOTA=<integer> ]
[ MEMORY_SPILL_RATIO=<integer> ]
[ MEMORY_AUDITOR= {vmtracker | cgroup} ]
Creates a new resource group for Greenplum Database resource management. You can create resource groups to manage resources for roles or to manage the resources of a Greenplum Database external component such as PL/Container.
A resource group that you create to manage a user role identifies concurrent transaction, memory, and CPU limits for the role when resource groups are enabled. You may assign such resource groups to one or more roles.
A resource group that you create to manage the resources of a Greenplum Database external component such as PL/Container identifies the memory and CPU limits for the component when resource groups are enabled. These resource groups use cgroups for both CPU and memory management. Assignment of resource groups to external components is component-specific. For example, you assign a PL/Container resource group when you configure a PL/Container runtime. You cannot assign a resource group that you create for external components to a role, nor can you assign a resource group that you create for roles to an external component.
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 run on the system, as well as the users/roles running those queries and the external components they may be using, such as PL/Containers.
After creating a resource group for a role, assign the group to one or more roles using the ALTER ROLE or CREATE ROLE commands.
After you create a resource group to manage the CPU and memory resources of an external component, configure the external component to use the resource group. For example, configure the PL/Container runtime resource_group_id
.
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 for resource groups defined for roles is 20.
You must set CONCURRENCY
to zero (0) for resource groups that you create for external components.
NoteYou cannot set the
CONCURRENCY
value for theadmin_group
to zero (0).
Required. You must specify only one of CPU_RATE_LIMIT
or CPUSET
when you create a resource group.
CPU_RATE_LIMIT
is 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.
CPUSET
identifies the CPU cores to reserve for this resource group on the master host and on segment hosts. The CPU cores that you specify must be available in the system and cannot overlap with any CPU cores that you specify for other resource groups.
Specify cores as a comma-separated list of single core numbers or core number intervals. Define the master host cores first, followed by segment host cores, and separate the two with a semicolon. You must enclose the full core configuration in single quotes. For example, '1;1,3-4' configures core 1 for the master host, and cores 1, 3, and 4 for the segment hosts.
NoteYou can configure
CPUSET
for a resource group only after you have enabled resource group-based resource management for your Greenplum Database cluster.
The total percentage of Greenplum Database memory resources to reserve for this resource group. The minimum memory percentage you can specify for a resource group is 0. The maximum is 100. The default value is 0.
When you specify a MEMORY_LIMIT
of 0, Greenplum Database reserves no memory for the resource group, but uses global shared memory to fulfill all memory requests in the group. If MEMORY_LIMIT
is 0, MEMORY_SPILL_RATIO
must also be 0.
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
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 80.
MEMORY_SPILL_RATIO
value is 0. When
MEMORY_SPILL_RATIO
is 0, Greenplum Database uses the
statement_mem
server configuration parameter value to control initial query operator memory.
The memory auditor for the resource group. Greenplum Database employs virtual memory tracking for role resources and cgroup memory tracking for resources used by external components. The default MEMORY_AUDITOR
is vmtracker
. When you create a resource group with vmtracker
memory auditing, Greenplum Database tracks that resource group's memory internally.
cgroup
MEMORY_AUDITOR
, Greenplum Database defers the accounting of memory used by that resource group to cgroups.
CONCURRENCY
must be zero (0) for a resource group that you create for external components such as PL/Container. You cannot assign a resource group that you create for external components to a Greenplum Database role.
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 20, 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 a resource group to manage PL/Container resources specifying a memory limit of 10, and a CPU limit of 10:
CREATE RESOURCE GROUP plc_run1 WITH (MEMORY_LIMIT=10, CPU_RATE_LIMIT=10,
CONCURRENCY=0, MEMORY_AUDITOR=cgroup);
Create a resource group with a memory limit percentage of 11 to which you assign CPU core 1 on the master host, and cores 1 to 3 on segment hosts:
CREATE RESOURCE GROUP rgroup3 WITH (CPUSET='1;1-3', MEMORY_LIMIT=11);
CREATE RESOURCE GROUP
is a Greenplum Database extension. There is no provision for resource groups or resource management in the SQL standard.
ALTER ROLE, CREATE ROLE, ALTER RESOURCE GROUP, DROP RESOURCE GROUP
Parent topic: SQL Commands