Managing resource groups

You can manage cluster resource usage quotas using resource groups. To do this, connect to the cluster as the user with the mdb_admin role.

View information about resource groupsView information about resource groups

To view a list of resource groups and their parameter values, run the command:

SELECT * FROM gp_toolkit.gp_resgroup_config;

To view a list of roles and the resource groups they belong to, run the command:

SELECT rolname, rsgname FROM pg_roles, pg_resgroup
WHERE pg_roles.rolresgroup=pg_resgroup.oid;

Create a resource groupCreate a resource group

Pass the name of the resource group and its parameters in the command:

CREATE RESOURCE GROUP <resource_group_name> WITH (<parameter_1>, <parameter_2>, ...);

When creating a resource group, just specify the value of the CPU_RATE_LIMIT or CPUSET parameter. For the other parameters, the default settings will be applied.

Assign a resource group to a roleAssign a resource group to a role

To create a role and immediately assign it to a resource group, run the command:

CREATE ROLE <role_name> RESOURCE GROUP <resource_group_name>;

To assign a different resource group to the role, run the command:

ALTER ROLE <role_name> RESOURCE GROUP <resource_group_name>;

View the current load of resource groupsView the current load of resource groups

SELECT * FROM gp_toolkit.gp_resgroup_status;

SELECT * FROM gp_toolkit.gp_resgroup_status_per_host;

SELECT * FROM gp_toolkit.gp_resgroup_status_per_segment;

View a list of queries in resource groupsView a list of queries in resource groups

To see a list of queries in progress and in the queue, run the command:

SELECT query, waiting, rsgname, rsgqueueduration
FROM pg_stat_activity;

Change resource group parametersChange resource group parameters

Run this command:

ALTER RESOURCE GROUP <resource_group_name> SET <parameter_name> <new_parameter_value>;

Delete a resource groupDelete a resource group

You can delete a resource group only if it isn't assigned to any roles and contains no transactions in progress or in the queue.

To delete a resource group, run the command:

DROP RESOURCE GROUP <resource_group_name>;

Example of creating a resource groupExample of creating a resource group

Create a resource group with test characteristics:

• Name: testgroup.
• Maximum number of processed transactions: 15.
• Share of CPU resources used in the segment: 10%.
• RAM share in the segment: 20%.
• Share allocated as an overall quota: 50%.
• RAM share in the resource group that a single transaction operator can occupy: 30%.

Run this command:

CREATE RESOURCE GROUP testgroup
WITH (CONCURRENCY=15, CPU_RATE_LIMIT=10, MEMORY_LIMIT=20, MEMORY_SHARED_QUOTA=50, MEMORY_SPILL_RATIO=30);

Check that the new resource group appeared in the list:

SELECT * FROM gp_toolkit.gp_resgroup_config;

Greenplum® and Greenplum Database® are registered trademarks or trademarks of Broadcom Inc. in the United States and/or other countries.