RESOURCE_POOL_STATUS

Provides current state of built-in and user-defined resource pools on each node.

Provides current state of built-in and user-defined resource pools on each node. Information includes:

  • Current memory usage

  • Resources requested and acquired by various requests

  • Number of queries executing

For general information about resource pools, see Resource pool architecture.

Column Name Data Type Description
NODE_NAME VARCHAR Name of the node for which information is provided.
POOL_OID INTEGER Unique numeric ID that identifies the pool and is assigned by the Vertica catalog.
POOL_NAME VARCHAR Name of the resource pool.
IS_INTERNAL BOOLEAN Denotes whether a pool is built-in.
MEMORY_SIZE_KB INTEGER Value of MEMORYSIZE setting of the pool in kilobytes.
MEMORY_SIZE_ACTUAL_KB INTEGER

Current amount of memory, in kilobytes, allocated to the pool by the resource manager. The actual size can be less than specified in the DDL, if both the following conditions exist:

  • The pool has been recently altered in a running system.

  • The request to shuffle memory is pending.

MEMORY_INUSE_KB INTEGER Amount of memory, in kilobytes, acquired by requests running against this pool.
GENERAL_MEMORY_BORROWED_KB INTEGER Amount of memory, in kilobytes, borrowed from the GENERAL pool by requests running against this pool. The sum of MEMORY_INUSE_KB and GENERAL_MEMORY_BORROWED_KB should be less than MAX_MEMORY_SIZE_KB.
QUEUEING_THRESHOLD_KB INTEGER Calculated as MAX_MEMORY_SIZE_KB * 0.95. When the amount of memory used by all requests against this resource pool exceeds the QUEUEING_THRESHOLD_KB, new requests against the pool are queued until memory becomes available.
MAX_MEMORY_SIZE_KB INTEGER

Value, in kilobytes, of the MAXMEMORYSIZE parameter as defined for the pool. After this threshold is reached, new requests against this pool are rejected or queued until memory becomes available.

MAX_QUERY_MEMORY_SIZE_KB INTEGER Value, in kilobytes, of the MAXQUERYMEMORYSIZE parameter as defined for the pool. The resource pool limits this amount of memory to all queries that execute in it.
RUNNING_QUERY_COUNT INTEGER Number of queries currently executing in this pool.
PLANNED_CONCURRENCY INTEGER Value of PLANNEDCONCURRENCY parameter as defined for the pool.
MAX_CONCURRENCY INTEGER Value of MAXCONCURRENCY parameter as defined for the pool.
IS_STANDALONE BOOLEAN If the pool is configured to have MEMORYSIZE equal to MAXMEMORYSIZE, the pool is considered standalone because it does not borrow any memory from the General pool.
QUEUE_TIMEOUT INTERVAL The interval that the request waits for resources to become available before being rejected. If you set this value to NONE, Vertica displays it as NULL.
QUEUE_TIMEOUT_IN_SECONDS INTEGER Value of QUEUETIMEOUT parameter as defined for the pool. If QUEUETIMEOUT is set to NONE, Vertica displays this value as NULL.
EXECUTION_PARALLELISM INTEGER Limits the number of threads used to process any single query issued in this resource pool.
PRIORITY INTEGER

Value of PRIORITY parameter as defined for the pool.

When set to HOLD, Vertica sets a pool's priority to -999 so the query remains queued until QUEUETIMEOUT is reached.

RUNTIMECAP_IN_SECONDS INTEGER Defined for this pool by parameter RUNTIMECAP, specifies in seconds the maximum time a query in the pool can execute. If a query exceeds this setting, it tries to cascade to a secondary pool.
RUNTIME_PRIORITY VARCHAR Defined for this pool by parameter RUNTIMEPRIORITY, determines how the resource manager should prioritize dedication of run-time resources (CPU, I/O bandwidth) to queries already running in this resource pool.
RUNTIME_PRIORITY_THRESHOLD INTEGER Defined for this pool by parameter RUNTIMEPRIORITYTHRESHOLD, specifies in seconds a time limit in which a query must finish before the resource manager assigns to it the resource pool's RUNTIME_PRIORITY setting.
SINGLE_INITIATOR BOOLEAN Set for backward compatibility.
QUERY_BUDGET_KB INTEGER

The current amount of memory that queries are tuned to use. The calculation that Vertica uses to determine this value is described inQuery budgeting.

For a detailed example of query budget calculations, see Do You Need to Put Your Query on a Budget? in the Vertica User Community.

CPU_AFFINITY_SET VARCHAR

The set of CPUs on which queries associated with this pool are executed. Can be:

  • A percentage of CPUs on the system

  • A zero-based list of CPUs (a four-CPU system c of CPUs 0, 1, 2, and 3).

CPU_AFFINITY_MASK VARCHAR The bit mask of CPUs available for use in this pool, read from right to left. See Examples below.
CPU_AFFINITY_MODE VARCHAR

The mode for the CPU affinity, one of the following:

  • ANY

  • EXCLUSIVE

  • SHARED

Examples

The following query returns bit masks that show CPU assignments for three user-defined resource pools. Resource pool bigqueries runs queries on CPU 0, ceo_pool on CPU 1, and testrp on CPUs 0 and 1:

 => SELECT pool_name, node_name, cpu_affinity_set, cpu_affinity_mode,
      TO_BITSTRING(CPU_AFFINITY_MASK::VARBINARY) "CPU Affinity Mask"
      FROM resource_pool_status WHERE IS_INTERNAL = 'false' order by pool_name, node_name;
 pool_name  |    node_name     | cpu_affinity_set | cpu_affinity_mode | CPU Affinity Mask
------------+------------------+------------------+-------------------+-------------------
 bigqueries | v_vmart_node0001 | 0                | SHARED            | 00110001
 bigqueries | v_vmart_node0002 | 0                | SHARED            | 00110001
 bigqueries | v_vmart_node0003 | 0                | SHARED            | 00110001
 ceo_pool   | v_vmart_node0001 | 1                | SHARED            | 00110010
 ceo_pool   | v_vmart_node0002 | 1                | SHARED            | 00110010
 ceo_pool   | v_vmart_node0003 | 1                | SHARED            | 00110010
 testrp     | v_vmart_node0001 | 0-1              | SHARED            | 00110011
 testrp     | v_vmart_node0002 | 0-1              | SHARED            | 00110011
 testrp     | v_vmart_node0003 | 0-1              | SHARED            | 00110011
(9 rows)

See also