Resource pool architecture

• 1: Defining secondary resource pools
• 2: Querying resource pool settings
• 3: User profiles
• 4: Query budgeting

1 - 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 = rp2 RUNTIMECAP - rp1 RUNTIMECAP

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 the RUNTIMEPRIORITYTHRESHOLD is not set for the primary pool or the RUNTIMEPRIORITY 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's RUNTIMEPRIORITY 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's RUNTIMEPRIORITYTHRESHOLD , the query adopts the secondary pool's RUNTIMEPRIORITY after the query reaches the RUNTIMEPRIORITYTHRESHOLD of the primary pool.

    For example: RUNTIMECAP of primary pool = 5 sec
    RUNTIMEPRIORITYTHRESHOLD of primary pool = 8 sec
    RUNTIMTPRIORITYTHRESHOLD of secondary pool = 7 sec

    In 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's RUNTIMEPRIORITYTHRESHOLD, the query adopts the secondary pool's RUNTIMEPRIORITY after the query reaches the RUNTIMEPRIORITYTHRESHOLD of the secondary pool.

    For example, RUNTIMECAP of primary pool = 5 sec
    RUNTIMEPRIORITYTHRESHOLD of primary pool = 8 sec
    RUNTIMTPRIORITYTHRESHOLD of secondary pool = 12 sec

    In 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.

2 - Querying resource pool settings

You can use the following to get information about resource pools:

• RESOURCE_POOLS returns resource pool settings from the Vertica database catalog, as set by CREATE RESOURCE POOL and ALTER RESOURCE POOL.

• SUBCLUSTER_RESOURCE_POOL_OVERRIDES displays all subcluster level overrides of global resource pool settings.

For runtime information about resource pools, see Monitoring resource pools.

Querying resource pool settings

The following example queries various settings of two internal resource pools, GENERAL and TM:

=> SELECT name, subcluster_oid, subcluster_name, maxmemorysize, memorysize, runtimepriority, runtimeprioritythreshold, queuetimeout
   FROM RESOURCE_POOLS WHERE name IN('general', 'tm');
  name   | subcluster_oid | subcluster_name | maxmemorysize | memorysize | runtimepriority | runtimeprioritythreshold | queuetimeout
---------+----------------+-----------------+---------------+------------+-----------------+--------------------------+--------------
 general |              0 |                 | Special: 95%  |            | MEDIUM          |                        2 | 00:05
 tm      |              0 |                 |               | 3G         | MEDIUM          |                       60 | 00:05
(2 rows)

Viewing overrides to global resource pools

In Eon Mode, you can query SUBCLUSTER_RESOURCE_POOL_OVERRIDES in the system tables to view any overrides to global resource pools for individual subclusters. The following query shows an override that sets MEMORYSIZE and MAXMEMORYSIZE for the built-in resource pool TM to 0% in the analytics_1 subcluster. These settings prevent the subcluster from performing Tuple Mover mergeout tasks.

=> SELECT * FROM SUBCLUSTER_RESOURCE_POOL_OVERRIDES;
     pool_oid      | name |  subcluster_oid   | subcluster_name | memorysize | maxmemorysize | maxquerymemorysize
-------------------+------+-------------------+-----------------+------------+---------------+--------------------
 45035996273705058 | tm   | 45035996273843504 | analytics_1     | 0%         | 0%            |
(1 row)

3 - User profiles

User profiles are attributes associated with a user that control that user's access to several system resources. These resources include:

• Resource pool to which a user is assigned (RESOURCE POOL)

• Maximum amount of memory a user's session can use (MEMORYCAP)

• Maximum amount of temporary file storage a user's session can use (TEMPSPACECAP)

• Maximum amount of time a user's query can run (RUNTIMECAP)

You can set these attributes with the CREATE USER statement and modify the attributes later with ALTER USER.

Two strategies limit a user's access to resources: setting attributes on the user directly to control resource use, or assigning the user to a resource pool. The first method lets you fine tune individual users, while the second makes it easier to group many users together and set their collective resource usage.

If you limit user resources collectively with resource pool assignments, consider the following:

• A user cannot log in to Vertica unless they either have privileges to the GENERAL pool, or are assigned to a default resource pool.

• If a user's default resource pool is dropped, the user's queries use the GENERAL pool.

• If a user does not have privileges to the GENERAL pool and you attempt to drop their assigned resource pool, the DROP operation fails.

In an Eon Mode database, you can set the user's default resource pool to a subcluster-specific resource pool. If that is the case, Vertica uses one of the following methods to determine which resource pool is used for queries when a user connects to a subcluster:

• If the subcluster uses their assigned default resource pool, then the user's queries use their assigned resource pool.

• If the subcluster does not use their assigned default resource pool, but the user has access to the GENERAL pool, the user's queries use the GENERAL pool.

• If the subcluster does not use the resource pool assigned to the user, and the user does not have privileges to the GENERAL pool, then the user cannot query from any node of this subcluster.

The following examples illustrate how to set a user's resource pool attributes. For additional examples, see the scenarios described in Using user-defined pools and user-profiles for workload management.

Example

Set the user's RESOURCE POOL attribute to assign the user to a resource pool. To create a user named user1 who has access to the resource pool my_pool, use the command:

=> CREATE USER user1 RESOURCE POOL my_pool;

To limit the amount of memory for a user without designating a pool, set the user's MEMORYCAP to either a particular unit or a percentage of the total memory available. For example, to create a user named user2 whose sessions are limited to using 200 MBs memory each, use the command:

=> CREATE USER user2 MEMORYCAP '200M';

To limit the time a user's queries are allowed to run, set the RUNTIMECAP attribute. To prevent queries for user2 from running more than five minutes, use this command:

=> ALTER USER user2 RUNTIMECAP '5 minutes';

To limit the amount of temporary disk space that the user's sessions can use, set the TEMPSPACECAP to either a particular size or a percentage of temporary disk space available. For example, the next statement creates user3, and limits her to using 1 GB of temporary space:

=> CREATE USER user3 TEMPSPACECAP '1G';

You can combine different attributes into a single command. For example, to limit the MEMORYCAP and RUNTIMECAP for user3, include both attributes in an ALTER USER statement:

=> ALTER USER user3 MEMORYCAP '750M' RUNTIMECAP '10 minutes';
ALTER USER
=> \x
Expanded display is on.
=> SELECT * FROM USERS;
-[ RECORD 1 ]-----+------------------
user_id           | 45035996273704962
user_name         | release
is_super_user     | t
resource_pool     | general
memory_cap_kb     | unlimited
temp_space_cap_kb | unlimited
run_time_cap      | unlimited
-[ RECORD 2 ]-----+------------------
user_id           | 45035996273964824
user_name         | user1
is_super_user     | f
resource_pool     | my_pool
memory_cap_kb     | unlimited
temp_space_cap_kb | unlimited
run_time_cap      | unlimited
-[ RECORD 3 ]-----+------------------
user_id           | 45035996273964832
user_name         | user2
is_super_user     | f
resource_pool     | general
memory_cap_kb     | 204800
temp_space_cap_kb | unlimited
run_time_cap      | 00:05
-[ RECORD 4 ]-----+------------------
user_id           | 45035996273970230
user_name         | user3
is_super_user     | f
resource_pool     | general
memory_cap_kb     | 768000
temp_space_cap_kb | 1048576
run_time_cap      | 00:10

See also

• ALTER USER
• CREATE USER

4 - Query budgeting

Before it can execute a query, Vertica devises a query plan, which it sends to each node that will participate in executing the query. The Resource Manager evaluates the plan on each node and estimates how much memory and concurrency the node needs to execute its part of the query. This is the query budget, which Vertica stores in the query_budget_kb column of system table V_MONITOR.RESOURCE_POOL_STATUS.

A query budget is based on several parameter settings of the resource pool where the query will execute:

• MEMORYSIZE

• MAXMEMORYSIZE

• PLANNEDCONCURRENCY

You can modify MAXMEMORYSIZE and PLANNEDCONCURRENCY for the GENERAL resource pool with ALTER RESOURCE POOL. This resource pool typically executes queries that are not assigned to a user-defined resource pool. You can set all three parameters for any user-defined resource pool when you create it with CREATE RESOURCE POOL, or later with ALTER RESOURCE POOL.

Computing the GENERAL pool query budget

Vertica calculates query budgets in the GENERAL pool with the following formula:

queryBudget = queuingThresholdPool / PLANNEDCONCURRENCY

Computing query budgets for user-defined resource pools

For user-defined resource pools, Vertica uses the following algorithm:

1. If MEMORYSIZE is set to 0 and MAXMEMORYSIZE is not set:

   queryBudget = queuingThresholdGeneralPool / PLANNEDCONCURRENCY
   

2. If MEMORYSIZE is set to 0 and MAXMEMORYSIZE is set to a non-default value:

   query-budget = queuingThreshold / PLANNEDCONCURRENCY
   

   Note

   Vertica calculates a user-defined pool's queuing threshold as 95 percent of its MAXMEMORYSIZE setting.

3. If MEMORYSIZE is set to a non-default value:

   queryBudget = MEMORYSIZE / PLANNEDCONCURRENCY
   

By carefully tuning a resource pool's MEMORYSIZE and PLANNEDCONCURRENCY parameters, you can control how much memory can be budgeted for queries.

See also

Do You Need to Put Your Query on a Budget? in the Vertica User Community.