Managing resource groups
You can manage cluster resource usage quotas using resource groups.
To perform operations using SQL queries, connect to the cluster as a user with the mdb_admin role.
View information about resource groups
If you do not have the Yandex Cloud CLI installed yet, install and initialize it.
By default, the CLI uses the folder specified when creating the profile. To change the default folder, use the yc config set folder-id <folder_ID> command. You can also set a different folder for any specific command using the --folder-name or --folder-id parameter.
To view a list of resource groups and their parameter values, run the command:
yc managed-greenplum resource-groups list --cluster-id=<cluster_ID>
To view the parameter values of a specific resource group, run this command:
yc managed-greenplum resource-groups get <resource_group_name> \
--cluster-id=<cluster_ID>
You can replace --cluster-id with --cluster-name. You can get the cluster ID and name with the list of clusters in the folder.
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 group
If you do not have the Yandex Cloud CLI installed yet, install and initialize it.
By default, the CLI uses the folder specified when creating the profile. To change the default folder, use the yc config set folder-id <folder_ID> command. You can also set a different folder for any specific command using the --folder-name or --folder-id parameter.
Provide the name of the resource group and the values of its parameters in the command:
yc managed-greenplum resource-groups create <resource_group_name> \
--cluster-id=<cluster_ID> \
--concurrency=<CONCURRENCY_parameter_value> \
--cpu-rate-limit=<CPU_RATE_LIMIT_parameter_value> \
--memory-limit=<MEMORY_LIMIT_parameter_value> \
--memory-shared-quota=<MEMORY_SHARED_QUOTA_parameter_value> \
--memory-spill-ratio=<MEMORY_SPILL_RATIO_parameter_value>
When creating a resource group, just specify the value of the CPU_RATE_LIMIT parameter. For the other parameters, the default settings will be applied.
You can replace --cluster-id with --cluster-name. You can get the cluster ID and name with the list of clusters in the folder.
Provide 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 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>;
Note
By default, new roles are assigned to default_group.
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 groups
To view the current load on resource groups across the whole cluster, run this command:
SELECT * FROM gp_toolkit.gp_resgroup_status;
To view the same data by hosts, use this command:
SELECT * FROM gp_toolkit.gp_resgroup_status_per_host;
To view the same data by segments, use this command:
SELECT * FROM gp_toolkit.gp_resgroup_status_per_segment;
View 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 parameters
If you do not have the Yandex Cloud CLI installed yet, install and initialize it.
By default, the CLI uses the folder specified when creating the profile. To change the default folder, use the yc config set folder-id <folder_ID> command. You can also set a different folder for any specific command using the --folder-name or --folder-id parameter.
Provide the name of the resource group and the new values for its parameters you want to change in the command:
yc managed-greenplum resource-groups update <resource_group_name> \
--cluster-id=<cluster_ID> \
--concurrency=<new_CONCURRENCY_parameter_value> \
--cpu-rate-limit=<new_CPU_RATE_LIMIT_parameter_value> \
--memory-limit=<new_MEMORY_LIMIT_parameter_value> \
--memory-shared-quota=<new_MEMORY_SHARED_QUOTA_parameter_value> \
--memory-spill-ratio=<new_MEMORY_SPILL_RATIO_parameter_value>
You can replace --cluster-id with --cluster-name. You can get the cluster ID and name with the list of clusters in the folder.
Provide the name of the resource group and the new values of its parameters you want to change in the command:
ALTER RESOURCE GROUP <resource_group_name> SET <parameter_name> <new_parameter_value>;
If you do not provide new values for certain resource group parameters, they will not change.
Delete 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.
If you do not have the Yandex Cloud CLI installed yet, install and initialize it.
By default, the CLI uses the folder specified when creating the profile. To change the default folder, use the yc config set folder-id <folder_ID> command. You can also set a different folder for any specific command using the --folder-name or --folder-id parameter.
To delete a resource group, run the command:
yc managed-greenplum resource-groups delete <resource_group_name> \
--cluster-id=<cluster_ID>
You can replace --cluster-id with --cluster-name. You can get the cluster ID and name with the list of clusters in the folder.
To delete a resource group, run the command:
DROP RESOURCE GROUP <resource_group_name>;
Example 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%.
If you do not have the Yandex Cloud CLI installed yet, install and initialize it.
By default, the CLI uses the folder specified when creating the profile. To change the default folder, use the yc config set folder-id <folder_ID> command. You can also set a different folder for any specific command using the --folder-name or --folder-id parameter.
Run this command:
yc managed-greenplum resource-groups create testgroup \
--cluster-id=<cluster_ID> \
--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:
yc managed-greenplum resource-groups list --cluster-id=<cluster_ID>
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.