AnalyticDB:Use resource groups to perform workload management

Limits

• For AnalyticDB for PostgreSQL V6.0 instances of V6.6.1.0 or later or AnalyticDB for PostgreSQL V7.0 instances of V7.0.6.3 or later, you can switch the resource management method from resource queue management to resource group management in the AnalyticDB for PostgreSQL console.

• For AnalyticDB for PostgreSQL V6.0 instances earlier than V6.6.1.0 or AnalyticDB for PostgreSQL V7.0 instances earlier than V7.0.6.3, you must submit a ticket to switch the resource management method from resource queue management to resource group management.

For information about how to view the minor version of an AnalyticDB for PostgreSQL instance, see View the minor version of an instance.

Introduction to resource groups

AnalyticDB for PostgreSQL allows you to use resource groups to manage database resources. Compared with resource queue management, resource group management supports more resource categories and manages resources in a finer-grained manner. Resource group management and resource queue management cannot be used at the same time. After you enable the resource group management feature, the resource queue management feature is automatically disabled. For more information about the resource queue management feature, see Use resource queues to perform workload management.

When you use resource groups to manage resources, you can associate a resource group with one or more database accounts and configure settings, such as CPU utilization, memory percentage, and concurrency, for each resource group. This way, you can manage the queries that are submitted by the database accounts associated with each resource group. The SET, RESET, and SHOW statements are not affected by the resource group settings.

Resource groups of AnalyticDB for PostgreSQL V6.0

The following table describes the parameters of the resource categories supported by AnalyticDB for PostgreSQL V6.0.

Memory management of V6.0 resource groups

The system reserves memory resources for resource groups based on the memory_limit parameter that you specify for each resource group. If the sum of the values of the memory_limit parameter of all resource groups is less than 100, the system stores the remaining memory that is not reserved for resource groups to a memory pool for global sharing. If necessary, the system allocates the global shared memory to transactions in a first-in first-out (FIFO) manner.

Within the memory pool of each resource group, the system configures the fixed memory quota and the shared memory quota based on the MEMORY_SHARED_QUOTA parameter that you specify. If you set the MEMORY_SHARED_QUOTA parameter to 100 for a resource group, all memory in the resource group is considered as shared memory and can be used for all jobs. If you set the MEMORY_SHARED_QUOTA parameter to a value that is less than 100 for a resource group, the system divides the resource group memory into the fixed memory and the shared memory based on the parameter. The fixed memory is assigned to each job, and the shared memory can be shared by all jobs.

Concurrency management of V6.0 resource groups

• When the number of queries that enter a resource group reaches the value of the CONCURRENCY parameter, new queries must wait in queues. Queued queries are managed in a FIFO manner.

• If a query must wait because the value of the CONCURRENCY parameter is reached, the query is in the waiting state and the value of the waiting_reason field is displayed as group when you query the pg_stat_activity view.

CPU management of V6.0 resource groups

AnalyticDB for PostgreSQL allows you to manage the CPU allocation rate for resource groups. The system allocates CPU time slices based on the CPU_RATE_LIMIT parameter that you specify for each resource group. If you set the CPU_RATE_LIMIT parameter to a higher value for a resource group, the resource group can obtain more CPU time slices to execute jobs. If you set the CPU_RATE_LIMIT parameter to a lower value for a resource group, the resource group can use less CPU resources. The resource group management feature also supports CPU preemption. If only one of the resource groups is running jobs, the resource group can use all CPU resources.

• If two resource groups are both busy, the resource group for which you set the CPU_RATE_LIMIT parameter to 40 can obtain twice the CPU resources of the resource group for which you set the CPU_RATE_LIMIT parameter to 20.

• If no queries are performed by the resource group for which you set the CPU_RATE_LIMIT parameter to 40, the resource group for which you set the CPU_RATE_LIMIT parameter to 20 can use nearly all CPU resources.

Disk spilling management of V6.0 resource groups

AnalyticDB for PostgreSQL allows you to manage the memory spill ratio for resource groups. When AnalyticDB for PostgreSQL executes memory-intensive operators, a large number of intermediate temporary results may be generated and spilled to disks. You can modify the MEMORY_SPILL_RATIO parameter to increase or decrease the spill amount. The MEMORY_SPILL_RATIO parameter applies only to operation types that generate a large number of intermediate temporary results. The operations that do not spill execution results to disks continue to occupy memory even when the memory usage exceeds the upper limit.

• If the value of the MEMORY_SPILL_RATIO parameter is greater than 0, the memory spill ratio is determined by the MEMORY_SPILL_RATIO parameter. When the memory that is used for memory-sensitive transactions reaches this value, data is spilled to disks.

• If the value of the MEMORY_SPILL_RATIO parameter is 0, the memory spill ratio is determined by the statement_mem parameter. For more information about the statement_mem parameter, see Performance parameter optimization.

If memory is insufficient to process heavy workloads, decrease the value of the MEMORY_SPILL_RATIO parameter for the involved resource group to spill the execution results of memory-intensive operators such as HashAgg, Join, and Sort to disks and reduce memory usage. If memory is sufficient to process small workloads, increase the value of the MEMORY_SPILL_RATIO parameter for the involved resource group to allow more operators to be executed in the memory and improve query efficiency.

Resource groups of AnalyticDB for PostgreSQL V7.0

The following table describes the parameters of the resource categories supported by AnalyticDB for PostgreSQL V7.0.

Concurrency management of V7.0 resource groups

• When the number of queries that enter a resource group reaches the value of the CONCURRENCY parameter, new queries must wait in queues. Queued queries are managed in a FIFO manner.

• If a query must wait because the value of the CONCURRENCY parameter is reached, the value of the wait_event_type field is displayed as ResourceGroup when you query the pg_stat_activity view.

• If a large number of queries are waiting in a resource group because the value of the CONCURRENCY parameter is reached, you must appropriately increase the concurrency capability of the resource group to ensure query performance.

Memory management of V7.0 resource groups

AnalyticDB for PostgreSQL V7.0 uses the MEMORY_LIMIT, statement_mem, and gp_resgroup_memory_query_fixed_mem parameters to manage the memory usage of resource groups.

• If the gp_resgroup_memory_query_fixed_mem parameter is set to a value greater than 0, the maximum memory that can be used by a query is the value of the parameter.

• If the gp_resgroup_memory_query_fixed_mem parameter is set to the default value 0, resource groups use the statement_mem and MEMORY_LIMIT parameters to determine the maximum memory that can be used by a query.

  • If the MEMORY_LIMIT parameter of the resource group is set to -1, the maximum memory that can be used by a query within the resource group is the value of the statement_mem parameter.

  • If the MEMORY_LIMIT parameter of the resource group is set to a value greater than -1, the maximum memory that can be used by a query within the resource group is the result of the MAX(MEMORY_LIMIT/CONCURRENCY, statement_mem) function. For example, the MEMORY_LIMIT parameter of the etl resource group is set to 2.0 GB and the CONCURRENCY parameter of the resource group is set to 5. By default, a query can use 400 MB of memory. Take note of the following scenarios:

• If the etl1 user submits a query and sets the session-level gp_resgroup_memory_query_fixed_mem parameter to 800 MB and the statement_mem parameter to 900 MB, the query can use up to 800 MB of memory.

• If the etl2 user submits a query, leaves the session-level gp_resgroup_memory_query_fixed_mem parameter empty, and sets the statement_mem parameter to 300 MB, the query is submitted to the system and occupies 400 MB of memory because 300 MB is less than the default memory of a query (400 MB).

• If the etl3 user submits a query, leaves the session-level gp_resgroup_memory_query_fixed_mem parameter empty, and sets the statement_mem parameter to 700 MB, the query is submitted to the system and occupies 700 MB of memory because 700 MB is greater than the default memory of a query (400 MB).

CPU management of V7.0 resource groups

AnalyticDB for PostgreSQL V7.0 uses the CPU_MAX_PERCENT parameter to manage the maximum CPU utilization of each resource group, and uses the CPU_WEIGHT parameter to manage the CPU allocation rate of each resource group. The system allocates CPU time slices based on the CPU_WEIGHT parameter of each resource group and ensures that the CPU utilization of each resource group does not exceed the value of the CPU_MAX_PERCENT parameter.

For example, the CPU_MAX_PERCENT parameter of the rg1 resource group is set to 30 and the CPU_WEIGHT parameter of the rg1 resource group is set to 100. The CPU_MAX_PERCENT parameter of the rg2 resource group is set to 60 and the CPU_WEIGHT parameter of the rg2 resource group is set to 50.

1. If only the rg1 resource group has active queries, the CPU utilization of the rg1 resource group can reach 30%, and the CPU utilization of the rg2 resource group can reach 60%.

2. If the rg1 and rg2 resource groups have active queries, the system allocates CPU time slices based on 100:50 until the CPU utilization of a resource group reaches the value of the CPU_MAX_PERCENT parameter of the resource group. For example, when the CPU utilization of the rg1 resource group reaches 30%, the system preferentially allocates CPU time slices to the rg2 resource group to ensure that the CPU utilization of the rg1 resource group does not exceed 30%.

Enable the resource group management feature

Enable or disable the resource group management feature in the AnalyticDB for PostgreSQL console

For AnalyticDB for PostgreSQL V6.0 instances of V6.6.1.0 or later, you can enable or disable the resource group management feature in the AnalyticDB for PostgreSQL console. This operation switches the resource management method from resource queue management to resource group management. During the switchover, the AnalyticDB for PostgreSQL instance restarts and becomes unavailable for approximately 5 minutes. To prevent your business from being affected, enable or disable the resource group management feature during off-peak hours. Perform the following 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. In the message that appears, click OK.

Enable or disable the resource group management feature by submitting a ticket

For AnalyticDB for PostgreSQL V6.0 instances earlier than V6.6.1.0 and AnalyticDB PostgreSQL V7.0 instances, you must submit a ticket to enable or disable the resource group management feature.

After you enable the resource group management feature, the system automatically creates a resource group named default_group. You can choose Monitoring and Alerts > Instance Monitoring in the left-side navigation pane and click the Resource Group Monitoring tab to view the monitoring information of all resource groups and the CPU and memory metrics of compute nodes.

Create a resource group

After you enable the resource group management feature, you can create resource groups in the AnalyticDB for PostgreSQL console or by using SQL syntax.

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 the resource group settings

After you create a resource group, you can view the resource group settings in the AnalyticDB for PostgreSQL console or by using SQL syntax.

SELECT * FROM gp_toolkit.gp_resgroup_config;

Modify the resource group settings

You can modify the resource group settings in the AnalyticDB for PostgreSQL console or by using SQL syntax.

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>;

Associate a resource group with one or more database accounts

You can associate a resource group with one or more database accounts in the AnalyticDB for PostgreSQL console or by using SQL syntax. For information about how to create a database account, see Create a database account.

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

• By default, privileged accounts are associated with the default_group resource group.

• If a database account is not associated with a resource group, the database account is associated with the default_group resource group.

Delete a resource group

You can delete a resource group in the AnalyticDB for PostgreSQL console or by using SQL syntax.

DROP RESOURCE GROUP <group_name>;

Query the monitoring information of a resource group

V6.0

SELECT * FROM gp_toolkit.gp_resgroup_status;

The following table describes the metrics.

V7.0

Query the queue duration and the number of queued transactions

SELECT * FROM gp_toolkit.gp_resgroup_status;

The following table describes the metrics.

Query the CPU utilization and the memory used

SELECT * FROM gp_toolkit.gp_resgroup_status_per_host;

The following table describes the metrics.

Disable the resource group management feature

If you want to switch the resource management method to resource queue management, you can disable the resource group management feature in the AnalyticDB for PostgreSQL console. During the disabling process, the AnalyticDB for PostgreSQL instance restarts and becomes unavailable for approximately 5 minutes. To prevent your business from being affected, disable the resource group management feature during off-peak hours. Perform the following 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. On the page that appears, click Disable Resource Group Management.