ALTER RESOURCE POOL
Modifies an existing resource pool by setting one or more parameters.
Syntax
ALTER RESOURCE POOL pool‑name [ parameter‑name setting ]...
Parameters
Note: You can set all resource pool parameters to their DEFAULT
value. The V_CATALOG.RESOURCE_POOL_DEFAULTS
system table contains default parameter values. Query this table to determine default settings for all resource pools.
Default values specified in this table pertain only to user-defined resource pools. For built-in pool default values, see Built-In Pool Configuration.
pool-name | The name of the resource pool. Built-in pool names cannot be used for user-defined pools. |
parameter‑name | The parameter to set, listed below. |
CASCADE TO
|
Specifies a secondary resource pool for executing queries that exceed the CASCADE TO secondary‑pool
|
CPUAFFINITYMODE
|
Specifies whether the resource pool has exclusive or shared use of the CPUs specified in CPUAFFINITYMODE {
|
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 {
|
EXECUTIONPARALLELISM
|
Limits the number of threads used to process any single query issued in this resource pool. EXECUTIONPARALLELISM { integer | AUTO }
|
MAXCONCURRENCY
|
Sets the maximum number of concurrent execution slots available to the resource pool, across the cluster: MAXCONCURRENCY { integer | NONE }
|
MAXMEMORYSIZE
|
The maximum size per node the resource pool can grow by borrowing memory from the MAXMEMORYSIZE { 'integer%' 'integer{K|M|G|T}' NONE }
|
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}' }
Default: 0%. No memory allocated, the resource pool borrows memory from the |
PLANNEDCONCURRENCY
|
Specifies the preferred number queries to execute concurrently in the resource pool. This setting applies to the entire cluster: PLANNEDCONCURRENCY { integer | AUTO }
For clusters where the number of logical cores differs on different nodes, Tip: Change this parameter only after evaluating performance over a period of time. |
PRIORITY
|
Specifies priority of queries in this pool when they compete for resources in the PRIORITY { integer | HOLD }
Default: 0 |
QUEUETIMEOUT
|
Species how long a request can wait for pool resources before it is rejected: QUEUETIMEOUT { integer | NONE }
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 }
To specify a value in days, provide an integer value. To provide a value less than one day, provide the interval in the format If the user or session also has a |
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: |
RUNTIMEPRIORITYTHRESHOLD
|
Specifies in seconds a time limit in which a query must finish before the resource manager assigns to it the resource pool's RUNTIMEPRIORITYTHRESHOLD seconds
Default: |
Privileges
The following parameters require superuser privileges on the resource pool:
- CASCADE TO
- CPUAFFINITYSET
- CPUAFFINITYMODE
- MAXMEMORYSIZE
- PRIORITY
- QUEUETIMEOUT
The following parameters require UPDATE privileges on the resource pool:
- MAXCONCURRENCY
- PLANNEDCONCURRENCY
- SINGLEINITIATOR
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 pool for the ceo_pool.
=> CREATE RESOURCE POOL second_pool; => ALTER RESOURCE POOL ceo_pool CASCADE TO second_pool;