ALTER RESOURCE POOL

Modifies an existing resource pool by setting one or more parameters.

Modifies an existing resource pool by setting one or more parameters.

Syntax

ALTER RESOURCE POOL pool-name [ FOR subcluster-spec ] [ parameter-name setting ]...

Parameters

pool-name
The name of the resource pool. Built-in pool names cannot be used for user-defined pools.
FOR subcluster-spec
Eon Mode only, specifies to associate this resource pool with a subcluster, where subcluster-spec is one of the following:
  • SUBCLUSTER subcluster-name: Alters the resource pool for an existing subcluster. You cannot be connected to this subcluster, otherwise Vertica returns an error.
  • CURRENT SUBCLUSTER: Alters the resource pool for the subcluster that you are connected to.
parameter-name
The parameter to set, listed below.
setting

The value to set on parameter-name. To reset this parameter to its default value, specify DEFAULT.

CASCADE TO

Specifies a secondary resource pool for executing queries that exceed the [RUNTIMECAP](#RUNTIMECAP) setting of their assigned resource pool:

CASCADE TO secondary-pool
CPUAFFINITYMODE

Specifies whether the resource pool has exclusive or shared use of the CPUs specified in [CPUAFFINITYSET](#CPUAFFINITYSET):

CPUAFFINITYMODE { SHARED | EXCLUSIVE | ANY }

  • SHARED: Queries that run in this pool share its CPUAFFINITYSET CPUs with other Vertica resource pools.

  • EXCLUSIVE: Dedicates CPUAFFINITYSET CPUs to this resource pool only, and excludes other Vertica resource pools. If CPUAFFINITYSET is set as a percentage, then that percentage of CPU resources available to Vertica is assigned solely for this resource pool.

  • ANY (default): Queries in this resource pool can run on any CPU, invalid if CPUAFFINITYSET designates CPU resources.

CPUAFFINITYSET

Specifies which CPUs are available to this resource pool. All cluster nodes must have the same number of CPUs. The CPU resources assigned to this set are unavailable to general resource pools.

CPUAFFINITYSET {
  'cpu-index[,...]'
| 'cpu-indexi-cpu-indexn'
| 'integer%'
| NONE
}

  • cpu-index[,...]: Dedicates one or more comma-delimited CPUs to this pool.

  • **``*cpu-indexi*-*cpu-index*n**: Dedicates a range of contiguous CPU indexes to this pool

  • integer%: Percentage of all available CPUs to use for this pool. Vertica rounds this percentage down to include whole CPU units.

  • NONE (default): No affinity set is assigned to this resource pool. The queries associated with this pool are executed on any CPU.

EXECUTIONPARALLELISM

Limits the number of threads used to process any single query issued in this resource pool.

EXECUTIONPARALLELISM { limit | AUTO }

  • limit: An integer value between 1 and the number of cores. Setting this parameter to a reduced value increases throughput of short queries issued in the pool, especially if the queries are executed concurrently.

  • AUTO or 0 (default): Vertica calculates the setting from the number of cores, available memory, and amount of data in the system. Unless memory is limited, or the amount of data is very small, Vertica sets this parameter to the number of cores on the node.

MAXCONCURRENCY

Sets the maximum number of concurrent execution slots available to the resource pool, across the cluster:

MAXCONCURRENCY { integer | NONE }

NONE (default) specifies unlimited number of concurrent execution slots.

MAXMEMORYSIZE

The maximum size per node the resource pool can grow by borrowing memory from the GENERAL pool:

MAXMEMORYSIZE {
  'integer%'
| 'integer{K|M|G|T}'
| NONE
}

  • integer%: Percentage of total memory

  • integer{K|M|G|T}: Amount of memory in kilobytes, megabytes, gigabytes, or terabytes

  • NONE (default): Unlimited, pool can borrow any amount of available memory from the GENERAL pool.

MAXQUERYMEMORYSIZE

The maximum amount of memory that this pool can allocate at runtime to process a query. If the query requires more memory than this setting, Vertica stops execution and returns an error.

Set this parameter as follows:

MAXQUERYMEMORYSIZE {
  'integer%'
| 'integer{K|M|G|T}'
| NONE
}

  • integer%: Percentage of MAXMEMORYSIZE for this pool.

  • integer{K|M|G|T}: Amount of memory in kilobytes, megabytes, gigabytes, or terabytes, up to the value of MAXMEMORYSIZE.

  • NONE (default): Unlimited; pool can borrow any amount of available memory from the GENERAL pool, within the limits set by MAXMEMORYSIZE.

MEMORYSIZE

The amount of total memory available to the Vertica resource manager that is allocated to this pool per node:

MEMORYSIZE {
  'integer%'
| 'integer{K|M|G|T}'
}

  • integer%: Percentage of total memory

  • integer{K|M|G|T}: Amount of memory in kilobytes, megabytes, gigabytes, or terabytes

Default: 0%. No memory allocated, the resource pool borrows memory from the GENERAL pool.

PLANNEDCONCURRENCY

Specifies the preferred number queries to execute concurrently in the resource pool. This setting applies to the entire cluster:

PLANNEDCONCURRENCY { num-queries | AUTO }

  • num-queries: Integer value ≥ 1, specifies the preferred number of concurrently executing queries. When possible, query resource budgets are limited to allow this level of concurrent execution.

  • AUTO (default): Value is calculated automatically at query runtime. Vertica sets this parameter to the lower of these two calculations, but never less than 4:

    • Number of logical cores

    • Memory divided by 2GB

For clusters where the number of logical cores differs on different nodes, AUTO can apply differently on each node. Distributed queries run like the minimal effective planned concurrency. Single node queries run with the planned concurrency of the initiator.

PRIORITY

Specifies priority of queries in this pool when they compete for resources in the GENERAL pool:

PRIORITY { integer | HOLD }

  • integer: A negative or positive integer value, where higher numbers denote higher priority:

  • HOLD: Sets priority to -999. Queries in this pool are queued until [QUEUETIMEOUT](#QUEUETIMEOUT) is reached.

Default: 0

QUEUETIMEOUT

Species how long a request can wait for pool resources before it is rejected:

QUEUETIMEOUT { integer | NONE }

  • integer: Maximum wait time in seconds

  • NONE: No maximum wait time, request can be queued indefinitely.

**Default:** 300 seconds

RUNTIMECAP

Prevents runaway queries by setting the maximum time a query in the pool can execute. If a query exceeds this setting, it tries to cascade to a secondary pool:

RUNTIMECAP { 'interval' | NONE }

  • interval: Maximum wait time expressed in the following format:

  • NONE (default): No time limit on queries running in this pool.

    To specify a value in days, provide an integer value. To provide a value less than one day, provide the interval in the format hours:minutes:seconds. For example a value of 1:30:00 would equal 90 minutes.

    If the user or session also has a RUNTIMECAP, the shorter limit applies.

RUNTIMEPRIORITY

Determines how the resource manager should prioritize dedication of run-time resources (CPU, I/O bandwidth) to queries already running in this resource pool:

RUNTIMEPRIORITY { HIGH | MEDIUM | LOW }

Default: MEDIUM

RUNTIMEPRIORITYTHRESHOLD

Specifies in seconds a time limit in which a query must finish before the resource manager assigns to it the resource pool's RUNTIMEPRIORITY. All queries begin running at a HIGH priority. When a query's duration exceeds this threshold, it is assigned the RUNTIMEPRIORITY of the resource pool.

RUNTIMEPRIORITYTHRESHOLD seconds

Default: 2

SINGLEINITIATOR

By default, set to false for backward compatibility. Do not change this setting.

Privileges

Superuser

Examples

This example shows how to alter resource pool ceo_pool by setting the priority to 5.

=> ALTER RESOURCE POOL ceo_pool PRIORITY 5;

This example shows how to designate a secondary resource pool for ceo_pool.

=> CREATE RESOURCE POOL second_pool;
=> ALTER RESOURCE POOL ceo_pool CASCADE TO second_pool;

This Eon Mode example decreases the MAXMEMORYSIZE and MEMORYSIZE of the built-in TM resource pool to 0% on the dashboard secondary subcluster. Changing these settings to 0 prevents the subcluster from running Tuple Mover mergeout operations.

=> ALTER RESOURCE POOL TM FOR SUBCLUSTER dashboard MEMORYSIZE '0%'
   MAXMEMORYSIZE '0%';

See Tuning tuple mover pool settings for more information.

See also