Managing resource groups
You can manage cluster resource usage quotas via 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 yet, install and initialize it.
The folder used by default is the one specified when creating the CLI 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 options.
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 this 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 yet, install and initialize it.
The folder used by default is the one specified when creating the CLI 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 options.
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. The other parameters will be set to defaults.
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. The other parameters will be set to defaults.
Assign a resource group to a role
To create a role and immediately assign it to a resource group, run this 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 this command:
ALTER ROLE <role_name> RESOURCE GROUP <resource_group_name>;
View the current load on 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 this command:
SELECT query, waiting, rsgname, rsgqueueduration
FROM pg_stat_activity;
Edit resource group settings
If you do not have the Yandex Cloud CLI yet, install and initialize it.
The folder used by default is the one specified when creating the CLI 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 options.
Provide the name of the resource group and the new values for its settings you want to edit in this command:
yc managed-greenplum resource-groups update <resource_group_name> \
--cluster-id=<cluster_ID> \
--concurrency=<new_CONCURRENCY_value> \
--cpu-rate-limit=<new_CPU_RATE_LIMIT_value> \
--memory-limit=<new_MEMORY_LIMIT_value> \
--memory-shared-quota=<new_MEMORY_SHARED_QUOTA_value> \
--memory-spill-ratio=<new_MEMORY_SPILL_RATIO_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 <setting_name> <new_setting_value>;
If you do not provide new values for certain resource group settings, they will not change.
Delete a resource group
You can only delete a resource group if it is not assigned to any roles and contains no transactions in progress or in the queue.
If you do not have the Yandex Cloud CLI yet, install and initialize it.
The folder used by default is the one specified when creating the CLI 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 options.
To delete a resource group, run this 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 this command:
DROP RESOURCE GROUP <resource_group_name>;
Example of creating a resource group
Create a resource group with test specifications:
- 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 yet, install and initialize it.
The folder used by default is the one specified when creating the CLI 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 options.
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
Make sure 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);
Make sure 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.