AnalyticDB for PostgreSQL allows you to manage database resources by using the resource queue management method or the resource group management method. The two methods allow you to isolate different types of resources from each other to ensure that the resources are fully and appropriately used for different service requests. This topic describes how to use the resource queue management and resource group management methods to manage AnalyticDB for PostgreSQL database resources, such as CPU, memory, and memory spill ratio.
Resource queue management
The resource queue management method is implemented based on technologies such as lightweight lock. The method allows you to manage AnalyticDB for PostgreSQL database resources, such as the number of concurrent queries, memory usage, and CPU priority. For more information, see Use resource queues for load management.
Resource group management
The resource group management method is implemented based on technologies such as memory sharing and cgroup. The method allows you to manage database resources, such as the number of concurrent queries, memory usage, CPU utilization, and memory spill ratio, in the AnalyticDB for PostgreSQL console. For more information, see Resource group management.
Query the resource management method
You can execute the following statement to query the resource management method of an AnalyticDB for PostgreSQL instance:
SHOW gp_resource_manager;Results:
group: the resource group management method.
queue: the resource queue management method.
Select a resource management method
You can select a resource management method based on your business requirements. The following table describes the differences between the resource queue management method and the resource group management method.
Business item | Resource queue management | Resource group management |
Managed object | CPU, memory, and optimizer | CPU, memory, optimizer, memory spill ratio, and minimum optimizer cost |
Using method | User group-based | User group-based |
Concurrency management | Lock-based, prone to deadlocks | Shared memory-based, no deadlock occurs |
CPU management | Priority-based, fuzzy management | cgroup-based, precise management |
Memory management | Supported | Supported |
Management level | Query | Transaction |
The following section describes different scenarios for which the resource queue management and resource group management methods are suitable:
Resource queue management
You want to manage resources for different business types only based on the CPU priority and allow business retries without the need for granular resource management and deadlock elimination.
Resource group management
You want to prevent business retries caused by accidental deadlocks of resource queues or completely eliminate deadlocks caused by resource queues.
You want to manage resources in the AnalyticDB for PostgreSQL console instead of by executing SQL statements.
You want to perform granular and precise management on resources. For example, you want to specify the maximum CPU utilization or CPU allocation ratio for different business types to implement resource isolation.