Manage workloads with resource groups

Limitations

• For an AnalyticDB for PostgreSQL V6.0 instance with minor version v6.6.1.0 or later, or an AnalyticDB for PostgreSQL V7.0 instance with minor version v7.0.6.3 or later, you can switch to resource group management in the console.

• For an AnalyticDB for PostgreSQL V6.0 instance with a minor version earlier than v6.6.1.0, or an AnalyticDB for PostgreSQL V7.0 instance with a minor version earlier than v7.0.6.3, you must submit a ticket to switch to resource group management.

To learn how to view the minor version of an instance, see View the minor version.

Resource groups

AnalyticDB for PostgreSQL supports resource management by using resource groups. Compared to resource queues, resource groups provide more fine-grained control and manage a more comprehensive range of resource types. Resource group management and resource queue management cannot be used at the same time. You can use only one of them. After you enable resource group management, resource queues are automatically disabled. For more information about resource queues, see Use Resource Queues for Workload Management.

Using resource groups, you can associate one or more database accounts with a resource group and configure specific CPU, memory, and concurrency limits for each group. This allows you to manage queries submitted by the associated accounts. However, the SET, RESET, and SHOW commands are not affected by resource group configurations.

V6.0 resource groups

The following table describes the configurable resource parameters for AnalyticDB for PostgreSQL V6.0 resource groups.

V6.0 resource group memory management

The database reserves memory for each resource group based on its MEMORY_LIMIT setting. If the sum of the MEMORY_LIMIT values for all resource groups is less than 100, the remaining unreserved memory is allocated to a global shared memory pool. The database allocates this global shared memory to transactions on a first-come, first-served basis.

Within the reserved memory pool of each resource group, the system uses the MEMORY_SHARED_QUOTA setting to divide the memory into fixed and shared portions. If MEMORY_SHARED_QUOTA is set to 100, all memory within the resource group is treated as shared and is available to all tasks. If MEMORY_SHARED_QUOTA is less than 100, the system divides the memory into a fixed portion and a shared portion. The fixed portion is exclusively for individual tasks, while the shared portion is a common memory space for all tasks. The following figure illustrates the memory management mechanism for AnalyticDB for PostgreSQL V6.0 resource groups.

V6.0 resource group concurrency management

• When the number of queries entering the system reaches the concurrency limit of a resource group, new queries for that group must wait. The concurrency queue follows a first-in, first-out (FIFO) principle.

• When a submitted query is waiting due to the concurrency limit, its status in the pg_stat_activity view is waiting, and the waiting_reason is group.

V6.0 resource group CPU management

AnalyticDB for PostgreSQL resource groups allow you to manage the CPU allocation ratio for each group using a preemptive CPU management approach. The database allocates CPU time slices based on the CPU_RATE_LIMIT value set for each group. If a resource group has a high CPU_RATE_LIMIT value, it receives more CPU time slices during contention, allowing it to execute more tasks. Conversely, a lower value restricts its CPU resource usage. The resource management feature also supports CPU preemption. If one resource group is executing tasks while others are idle, that group can use all available CPU resources.

• If two resource groups are both busy, a group with CPU_RATE_LIMIT set to 40 is allocated twice the CPU resources of a group with CPU_RATE_LIMIT set to 20.

• If a resource group with CPU_RATE_LIMIT set to 40 has no incoming queries, a resource group with CPU_RATE_LIMIT set to 20 can use almost all CPU resources.

V6.0 disk spilling management

AnalyticDB for PostgreSQL resource groups allow you to manage the disk spilling threshold for queries. In AnalyticDB for PostgreSQL, memory-intensive operators can generate large amounts of intermediate results that may need to be temporarily spilled to disk. You can adjust the MEMORY_SPILL_RATIO to increase or decrease this spilling. Therefore, only operations that generate a large number of intermediate results are controlled by this setting. Many operations cannot be spilled to disk and will continue to consume memory, potentially causing memory usage to increase beyond the defined limit.

• If MEMORY_SPILL_RATIO is greater than 0, the operator spilling threshold is determined by this parameter. When the memory usage of a memory-intensive transaction reaches this threshold, its data is spilled to disk.

• If MEMORY_SPILL_RATIO is 0, the operator spilling threshold is determined by the statement_mem parameter. For more information about statement_mem, see Tune performance parameters.

When the cluster load is high and memory resources are limited, you can lower the MEMORY_SPILL_RATIO of the relevant resource group. This causes memory-intensive operators such as hashagg, join, and sort to perform more disk spilling during computation, which reduces memory consumption. When the cluster load is low and memory resources are abundant, you can increase the MEMORY_SPILL_RATIO. This raises the spilling threshold, allowing more computations to be performed in memory and improving query execution efficiency.

V7.0 resource groups

The following table describes the configurable resource parameters for AnalyticDB for PostgreSQL V7.0 resource groups.

V7.0 resource group concurrency management

• When the number of queries entering the system reaches the concurrency limit of a resource group, new queries for that group must wait. The concurrency queue follows a FIFO principle.

• When a submitted query is waiting due to the concurrency limit, its wait_event_type in the pg_stat_activity view is ResourceGroup.

• If a large number of queries are waiting in a group due to the concurrency limit, you should increase the group's concurrency capacity to avoid impacting query performance.

V7.0 resource group memory management

In AnalyticDB for PostgreSQL V7.0, resource groups manage memory usage based on the MEMORY_LIMIT configuration, in conjunction with the statement_mem and gp_resgroup_memory_query_fixed_mem parameters.

• If gp_resgroup_memory_query_fixed_mem is set to a value greater than 0, the maximum memory that a single query can use is limited to that value.

• If gp_resgroup_memory_query_fixed_mem is set to its default value of 0, the resource group determines the memory for a single query based on statement_mem and MEMORY_LIMIT. Details are as follows:

  • If the MEMORY_LIMIT of the resource group is set to -1, the memory limit for a single query managed by the group is determined by statement_mem.

  • If the MEMORY_LIMIT of the resource group is set to a value greater than -1, the memory limit for a single query is max(MEMORY_LIMIT/CONCURRENCY, statement_mem). For example, a database has a resource group named etl with MEMORY_LIMIT set to 2.0 GB and a concurrency value of 5. By default, a single query is expected to use 400 MB of memory. Consider the following scenarios:

• User etl1 submits query Q1 and sets the session-level gp_resgroup_memory_query_fixed_mem to 800 MB (set gp_resgroup_memory_query_fixed_mem='800MB') and statement_mem to 900 MB. Q1 can use a maximum of 800 MB of memory.

• User etl2 submits query Q2 without configuring the session-level gp_resgroup_memory_query_fixed_mem parameter, and sets statement_mem to 300 MB. Since 300 MB is less than 400 MB, Q2 is submitted to the system and uses 400 MB of memory.

• User etl3 submits query Q3 without configuring the session-level gp_resgroup_memory_query_fixed_mem parameter, and sets statement_mem to 700 MB. Since 700 MB is greater than 400 MB, Q3 is submitted to the system and uses 700 MB of memory.

V7.0 resource group CPU management

In AnalyticDB for PostgreSQL V7.0, resource groups use CPU_MAX_PERCENT to manage the maximum CPU utilization for each group and CPU_WEIGHT to manage the allocation ratio among groups. The system allocates CPU time slices according to the CPU_WEIGHT ratio of each group, while ensuring that the CPU utilization of each group does not exceed its CPU_MAX_PERCENT value.

For example, a database has two groups, rg1 and rg2. For rg1, CPU_MAX_PERCENT is 30 and CPU_WEIGHT is 100. For rg2, CPU_MAX_PERCENT is 60 and CPU_WEIGHT is 50.

1. If only rg1 has active queries and rg2 is idle, the CPU utilization of rg1 can reach 30%, but the group will not use more even if idle CPU is available. In this scenario, rg2 has no active queries, so its CPU utilization is 0%.

2. If both rg1 and rg2 have active queries, the database allocates CPU time slices based on the 100:50 weight ratio. However, once a resource group reaches its CPU_MAX_PERCENT limit, it no longer receives additional CPU resources. For example, after rg1 reaches 30% CPU utilization, subsequent time slices are allocated to rg2 (as long as it is below its 60% limit), which ensures that rg1 does not exceed its 30% cap.

Enable resource group management

Enable or disable resource group management in the console

For AnalyticDB for PostgreSQL V6.0 instances with minor version v6.6.1.0 or later, you can enable or disable resource group management in the console. Switching the resource management mode from resource queues to resource groups restarts the instance, which will be unavailable for about 5 minutes. Plan this operation during off-peak hours to avoid disrupting your business. Follow these steps:

1. Log on to the AnalyticDB for PostgreSQL console.
2. In the upper-left corner of the console, select a region.
3. Find the instance that you want to manage and click the instance ID.

4. In the left-side navigation pane, click Workload Management.

5. Click Enable Resource Group Management, and in the confirmation dialog box, click OK.

Enable or disable by submitting a ticket

For AnalyticDB for PostgreSQL V6.0 instances with a minor version earlier than v6.6.1.0 or for any AnalyticDB for PostgreSQL V7.0 instance, you must submit a ticket to enable or disable resource group management.

After you enable resource group management, the system automatically creates a resource group named default_group. On the Resource Group Monitoring tab of the Monitoring and Alerts > Instance Monitoring page, you can view monitoring information for all resource groups, including CPU and memory usage for each segment.

Create a resource group

After you enable resource group management, you can create a new resource group by using the console or by running an SQL statement.

CREATE RESOURCE GROUP <group_name> WITH (group_attribute=value [, ... ])

CPU_RATE_LIMIT=integer
MEMORY_LIMIT=integer
[ CONCURRENCY=integer ]
[ MEMORY_SHARED_QUOTA=integer ]
[ MEMORY_SPILL_RATIO=integer ]

CPU_MAX_PERCENT=integer
[CPU_WEIGHT=integer ]
[ CONCURRENCY=integer ]
[ MEMORY_LIMIT=integer ]
[ MIN_COST=integer ]

View resource group configurations

After you create a resource group, you can view its configuration by using the console or by running an SQL statement.

SELECT * FROM gp_toolkit.gp_resgroup_config;

Modify resource group configurations

You can modify resource group configurations by using the console or by running an SQL statement.

ALTER RESOURCE GROUP <group_name> 
SET CONCURRENCY|CPU_RATE_LIMIT|MEMORY_LIMIT|MEMORY_SHARED_QUOTA|MEMORY_SPILL_RATIO <value>;

ALTER RESOURCE GROUP <group_name> 
SET CPU_MAX_PERCENT|CPU_WEIGHT|CONCURRENCY|MEMORY_LIMIT|MIN_COST <value>;

Assign a database account to a resource group

You can assign a database account to a resource group by using the console or by running an SQL statement. For information about how to create a database account, see Create and manage users.

• Each resource group can be associated with one or more database accounts, but each account can be assigned to only one resource group.

• By default, the privileged account is assigned to the default_group resource group.

• A database account that is not assigned to any resource group is automatically assigned to the default_group resource group.

Delete a resource group

You can delete a resource group by using the console or by running an SQL statement.

DROP RESOURCE GROUP <group_name>;

Manually query resource group monitoring information

V6.0

SELECT * FROM gp_toolkit.gp_resgroup_status;

The following table describes the returned fields.

V7.0

Query queue time and queue count

SELECT * FROM gp_toolkit.gp_resgroup_status;

The following table describes the returned fields.

Query CPU and memory usage

SELECT * FROM gp_toolkit.gp_resgroup_status_per_host;

The following table describes the returned fields.

Disable resource group management

If you need to switch the resource management mode back to resource queues, you can disable resource group management in the console. The process involves restarting the instance, which will be unavailable for about 5 minutes. Plan this operation during off-peak hours to avoid disrupting your business. Follow these steps:

1. Log on to the AnalyticDB for PostgreSQL console.

2. In the upper-left corner of the console, select the region where the instance resides.

3. Find the target instance and click its instance ID.

4. In the left-side navigation pane, click Workload Management, and then click Disable Resource Group Management.