Built-in resource pools configuration

To view the current and default configuration for built-in resource pools, query the system tables RESOURCE_POOLS and RESOURCE_POOL_DEFAULTS, respectively.

To view the current and default configuration for built-in resource pools, query the system tables RESOURCE_POOLS and RESOURCE_POOL_DEFAULTS, respectively. The sections below provide this information, and also indicate which built-in pool parameters can be modified with ALTER RESOURCE POOL:

GENERAL

Parameter Settings
MEMORYSIZE Empty / cannot be set
MAXMEMORYSIZE

The maximum memory to use for all resource pools, one of the following:

MAXMEMORYSIZE {
  'integer%'
 | 'integer{K|M|G|T}'
}
  • integer%: Percentage of total system RAM, must be ≥ 25%
  • integer{K|M|G|T}: Amount of memory in kilobytes, megabytes, gigabytes, or terabytes, must be ≥ 1GB

For example, if your node has 64GB of memory, setting MAXMEMORYSIZE to 50% allocates half of available memory. Thus, the maximum amount of memory available to all resource pools is 32GB.

Default: 95%

MAXQUERYMEMORYSIZE

The maximum amount of memory allocated by this pool to process any query:

MAXQUERYMEMORYSIZE {
  'integer%'
 | 'integer{K|M|G|T}'
}
  • integer%: Percentage of MAXMEMORYSIZE for this pool.

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

EXECUTIONPARALLELISM Default: AUTO
PRIORITY Default: 0
RUNTIMEPRIORITY Default: Medium
RUNTIMEPRIORITYTHRESHOLD Default: 2
QUEUETIMEOUT Default: 00:05 (minutes)
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: An interval of 1 minute or 100 seconds; should not exceed one year.

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

PLANNEDCONCURRENCY

The number of concurrent queries you expect to run against the resource pool, an integer ≥ 4. If set to AUTO (default), Vertica automatically sets PLANNEDCONCURRENCY at query runtime, choosing the lower of these two values:

  • Number of cores

  • Memory/2GB

Default: AUTO

MAXCONCURRENCY

Default: Empty

SINGLEINITIATOR

Default: False.

CPUAFFINITYSET Default: Empty
CPUAFFINITYMODE Default: ANY
CASCADETO Default: Empty

BLOBDATA

Parameter Default Setting
MEMORYSIZE 0%
MAXMEMORYSIZE 10
MAXQUERYMEMORYSIZE Empty / cannot be set
EXECUTIONPARALLELISM
PRIORITY
RUNTIMEPRIORITY
RUNTIMEPRIORITYTHRESHOLD
QUEUETIMEOUT
RUNTIMECAP NONE
PLANNEDCONCURRENCY AUTO
MAXCONCURRENCY Empty / cannot be set
SINGLEINITIATOR
CPUAFFINITYSET
CPUAFFINITYMODE ANY / cannot be set
CASCADETO Empty / cannot be set

DBD

Parameter Default Setting
MEMORYSIZE 0%
MAXMEMORYSIZE Unlimited
MAXQUERYMEMORYSIZE Empty / cannot be set
EXECUTIONPARALLELISM AUTO
PRIORITY 0
RUNTIMEPRIORITY MEDIUM
RUNTIMEPRIORITYTHRESHOLD 0
QUEUETIMEOUT 0
RUNTIMECAP NONE
PLANNEDCONCURRENCY AUTO
MAXCONCURRENCY NONE
SINGLEINITIATOR

True

CPUAFFINITYSET Empty / cannot be set
CPUAFFINITYMODE ANY / cannot be set
CASCADETO Empty / cannot be set

JVM

Parameter Default Setting
MEMORYSIZE 0%
MAXMEMORYSIZE 10% of memory or 2 GB, whichever is smaller
MAXQUERYMEMORYSIZE Empty / cannot be set
EXECUTIONPARALLELISM AUTO
PRIORITY 0
RUNTIMEPRIORITY MEDIUM
RUNTIMEPRIORITYTHRESHOLD 2
QUEUETIMEOUT 00:05 (minutes)
RUNTIMECAP NONE
PLANNEDCONCURRENCY AUTO
MAXCONCURRENCY Empty / cannot be set
SINGLEINITIATOR

FALSE

CPUAFFINITYSET Empty / cannot be set
CPUAFFINITYMODE ANY / cannot be set
CASCADETO Empty / cannot be set

METADATA

Parameter Default Setting
MEMORYSIZE 0%
MAXMEMORYSIZE Unlimited
MAXQUERYMEMORYSIZE Empty / cannot be set
EXECUTIONPARALLELISM AUTO
PRIORITY 108
RUNTIMEPRIORITY HIGH
RUNTIMEPRIORITYTHRESHOLD 0
QUEUETIMEOUT 0
RUNTIMECAP NONE
PLANNEDCONCURRENCY AUTO
MAXCONCURRENCY 0
SINGLEINITIATOR

FALSE.

CPUAFFINITYSET Empty / cannot be set
CPUAFFINITYMODE ANY / cannot be set
CASCADETO Empty / cannot be set

RECOVERY

Parameter Default Setting
MEMORYSIZE 0%
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 Empty / cannot be set
EXECUTIONPARALLELISM AUTO
PRIORITY

One of the following:

  • Enterprise Mode: 107

  • Eon Mode: 110

RUNTIMEPRIORITY MEDIUM
RUNTIMEPRIORITYTHRESHOLD 60
QUEUETIMEOUT 00:05 (minutes)
RUNTIMECAP NONE
PLANNEDCONCURRENCY AUTO
MAXCONCURRENCY

By default, set as follows:

(numberCores / 2) + 1

Thus, given a system with four cores, MAXCONCURRENCY has a default setting of 3.

SINGLEINITIATOR

True.

CPUAFFINITYSET Empty / cannot be set
CPUAFFINITYMODE ANY / cannot be set
CASCADETO Empty / cannot be set

REFRESH

Parameter Default Setting
MEMORYSIZE 0%
MAXMEMORYSIZE NONE (unlimited)
MAXQUERYMEMORYSIZE Empty / cannot be set
EXECUTIONPARALLELISM AUTO
PRIORITY -10
RUNTIMEPRIORITY MEDIUM
RUNTIMEPRIORITYTHRESHOLD 60
QUEUETIMEOUT 00:05 (minutes)
RUNTIMECAP NONE (unlimited)
PLANNEDCONCURRENCY AUTO (4)
MAXCONCURRENCY

3

This parameter must be set ≥ 1.

SINGLEINITIATOR

True.

CPUAFFINITYSET Empty / cannot be set
CPUAFFINITYMODE ANY / cannot be set
CASCADETO Empty / cannot be set

SYSQUERY

Parameter Default Setting
MEMORYSIZE

1G

MAXMEMORYSIZE Empty (unlimited)
MAXQUERYMEMORYSIZE Empty / cannot be set
EXECUTIONPARALLELISM AUTO
PRIORITY 110
RUNTIMEPRIORITY HIGH
RUNTIMEPRIORITYTHRESHOLD 0
QUEUETIMEOUT 00:05 (minutes)
RUNTIMECAP NONE
PLANNEDCONCURRENCY AUTO
MAXCONCURRENCY

Empty

SINGLEINITIATOR

False.

CPUAFFINITYSET Empty / cannot be set
CPUAFFINITYMODE
CASCADETO

TM

Parameter Default Setting
MEMORYSIZE

5% (of the GENERAL pool's MAXMEMORYSIZE setting) + 2GB

MAXMEMORYSIZE Unlimited
MAXQUERYMEMORYSIZE Empty / cannot be set
EXECUTIONPARALLELISM AUTO
PRIORITY 105
RUNTIMEPRIORITY MEDIUM
RUNTIMEPRIORITYTHRESHOLD 60
QUEUETIMEOUT 00:05 (minutes)
RUNTIMECAP NONE
PLANNEDCONCURRENCY 7
MAXCONCURRENCY

Sets across all nodes the maximum number of concurrent execution slots available to TM pool. In databases created in Vertica releases ≥9.3, the default value is 7. In databases created in earlier versions, the default is 3.This setting specifies the maximum number of merges that can occur simultaneously on multiple threads.

SINGLEINITIATOR

True

CPUAFFINITYSET Empty / cannot be set
CPUAFFINITYMODE ANY / cannot be set
CASCADETO Empty / cannot be set