The CPU percent metric is the average current CPU percentage for all backend processes executing this query. The percentages for all processes running a query on each segment are averaged, and then the average of all those values is calculated to render this metric.
You can manage the percentage of CPU that queries can consume by creating workloads and specifying the maximum percent of CPU each workload can consume. That percentage is further divided among the segments running on each host and then among the concurrent queries the workload can execute.
CPU allocated to idle workloads is reallocated to active queries and reclaimed when the idle workload becomes active again. This means that the CPU percent value for a query can exceed limits defined for workloads and can increase and decrease as other queries start or finish.
Memory and disk I/O resources are more likely causes for degraded query performance than lack of CPU cycles. The ways to reduce CPU contention mirror the solutions for insufficient memory:
- Reduce concurrency of workloads to make more CPU available to each query.
- Reduce the number of workloads and reallocate CPU to the remaining workloads.
If CPU is not constrained and the size of spill files for some queries is very large, make sure that the gp_workfile_compression server configuration parameter is set to on. Compressing spill files reduces disk I/O, but uses CPU cyles to compress and decompress the data.
See Using Resource Groups for more about managing performance with resource groups.
If your VMware Greenplum system is configured to manage resources with resource queues, see Using Resource Queues.
