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

SYSQUERY

Parameter Default Setting
MEMORYSIZE

1G

Setting must resolve to ≥ 20M, otherwise Vertica generates a 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 a 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) + 2GB

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

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.

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