Built-In Resource Pools Configuration

You can view the current and default configuration for built-in resource pools by querying 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%

    Caution: Setting this parameter to 100% generates warning of potential swapping.

  • 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

Important: In systems with a large number of cores, the default AUTO setting of PLANNEDCONCURRENCY is liable to be too low. In this case, set the parameter to the actual number of cores:

ALTER RESOURCE POOL general PLANNEDCONCURRENCY #cores;

Default: AUTO

MAXCONCURRENCY

Default: empty

Caution: Must be set ≥ 1, otherwise Vertica generates warning that system queries might be unable to execute.
SINGLEINITIATOR

Default: False.

Important: This parameter is included for backwards compatibility only. Do not change the value.
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

Important: Included for backwards compatibility, do not change.
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

Important: Included for backwards compatibility, do not change.
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.

Important: Included for backwards compatibility, do not change.
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.

Caution: Setting must resolve to ≥ 25%, otherwise Vertica generates a warning that system queries might be unable to execute.
MAXQUERYMEMORYSIZE empty / cannot be set
EXECUTIONPARALLELISM

AUTO
PRIORITY

107
RUNTIMEPRIORITY

MEDIUM
RUNTIMEPRIORITYTHRESHOLD

60
QUEUETIMEOUT

00:05 (minutes)
RUNTIMECAP

NONE
PLANNEDCONCURRENCY

AUTO
MAXCONCURRENCY

2

Note: 0 or NONE (unlimited) are invalid settings.
SINGLEINITIATOR

True.

Important: Included for backwards compatibility, do not change.
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.

Important: Included for backwards compatibility, do not change.
CPUAFFINITYSET

empty / cannot be set
CPUAFFINITYMODE ANY / cannot be set
CASCADETO

empty / cannot be set

SYSDATA

Parameter Default Setting
MEMORYSIZE

100M
MAXMEMORYSIZE

10%

Caution: Setting must resolve to ≥ 4M, otherwise Vertica generates warning that system queries might be unable to execute.

Note: MAXMEMORYSIZE cannot be changed if any of its memory is in use.
MAXQUERYMEMORYSIZE empty / cannot be set
EXECUTIONPARALLELISM

empty
PRIORITY
RUNTIMEPRIORITY
RUNTIMEPRIORITYTHRESHOLD
QUEUETIMEOUT empty / cannot be set
RUNTIMECAP
PLANNEDCONCURRENCY empty
MAXCONCURRENCY empty / cannot be set
SINGLEINITIATOR

empty

Important: Included for backwards compatibility, do not change.
CPUAFFINITYSET empty / cannot be set
CPUAFFINITYMODE
CASCADETO

SYSQUERY

Parameter Default Setting
MEMORYSIZE

64M

Caution: Setting must resolve to ≥ 20M, otherwise Vertica generates warning that system queries might be unable to execute, and diagnosing problems might be difficult.
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

Caution: Must be set ≥ 1, otherwise Vertica generates warning that system queries might be unable to execute.
SINGLEINITIATOR

False.

Important: This parameter is included for backwards compatibility only. Do not change the value.
CPUAFFINITYSET empty / cannot be set
CPUAFFINITYMODE
CASCADETO

TM

Parameter Default Setting
MEMORYSIZE

5%
MAXMEMORYSIZE

Unlimited
MAXQUERYMEMORYSIZE empty / cannot be set
EXECUTIONPARALLELISM

AUTO
PRIORITY

105
RUNTIMEPRIORITY

MEDIUM
RUNTIMEPRIORITYTHRESHOLD

60
QUEUETIMEOUT

00:05 (minutes)
RUNTIMECAP

NONE
PLANNEDCONCURRENCY

AUTO
MAXCONCURRENCY

3

Note: 0 or NONE (unlimited) are invalid settings.
SINGLEINITIATOR

True

Important: Included for backwards compatibility, do not change.
CPUAFFINITYSET

empty / cannot be set
CPUAFFINITYMODE ANY / cannot be set
CASCADETO

empty / cannot be set

WOSDATA

Parameter Default Setting
MEMORYSIZE

0%
MAXMEMORYSIZE

25% of the GENERAL pool limit or 2GB, whichever is less.

Important: This parameter can never be less than 2GB.
MAXQUERYMEMORYSIZE empty / cannot be set
EXECUTIONPARALLELISM
PRIORITY
RUNTIMEPRIORITY
RUNTIMEPRIORITYTHRESHOLD
QUEUETIMEOUT
RUNTIMECAP

NONE
PLANNEDCONCURRENCY

AUTO
MAXCONCURRENCY empty / cannot be set
SINGLEINITIATOR
CPUAFFINITYSET
CPUAFFINITYMODE
CASCADETO