Query failing with "ERROR: Canceling query because of high VMEM usage"

Question

We have small array of gpdb cluster. in that, few queries are failing

System Related information

TOTAL RAM =30G
SWAP =15G
gp_vmem_protect_limit= 2700MB
TOTAL segment = 8 Primary + 8 mirror = 16
SEGMENT HOST=2 
VM_OVERCOMMIT RATIO =72
Used this calc : http://greenplum.org/calc/#

SYMPTOM

The query failed with the error message shown below:

ERROR: XX000: Canceling query because of high VMEM usage. Used: 2433MB, available 266MB, red zone: 2430MB (runaway_cleaner.c:135)  (seg2 slice74 DATANODE01:40002 pid=11294) (cdbdisp.c:1320)

We tried :

changed following parameters statement_mem from 125 MB to 8GB MAX_STATEMENT MEMORY from 200 MB TO 16 GB

Not sure what exactly needs to change here.still, trying to understand root cause of error.

Any help in it would be much appreciated ?

Answer 1

gp_vmem_protect_limit is for per segment. You have 16segments. based on your segments and vm_protect, you need 2700MB X 16 total memory.