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}' }
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}' } |
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 }
|
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:
In systems with a large number of cores, the default AUTO setting of 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 MAXMEMORYSIZE { 'integer%' | 'integer{K|M|G|T}' | NONE }
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:
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, 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 |