how to manage database resources-AnalyticDB(AnalyticDB)-阿里云帮助中心

AnalyticDB for PostgreSQL supports resource queues and resource groups to manage database resources such as CPU, memory, and disk spilling. These methods provide resource isolation for different types of queries, ensuring efficient resource utilization and service independence for an AnalyticDB for PostgreSQL database.

Resource queue management

Resource queues use lightweight locks to manage concurrent queries, query memory, and CPU priority for AnalyticDB for PostgreSQL databases. For more information, see Use resource queues to manage workloads.

Resource group management

Resource groups use shared memory and cgroups to manage concurrent queries, query memory, CPU utilization, and disk spill thresholds for AnalyticDB for PostgreSQL databases. You can manage resource groups from the console. For more information, see Use resource groups to manage workloads.

Query the resource management method

Run the following SQL statement to check the resource management method of the current AnalyticDB for PostgreSQL instance:

SHOW gp_resource_manager;

Results:

group: The resource group method is used.
queue: The resource queue method is used.

Choose a resource management method

Choose a resource management method based on your business needs. The following table compares resource queues and resource groups.

Service	Resource Queue	Resource Group
Control items	CPU, memory, and optimizer	CPU, memory, and optimizer Disk persistence, minimum optimizer cost
Method of use	Based on user groups	Based on user groups
Concurrency management	Based on locks. Prone to deadlocks.	Based on shared memory. Does not cause deadlocks.
CPU management	Priority-based. Imprecise management.	Cgroup-based. Precise management.
Memory management	Support	Support
Control level	Query level	Transaction level

Use the following guidelines to choose a resource management method:

Use resource queues.

Choose this method if you do not require fine-grained resource management, prefer simple resource configuration based on CPU priority, and can handle potential deadlocks by retrying operations.
Use resource groups.
- Eliminate deadlocks and avoid the need to retry operations.
- Manage resource configurations in the console instead of using SQL statements.
- Perform fine-grained resource management. For example, you can limit the maximum CPU usage or allocate CPU time slice ratios for different services to achieve effective resource isolation.