For cascading pools

khaldrogo

For cascading pools, does cascading really do anything if we don't have the following parameter set to 1?
If a query cascades without replanning, will the memory acquired for the query get returned to the original pool and then be taken from the new pool it cascaded too?

Vertica_Curtis

Are you referring to the parameter "CascadeResourcePoolAlwaysReplan"?

I assume that you are. If that parameter is not set, and a query cascades into a different pool, about the only benefit you really acquire is that you can lower the priority of the request (RuntimePriority). Setting the parameter to 1, does force the query to restart, and it will replan it according to the resource pool parameters of the cascaded resource pool - but it will add time to the query, since it's restarting from scratch. If you have a RunTimeCap of 1 minute, then you'll lose the whole minute due to the replan.

Keep in mind that the RunTimePriorityThreshold parameter acts as a kind of cascade as well. The query will run as HIGH for the number of seconds defined by that parameter - usually 2 seconds, and then will back it down to the RunTimePriority defined by the pool, which likely should be MEDIUM RunTimePriority.

Also, there's no reason at all to have two-levels of Cascaded pool - the "first" is given to you by the RunTimePriorityThreshold. The pool itself should likely have a RunTimePriority of MEDIUM, and then you can cascade that pool off to a 2nd resource pool defined with a LOW RunTimePriority. I often see people cascading that again into a 3rd resource pool, which adds no value whatsoever, since there's nothing below LOW RunTimePriority.

Lastly, NEVER set a resource pool to HIGH - that should be reserved for the SysQuery pools, If there's an issue with a user query, and it hogs the system (run-away cartesion join, for example), if that pool is HIGH priority, then even the DBA won't be able to sign into the system in order to troubleshoot or kill the query, since the DBA will be competing against that query.