Querying Resource Pool Data

Vertica provides several tables that provide detailed information about resource pools:

You can also use the Management Console to obtain run-time data on resource pool usage. For details, see Monitoring Resource Pools in Using Management Console.

The Linux top command returns data on overall CPU usage and I/O wait time across the system. Because of file system caching, the resident memory size returned by top is not the best indicator of actual memory use or available reserves.

Querying Resource Pool Settings

This example shows how to find the various resource pools defined in the system.

=> SELECT name, memorysize, maxmemorysize FROM V_CATALOG.RESOURCE_POOLS;

   name   | memorysize | maxmemorysize
----------+------------+---------------
 general  |            | Special: 95%
 sysquery | 64M        |
 sysdata  | 100M       | 10%
 wosdata  | 0%         | 25%
 tm       | 200M       |
 refresh  | 0%         |
 recovery | 0%         |  
 dbd      | 0%         |
 jvm      | 0%         | 10%
(9 rows)

Viewing Resource Pool Status

The following example queries V_MONITOR.RESOURCE_POOL_STATUS for memory size data:

=> SELECT pool_name poolName, 
    node_name nodeName, 
    max_query_memory_size_kb maxQueryMemSizeKb, 
    max_memory_size_kb maxMemSizeKb, 
    memory_size_actual_kb memSizeActualKb 
    FROM resource_pool_status WHERE pool_name='ceo_pool';
 poolName |     nodeName     | maxQueryMemSizeKb | maxMemSizeKb | memSizeActualKb
----------+------------------+-------------------+--------------+-----------------
 ceo_pool | v_vmart_node0001 |          12179388 |     13532654 |         1843200
 ceo_pool | v_vmart_node0002 |          12191191 |     13545768 |         1843200
 ceo_pool | v_vmart_node0003 |          12191170 |     13545745 |         1843200
(3 rows)

Viewing Query Resource Acquisitions

The following example displays all resources granted to the queries that are currently running. The information shown is stored in system table V_MONITOR.RESOURCE_ACQUISITIONS table. You can see that the query execution used 708504 KB of memory from the GENERAL pool.

=> SELECT pool_name, thread_count, open_file_handle_count, memory_inuse_kb,
   queue_entry_timestamp, acquisition_timestamp 
   FROM V_MONITOR.RESOURCE_ACQUISITIONS WHERE node_name ILIKE '%node0001';

-[ RECORD 1 ]----------+------------------------------
pool_name              | sysquery
thread_count           | 4
open_file_handle_count | 0
memory_inuse_kb        | 4103
queue_entry_timestamp  | 2013-12-05 07:07:08.815362-05
acquisition_timestamp  | 2013-12-05 07:07:08.815367-05
-[ RECORD 2 ]----------+------------------------------
...
-[ RECORD 8 ]----------+------------------------------
pool_name              | general
thread_count           | 12
open_file_handle_count | 18
memory_inuse_kb        | 708504
queue_entry_timestamp  | 2013-12-04 12:55:38.566614-05
acquisition_timestamp  | 2013-12-04 12:55:38.566623-05
-[ RECORD 9 ]----------+------------------------------
...

You can determine how long a query waits in the queue before it can run. To do so, you obtain the difference between the acquisition_timestamp and the queue_entry_timestamp using a query as this example shows:

=> SELECT pool_name, queue_entry_timestamp, acquisition_timestamp, 
    (acquisition_timestamp-queue_entry_timestamp) AS 'queue wait' 
   FROM V_MONITOR.RESOURCE_ACQUISITIONS WHERE node_name ILIKE '%node0001';

-[ RECORD 1 ]---------+------------------------------
pool_name             | sysquery
queue_entry_timestamp | 2013-12-05 07:07:08.815362-05
acquisition_timestamp | 2013-12-05 07:07:08.815367-05
queue wait            | 00:00:00.000005
-[ RECORD 2 ]---------+------------------------------
pool_name             | sysquery
queue_entry_timestamp | 2013-12-05 07:07:14.714412-05
acquisition_timestamp | 2013-12-05 07:07:14.714417-05
queue wait            | 00:00:00.000005
-[ RECORD 3 ]---------+------------------------------
pool_name             | sysquery
queue_entry_timestamp | 2013-12-05 07:09:57.238521-05
acquisition_timestamp | 2013-12-05 07:09:57.281708-05
queue wait            | 00:00:00.043187
-[ RECORD 4 ]---------+------------------------------
...

Querying User-Defined Resource Pools

The Boolean column IS_INTERNAL in system tables RESOURCE_POOLS and RESOURCE_POOL_STATUS lets you get data on user-defined resource pools only. For example:

=> SELECT name, memorysize, maxmemorysize, priority, maxconcurrency
    FROM V_CATALOG.RESOURCE_POOLS where is_internal ='f';

     name     | memorysize | maxmemorysize | priority | maxconcurrency 
--------------+------------+---------------+----------+----------------
 load_pool    | 0%         |               |       10 |               
 ceo_pool     | 250M       |               |       10 |               
 ad hoc_pool  | 200M       | 200M          |        0 |               
 billing_pool | 0%         |               |        0 |              3
 web_pool     | 25M        |               |       10 |              5
 batch_pool   | 150M       | 150M          |        0 |             10
 dept1_pool   | 0%         |               |        5 |               
 dept2_pool   | 0%         |               |        8 |               
(8 rows)