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

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.

Included for backwards compatibility. Do not change.

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

One of the following:

  • Enterprise Mode: 107
  • Eon Mode: 110

Change these settings only under guidance from Vertica technical support.

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.

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

In Vertica version 9.3 and later, this pool has no resources and is not used.

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
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

1G

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.

Included for backwards compatibility. Do not change.

CPUAFFINITYSET Empty / cannot be set
CPUAFFINITYMODE
CASCADETO

TM

Parameter Default Setting
MEMORYSIZE

5% (of the GENERAL pool's MAXMEMORYSIZE setting.)

You can estimate the optimal amount of RAM for the TM resource pool as follows:

GbRAM / (6 * #table-cols) > 10

where #table-cols is the number of columns in the largest database table. For example, given a 100-column table, MEMORYSIZE needs least 6GB of RAM:

6144MB / (6 * 100)  = 10.24
MAXMEMORYSIZE

Unlimited

MAXQUERYMEMORYSIZE Empty / cannot be set
EXECUTIONPARALLELISM

AUTO

PRIORITY

105

RUNTIMEPRIORITY

MEDIUM

RUNTIMEPRIORITYTHRESHOLD

60

QUEUETIMEOUT

00:05 (minutes)

RUNTIMECAP

NONE

PLANNEDCONCURRENCY

6

MAXCONCURRENCY

7

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

In databases created in Vertica 9.3 and later, this pool has a default value of 0. Instead, Vertica uses the load method DIRECT to load data directly to ROS, bypassing the WOS. You can control this behavior using the DMLTargetDirect configuration parameter.

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