1

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 ?

vs0731
  • 17
  • 1
  • 8

1 Answers1

2

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.

Sung Yu-wei
  • 161
  • 8
  • thanks,we have to total vm memory available = 45 GB . in this case , what should be vmprotect limit 1400 ? – vs0731 Sep 05 '17 at 20:23
  • 16 segment on 2 segment host server. – vs0731 Sep 05 '17 at 20:30
  • 1
    overcommit ratio is set to 75, I don't think you have 45GB vmem. 30*0.75 + 15 ~= 37.5G. You need to reserve memory for OS. Let's say you reserve 32G for GPDB, then 32/16=2GB. Set vm protect to 2048MB. Note that GPDB doesn't like memory overcommit. You have 30G physical memory, but your current have 37.5G vmem addresses. Also, assign number of segments based on your cpu cores. you don't want gpdb overcommit cpu too. – Sung Yu-wei Sep 05 '17 at 20:37
  • This error is triggered by runaway_detector. You can either disable runaway_detector or avoid running queries as "gpadmin". – Sung Yu-wei Sep 05 '17 at 20:42
  • Thanks for nice thought. I am going to check into the env. – vs0731 Sep 05 '17 at 20:44
  • After disabling the parameter runaway_detector_activation_percent . showing OOM error – vs0731 Sep 05 '17 at 20:57
  • Still same error exist here . gp_vmem_limit_per_query set to 0 (default) what to set here. i am trying to understand – vs0731 Sep 07 '17 at 17:19
  • Still, oom exists – vs0731 Sep 10 '17 at 13:30