Managing system resources

Govern cluster performance by controlling how system resources are distributed across different workloads using the Resource Management panel on the left sidebar. By implementing a resource management strategy, you prevent a single database tenant or poorly optimized query from exhausting cluster capacity and impacting other users.

WarehousePG (WHPG) provides two primary strategies for resource management: resource groups and resource queues. They're controlled by the gp_resource_manager server configuration parameter. See Managing resources for details on how WHPG provides resource management.

The WarehousePG Enterprise Manager (WEM) interface adapts based on two factors:

1. The value of gp_resource_manager:

   • group: Displays the Resource Groups interface.
   • queue: Displays the Resource Queues interface.
   • none (WHPG 7+): Disables resource management.

2. Your WHPG major version: The configuration options differ between WHPG 6 and WHPG 7 to reflect changes in the underlying engine's capabilities.

Governing workloads with resource groups

To ensure high-priority processes remain isolated from ad-hoc queries, configure limits that match your cluster version and hardware capacity. WEM adjusts the Configuration tab to match the capabilities of your WHPG version.

• Map database roles to groups with the Role Assignments tab. The Role > Resource Group Assignments table shows each role, its superuser flag, and the group it belongs to.
• View and edit CPU and memory limits in the Configuration tab. The Resource Group Configuration table shows all groups with their settings. Charts show the CPU Max % Distribution (WHPG 7) or CPU Rate Limit Distribution (WHPG 6), and a Concurrency by Group bar chart:
  • For WHPG 7, use CPU Max % to set a hard ceiling that the group can't exceed. Adjust CPU Weight to determine how much relative processing power a group receives during periods of high contention.
  • For WHPG 6, use CPU Rate % and Memory % to define the fixed share of resources dedicated to a group.
• Limit simultaneous activity to prevent too many queries from running at once by adjusting the value of Concurrency. If this limit is too high, individual queries could fail due to out-of-memory (OOM) errors.
• Optimize memory-intensive tasks on a WHPG 6 cluster by adjusting the Spill Ratio to control when operations like large joins or sorts begin writing data to disk instead of consuming RAM.

Example scenario: resolving CPU contention

Suppose you notice that ad-hoc analytics queries are slowing down nightly production backups:

• Check the CPU Max % Distribution or CPU Rate Limit Distribution chart. If you see the analytics group consuming 90% of the CPU, you must intervene.
• In the Configuration tab, lower the CPU Max % (WHPG 7) or CPU Rate % (WHPG 6) for the analytics group. This action immediately frees up processing cycles for the high-priority backup tasks.

Governing workloads with resource queues

Resource queues are available on both WHPG 6 and 7, but they use a different management logic centered on query cost rather than OS-level cgroups.

• Review queue settings in the Queue Configuration tab. The Resource Queue Configuration table shows each queue's Queue Name, Priority, Active Statements, Max Cost, Memory Limit, Overcommit, and Ignore Cost Limit settings.
• Monitor real-time queue activity in the Queue Activity tab. The Total Queues, Active Queries, Waiting Queries, and Total Cost cards provide a cluster-wide snapshot. The Queue Activity table shows current and waiting query counts, cost, and utilization per queue.
• Use the Ignore Cost Limit setting to allow low-cost administrative queries to bypass the queue entirely, ensuring routine maintenance tasks always run without delay.

Troubleshooting waiting queries

Assess and resolve bottlenecks caused by resource-based delays by comparing real-time consumption against your configured boundaries:

• Check the Status tab (resource groups) or Queue Activity tab (resource queues) to identify which groups or queues have queries in a waiting state.
• If many queries are queued, determine if the group or queue is consistently hitting its maximum limit.
• If a workload consistently requires more power than assigned, navigate to the Configuration or Queue Configuration tab and increase the relevant limits.
• If a group is reaching its memory limit with only a few queries, investigate the queries themselves for missing indexes or inefficient joins that are causing excessive resource consumption.