Defining Secondary Resource Pools
You can define secondary resource pools to which running queries can cascade if they exceed the initial pool's RUNTIMECAP
.
Identifying a Secondary Pool
Defining secondary resource pools allows you to designate a place where queries that exceed the RUNTIMECAP
of the pool on which they are running can execute. This way, if a query exceeds a pool's RUNTIMECAP
, the query can cascade to a pool with a larger RUNTIMECAP
instead of causing an error. When a query cascades to another pool, the original pool regains the memory used by that query.
Because grant privileges are not considered on secondary pools, you can use this functionality to designate secondary resource pools for user queries without giving users explicit permission to run queries on that pool.
You can also use secondary pools as a place to store long-running queries for later. Using the PRIORITY HOLD
option, you can designate a secondary pool that re-queues the queries until QUEUETIMEOUT
is reached or the pool's priority is changed to a non-hold value.
In Eon Mode, the following restrictions apply when defining secondary resource pools for subcluster-specific resource pools:
- Global resource pools can cascade to other global resource pools only.
- A subcluster-specific resource pool can cascade to a global resource pool, or to another subcluster-specific resource pool that belongs to the same subcluster. If a subcluster-specific resource pool cascades to a user-defined resource pool that exists on both the global and subcluster level, the subcluster-level resource pool has priority. For example:
=> CREATE RESOURCE POOL billing1; => CREATE RESOURCE POOL billing1 FOR CURRENT SUBCLUSTER; => CREATE RESOURCE POOL billing2 FOR CURRENT SUBCLUSTER CASCADE TO billing1; WARNING 9613: Resource pool billing1 both exists at both subcluster level and global level, assuming subcluster level CREATE RESOURCE POOL
Query Cascade Path
Vertica routes queries to a secondary pool when the RUNTIMECAP
on an initial pool is reached. Vertica then checks the secondary pool's RUNTIMECAP
value. If the secondary pool's RUNTIMECAP
is greater than the initial pool's value, the query executes on the secondary pool. If the secondary pool's RUNTIMECAP
is less than or equal to the initial pool's value, Vertica retries the query on the next pool in the chain until it finds a pool on which the RUNTIMECAP
is greater than the initial pool's value. If the secondary pool does not have sufficient resources available to execute the query at that time, SELECT
queries may re-queue, re-plan, and abort on that pool. Other types of queries will fail due to insufficient resources. If no appropriate secondary pool exists for a query, the query will error out.
The following diagram demonstrates the path a query takes to execution.
Query Execution Time Allocation
After Vertica finds an appropriate pool on which to run the query, it continues to execute that query uninterrupted. The query now has the difference of the two pools' RUNTIMECAP
limits in which to complete:
query execution time allocation = rp2RUNTIMECAP
- rp1RUNTIMECAP
Using the CASCADE TO Parameter
As a superuser, you can identify the secondary pool by using the CASCADE TO
parameter in the CREATE RESOURCE POOL
or ALTER RESOURCE POOL
statement. The secondary pool must already exist as a user-defined pool or the GENERAL
pool. When using CASCADE TO
, you cannot create a resource pool loop.
This example demonstrates a situation where the administrator wants user1
's queries to start on the user_0
resource pool, but cascade to the userOverflow
pool if the queries are too long.
=> CREATE RESOURCE POOL userOverflow RUNTIMECAP '5 minutes'; => CREATE RESOURCE POOL user_0 RUNTIMECAP '1 minutes' CASCADE TO userOverflow; => CREATE USER "user1" RESOURCE POOL user_0;
In this scenario, user1
cannot start his or her queries on the userOverflow
resource pool, but because grant privileges are not considered for secondary pools, user1
's queries can cascade to the userOverflow
pool if they exceed the user_0
pool RUNTIMECAP
. Using the secondary pool frees up space in the primary pool so short queries can run.
This example shows a situation where the administrator wants long-running queries to stay queued on a secondary pool.
=> CREATE RESOURCE POOL rp2 PRIORITY HOLD; => CREATE RESOURCE POOL rp1 RUNTIMECAP '2 minutes' CASCADE TO rp2; => SET SESSION RESOURCE_POOL = rp1;
In this scenario, queries that run on rp1
for more than 2 minutes will queue on rp2
until QUEUETIMEOUT
is reached, at which point the queries will be rejected.
Dropping a Secondary Pool
If you try to drop a resource pool that is a secondary pool for another resource pool, Vertica returns an error. The error lists the resource pools that depend on the secondary pool you tried to drop. To drop a secondary resource pool, first set the CASCADE TO
parameter to DEFAULT
on the primary resource pool, and then drop the secondary pool.
For example, you can drop resource pool rp2
, which is a secondary pool for rp1
, as follows:
=> ALTER RESOURCE POOL rp1 CASCADE TO DEFAULT; => DROP RESOURCE POOL rp2;
Parameter Considerations
The secondary resource pool's CPUAFFINITYSET
and CPUAFFINITYMODE
is applied to the query when it enters the pool.
The query adopts the secondary pool's RUNTIMEPRIORITY
at different times, depending on the following circumstances:
- If the
RUNTIMEPRIORITYTHRESHOLD
timer was not started when the query was running in the primary pool, the query adopts the secondary resource pools'RUNTIMEPRIORITY
when it cascades. This happens either when theRUNTIMEPRIORITYTHRESHOLD
is not set for the primary pool or theRUNTIMEPRIORITY
is set to HIGH for the primary pool. - If the
RUNTIMEPRIORITYTHRESHOLD
was reached in the primary pool, the query adopts the secondary resource pools'RUNTIMEPRIORITY
when it cascades. - If the
RUNTIMEPRIORITYTHRESHOLD
was not reached in the primary pool and the secondary pool has no threshold, the query adopts the new pool'sRUNTIMEPRIORITY
when it cascades. - If the
RUNTIMEPRIORITYTHRESHOLD
was not reached in the primary pool and the secondary pool has a threshold set. -
If the primary pool's
RUNTIMEPRIORITYTHRESHOLD
is greater than or equal to the secondary pool'sRUNTIMEPRIORITYTHRESHOLD
, the query adopts the secondary pool'sRUNTIMEPRIORITY
after the query reaches theRUNTIMEPRIORITYTHRESHOLD
of the primary pool.For example:
RUNTIMECAP
of primary pool = 5 secRUNTIMEPRIORITYTHRESHOLD
of primary pool = 8 secRUNTIMTPRIORITYTHRESHOLD
of secondary pool = 7 secIn this case, the query runs for 5 seconds on the primary pool and then cascades to the secondary pool. After another 3 seconds, 8 seconds total, the query adopts the
RUNTIMEPRIORITY
of the secondary pool. - If the primary pool's
RUNTIMEPRIORITYTHRESHOLD
is less than the secondary pool'sRUNTIMEPRIORITYTHRESHOLD
, the query adopts the secondary pool'sRUNTIMEPRIORITY
after the query reaches theRUNTIMEPRIORITYTHRESHOLD
of the secondary pool.For example,
RUNTIMECAP
of primary pool = 5 secRUNTIMEPRIORITYTHRESHOLD
of primary pool = 8 secRUNTIMTPRIORITYTHRESHOLD
of secondary pool = 12 secIn this case, the query runs for 5 seconds on the primary pool and then cascades to the secondary pool. After another 7 seconds, 12 seconds total, the query adopts the
RUNTIMEPRIORITY
of the secondary pool.