ALTER RESOURCE POOL
Modifies an existing resource pool by setting one or more parameters.
You can use ALTER RESOURCE POOL to modify some parameters in Vertica built-in resource pools. For details on default settings and restrictions, see Built-in resource pools configuration.
Important
Changes to parameters of the built-in GENERAL resource pool take effect only when the database restarts.Syntax
ALTER RESOURCE POOL pool-name [ FOR subcluster ] parameter-name setting[...]
Arguments
pool-name
- Name of the resource pool to modify.
FOR
subcluster
Eon Mode only, the subcluster to associate with this resource pool, where
subcluster
is one of the following:SUBCLUSTER
subcluster-name
: Resource pool for an existing subcluster. You cannot be connected to this subcluster, otherwise Vertica returns an error.CURRENT SUBCLUSTER
: Resource pool for the subcluster that you are connected to.
Important
You cannot use ALTER RESOURCE POOL to convert a global resource pool to a subcluster-level resource pool.parameter-name setting
- A resource pool parameter and its new setting. To reset this parameter to its default value, specify
DEFAULT
.If you specify a subcluster, you can alter only the
MAXMEMORYSIZE
,MAXQUERYMEMORYSIZE
, andMEMORYSIZE
parameters for built-in pools.
Parameters
Note
Default values specified here pertain only to user-defined resource pools. For built-in pool default values, see Built-in resource pools configuration, or query system table RESOURCE_POOL_DEFAULTS.-
CASCADE TO
Secondary resource pool for executing queries that exceed the
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
:CPUAFFINITYMODE { SHARED | EXCLUSIVE | ANY }
SHARED
: Queries that run in this resource pool share itsCPUAFFINITYSET
CPUs with other Vertica resource pools.EXCLUSIVE
: DedicatesCPUAFFINITYSET
CPUs to this resource pool only, and excludes other Vertica resource pools. IfCPUAFFINITYSET
is set as a percentage, then that percentage of CPU resources available to Vertica is assigned solely for this resource pool.ANY
: Queries in this resource pool can run on any CPU, invalid ifCPUAFFINITYSET
designates CPU resources.
Default:
ANY
-
CPUAFFINITYSET
CPUs 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 resource pool.cpu-indexi-cpu-indexn
: Dedicates a range of contiguous CPU indexesi
throughn
to this resource pool.integer
%
: Percentage of all available CPUs to use for this resource pool. Vertica rounds this percentage down to include whole CPU units.NONE
(empty string): No affinity set is assigned to this resource pool. Queries associated with this pool are executed on any CPU.
Default:
NONE
Important
CPUAFFINITYSET
andCPUAFFINITYMODE
must be set together in the same statement.-
EXECUTIONPARALLELISM
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 resource pool, especially if queries are executed concurrently.AUTO
or0
: 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.
Default:
AUTO
-
MAXCONCURRENCY
Maximum number of concurrent execution slots available to the resource pool across the cluster:
MAXCONCURRENCY { integer | NONE }
NONE
(empty string): Unlimited number of concurrent execution slots.Default:
NONE
-
MAXMEMORYSIZE
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 memoryinteger
{K|M|G|T}
: Amount of memory in kilobytes, megabytes, gigabytes, or terabytesNONE
(empty string): Unlimited, resource pool can borrow any amount of available memory from theGENERAL
pool.
Default:
NONE
MAXQUERYMEMORYSIZE
Maximum amount of memory this resource 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 ofMAXMEMORYSIZE
for this resource pool.integer
{K|M|G|T}
: Amount of memory in kilobytes, megabytes, gigabytes, or terabytes, up to the value ofMAXMEMORYSIZE
.NONE
(empty string): Unlimited; resource pool can borrow any amount of available memory from the GENERAL pool, within the limits set byMAXMEMORYSIZE
.
Default:
NONE
Important
Changes toMAXQUERYMEMORYSIZE
are applied retroactively to queries that are currently executing. If you reduce this setting, queries that were budgeted with the previous memory size are liable to fail if they try to allocate more memory than the new setting allows.-
MEMORYSIZE
Total per-node memory available to the Vertica resource manager that is allocated to this resource pool:
MEMORYSIZE { 'integer%' | 'integer{K|M|G|T}' }
integer
%
: Percentage of total memoryinteger
{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
Preferred number of queries to execute concurrently in the resource pool. This setting applies to the entire cluster:
PLANNEDCONCURRENCY { num-queries | AUTO }
-
num-queries
: Integer value ≥ 1, the preferred number of queries to execute concurrently in the resource pool. When possible, query resource budgets are limited to allow this level of concurrent execution. -
AUTO
: 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
If the number of logical cores on each node is different,
AUTO
is calculated differently for each node. Distributed queries run like the minimal effective planned concurrency. Single node queries run with the planned concurrency of the initiator. -
Default:
AUTO
Tip
Change this parameter only after evaluating performance over a period of time.-
-
PRIORITY
Priority of queries in this resource pool when they compete for resources in the
GENERAL
pool:PRIORITY { integer | HOLD }
-
integer
: Negative or positive integer value, where higher numbers denote higher priority: -
HOLD
: Sets priority to-999
. Queries in this resource pool are queued untilQUEUETIMEOUT
is reached.
Default: 0
-
-
QUEUETIMEOUT
Maximum time a request can wait for pool resources before it is rejected, not more than one year:
QUEUETIMEOUT { integer | 'interval' | 'NONE' }
-
integer
: Maximum wait time in seconds -
[interval](/en/sql-reference/language-elements/literals/datetime-literals/interval-literal/): Maximum wait time expressed in the following format:
num year num months num [days] HH:MM:SS.ms
-
NONE
(empty string): No maximum wait time, request can be queued indefinitely, up to one year.
If the value that you specify resolves to more than one year, Vertica returns with a warning and sets the parameter to 365 days:
=> ALTER RESOURCE POOL user_0 QUEUETIMEOUT '11 months 50 days 08:32'; WARNING 5693: Using 1 year for QUEUETIMEOUT ALTER RESOURCE POOL => SELECT QUEUETIMEOUT FROM resource_pools WHERE name = 'user_0'; QUEUETIMEOUT -------------- 365 (1 row)
Default: 00:05 (5 minutes)
-
-
RUNTIMECAP
Maximum execution time allowed to queries in this resource pool, not more than one year, otherwise Vertica returns with an error. 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:num year num month num [day] HH:MM:SS.ms
-
NONE
(empty string): No maximum wait time, request can be queued indefinitely, up to one year.
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
Maximum time (in seconds) in which query processing must complete before the resource manager assigns to it the resource pool's
RUNTIMEPRIORITY
. All queries begin execution with a priority of HIGH.RUNTIMEPRIORITYTHRESHOLD seconds
Default:
2
SINGLEINITIATOR
Set to false for backward compatibility. Do not change this setting.
Privileges
Superuser
Examples
Set resource pool PRIORITY to 5:
=> ALTER RESOURCE POOL ceo_pool PRIORITY 5;
Designate a secondary resource pool:
=> CREATE RESOURCE POOL second_pool;
=> ALTER RESOURCE POOL ceo_pool CASCADE TO second_pool;
Decrease to 0% the MAXMEMORYSIZE and MEMORYSIZE settings on the dashboard
subcluster's built-in TM resource pool. Changing these settings to 0 prevents the subcluster from running mergeout operations:
=> ALTER RESOURCE POOL TM FOR SUBCLUSTER dashboard MEMORYSIZE '0%'
MAXMEMORYSIZE '0%';
See Tuning tuple mover pool settings for more information.