One ad hoc query could stall the whole reporting window

Overview

In Greenplum, resource queues and per-statement memory decide who runs and who waits. With one shared queue and default memory settings, a single heavy statement can hold the slots and the segment disks while everything behind it queues.

Environment

Greenplum 6 on vSphere virtual machines, around forty analysts and a dozen scheduled ETL jobs sharing a single default resource queue.

Approach

AceMQ looked at the queue and the running statements while the problem was live, before changing anything. gp_toolkit queue views and per-segment work file usage showed that two ad hoc cross-joins and one large load held the active-statement slots and wrote several gigabytes of spill files per segment. We separated the workloads and bounded what any one statement could take.

Solution

1. 01Captured queue state, running statements and work file usage per segment during the window using gp_toolkit views
2. 02Created separate resource queues for ETL, scheduled reporting and ad hoc analysis, with their own active-statement limits and priorities
3. 03Set statement_mem per role and a work file limit per query, so one statement cannot fill the segment disks
4. 04Reverted the earlier queue limit increases that had allowed more statements to spill at once
5. 05Rewrote the two worst ad hoc patterns with the analysts, replacing cross-joins with joins on the distribution key
6. 06Added alerts for queued statement counts and segment disk use, with a runbook for cancelling a runaway statement

Technologies

GreenplumVMware vSphereLinux