Search code examples
linuxgreenplumhawq

GPDB:Out of memory at segment

we re facing OOM error when trying to execute multiple SQL query session via scheduled job .

Detailed error:

The error message is: org.postgresql.util.PSQLException:ERROR: Out of memory (seg6 slice5 sungpmsh0:40002 pid=13610)
Detail: VM protect failed to allocate 65584 bytes from system, VM Protect 5835 MB available

We tried

After reading the pivotal support doc, we are doing basic troubleshoot here validated two memory parameters here

current setting in GPdb
GPDB vmprotect limit :8 GB
GPB statemen_mem: based on the vmprotect limit.as per reading it is responsible for running the query in the segment.

Test 2 Did Tuning the SQL queries. also, what should I tune here please guide?

Based on source

https://discuss.pivotal.io/hc/en-us/articles/201947018-Pivotal-Greenplum-GPDB-Memory-Configuration https://discuss.pivotal.io/hc/en-us/articles/204268778-What-are-VM-Protect-failed-to-allocate-d-bytes-d-MB-available-error-

But still getting the same OOM error.

  1. Do we need to increase the vmprotect limit? if Yes, then by which amount should we increase it?

  2. How to handle concurrency at gpdb?

  3. How much swap we need to add here when we are already running with 30 GB RAM. currently, we have added 15GB swap here? is that ok ?

  4. What is the query to identify host connection with Greenplum database ?

Thanks in advance

Solution

  • Do we need to increase the vmprotect limit? if Yes, then by which amount should we increase it?

    There is a nice calculator on setting gp_vmem_protect_limit on Greenplum.org. The setting depends on how much memory, swap, and segments per host you have.
    http://greenplum.org/calc/

    You can be getting OOM errors for several reasons.

    • Bad query
    • Bad table distribution (skew)
    • Bad settings (like gp_vmem_protect_limit)
    • Not enough resources (RAM)

    How to handle concurrency at gpdb?

    More RAM, less segments per host, and workload management to limit the number of concurrent queries running.

    How much swap we need to add here when we are already running with 30 GB RAM. currently, we have added 15GB swap here? is that ok ?

    Only 30GB of RAM? That is pretty small. You can add more swap but it will slow down the queries compared to real RAM. I wouldn't use much more than 8GB of swap.

    I recommend using 256GB of RAM or more especially if you are worried about concurrency.

    What is the query to identify host connection with Greenplum database

    select * from pg_stat_activity;