Skip to main content
Question

Resource manager - custom pools

  • June 4, 2020
  • 1 reply
  • 10 views
ramki

Hi, We currently have everyone assigned to general_pool and it seemed to have worked so far, but we do see etl jobs or reports fail at times with resource limit, it's always the memory, we have a 3-node cluster with 128GB memory on each node and would like to create the following custom pools and assign to those respective users?
Idea is to make etl jobs to have higher priority when resources are allocated, followed by reports user and individual users.

CREATE RESOURCE POOL etl_pool
MEMORYSIZE 0%
MAXMEMORYSIZE 90%
PRIORITY 10
QUEUETIMEOUT 300
RUNTIMECAP NONE

RUNTIMEPRIORITY HIGH;

CREATE RESOURCE POOL tableau_pool
MEMORYSIZE 0%
MAXMEMORYSIZE 80%
PRIORITY 9
QUEUETIMEOUT 600
RUNTIMECAP NONE

RUNTIMEPRIORITY MEDIUM;

CREATE RESOURCE POOL adhoc_pool
MEMORYSIZE 0%
MAXMEMORYSIZE 60%
PRIORITY 8
QUEUETIMEOUT 600
RUNTIMECAP 'minutes 30'
RUNTIMEPRIORITY MEDIUM;
But I was reading best practices article and found that sum of all custom pools should not exceed 20% above of max physical memory?
https://www.vertica.com/kb/BestPracticesforManagingResourcePools/Content/BestPractices/BestPracticesforManagingResourcePools.htm
"We typically recommend that the sum of the MAXMEMORYSIZE of user-defined pools does not exceed 20% above the available physical memory."
Regards,
Ramki

    1 reply

    ramki
    • ramki
    • Author
    • New Participant
    • June 4, 2020

    Thanks Curtis, Appreciate it.
    I am an Oracel/MySQL DBA, recently took over Vertica administration, all users have been running things in general_pool, so wanted to put some restrictions on how much memory one can use and how long their query can run etc, often we are seeing one of node gets killed by OOM killer, here are my updated configs for the three pools, I guess I will have to deal with people starting to scream as they cannot run all their reports as they used to earlier, may be that's when we get to look at what they are trying to see and run thru designer to see if any improvements can be made to improve the overall performance.

    CREATE RESOURCE POOL etl_pool
    MAXMEMORYSIZE 50%
    MAXCONCURRENCY 5
    PRIORITY 10;

    CREATE RESOURCE POOL tableau_pool
    MAXMEMORYSIZE 40%
    PRIORITY 9
    QUEUETIMEOUT 600
    MAXCONCURRENCY 5;

    CREATE RESOURCE POOL adhoc_pool
    MAXMEMORYSIZE 30%
    PRIORITY 8
    QUEUETIMEOUT 600
    RUNTIMECAP 'minutes 60'
    MAXCONCURRENCY 5;

    We are adding a 4th node, that would mean another 100+GB memory to the cluster.

    Regards,
    Ramki

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement