Resource manager - custom pools

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

Vertica_Curtis

Never set a custom resource pool to RUNTIMEPRIORITY HIGH; Terrible idea. Only the DBA pools (sysquery, sysdata) should ever have HIGH runtimepriority. The reason is that queries in those pools will completely dominate everything, and if a request takes a long time, even a DBA won't be able to sign in in order to debug the problem. The query will literally own your system until it completes.

RunTimePriorityThreshold is a powerful parameter as well, since it allows a query to run as HIGH priority for a given number of seconds. So, it's kind of like a built-in cascade pool. But you also don't want to set this too high for the same reason as above. 2-5 seconds is probably reasonable for most requests.

You've also set all your MAXMEMORYSIZE to be a % total greater than 100%. How can your system allow for 90% of the memory to be used by the ETL pool AND also allow 80% of the memory to be used by the Tableau pool?

You might consider, instead, setting a MaxConcurrency. That's easier to manage in my mind. You might have a better sense of how many concurrent requests might be firing at any given time. You could control that knob pretty easily, and adjust it accordingly.
Setting a RunTimeCap is also a good idea. Even good people write bad queries, so it's not a bad idea to cap stuff (even if it's several hours) to keep the rogue cartesian join from running for a day and a half.

ramki

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