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. The sections below provide this information, and also indicate which built-in pool parameters can be modified with ALTER RESOURCE POOL:

GENERAL
BLOBDATA
DBD
JVM
METADATA
RECOVERY
REFRESH
SYSDATA
SYSQUERY
TM
WOSDATA

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% 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 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 Must be set ≥ 1, otherwise Vertica generates warning that system queries might be unable to execute.
SINGLEINITIATOR	Default: False. 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 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 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. 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. 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 0 or NONE (unlimited) are invalid settings.
SINGLEINITIATOR	True. 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. 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% Setting must resolve to ≥ 4M, otherwise Vertica generates warning that system queries might be unable to execute. 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	Empty / cannot be set
PLANNEDCONCURRENCY	Empty
MAXCONCURRENCY	Empty / cannot be set
SINGLEINITIATOR	Empty Included for backwards compatibility, do not change.
CPUAFFINITYSET	Empty / cannot be set
CPUAFFINITYMODE
CASCADETO

SYSQUERY

Parameter	Default Setting
MEMORYSIZE	64M 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 Must be set ≥ 1, otherwise Vertica generates warning that system queries might be unable to execute.
SINGLEINITIATOR	False. 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 0 or NONE (unlimited) are invalid settings.
SINGLEINITIATOR	True Included for backwards compatibility, do not change.
CPUAFFINITYSET	Empty / cannot be set
CPUAFFINITYMODE	ANY / cannot be set
CASCADETO	Empty / cannot be set

WOSDATA

Eon Mode does not use the WOS. When creating or upgrading an Eon Mode database, Vertica sets the default value for MAXMEMORYSIZE and MEMORYSIZE for the WOSDATA resource pool to 0.

Parameter	Default Setting
MEMORYSIZE	0% Eon Mode does not use the WOS. When creating or upgrading an Eon Mode database, Vertica sets the default value for MAXMEMORYSIZE and MEMORYSIZE for the WOSDATA resource pool to 0.
`MAXMEMORYSIZE`	25% of the GENERAL pool limit or 2GB, whichever is less. Eon Mode does not use the WOS. When creating or upgrading an Eon Mode database, Vertica sets the default value for MAXMEMORYSIZE and MEMORYSIZE for the WOSDATA resource pool to 0.
MAXQUERYMEMORYSIZE	Empty / cannot be set
EXECUTIONPARALLELISM
PRIORITY
RUNTIMEPRIORITY
RUNTIMEPRIORITYTHRESHOLD
QUEUETIMEOUT
RUNTIMECAP	NONE
PLANNEDCONCURRENCY	AUTO
MAXCONCURRENCY	Empty / cannot be set
SINGLEINITIATOR
CPUAFFINITYSET
CPUAFFINITYMODE
CASCADETO