Using Queries to Monitor Resource Pool Size and Usage

You can use the Linux top command to determine the overall CPU usage and I/O wait time across the system. However, because of file system caching, the resident memory size indicated by top is not a good indicator of actual memory use or available reserve.

Instead, Vertica provides several monitoring tables that provide detailed information about resource pools, their current memory usage, resources requested and acquired by various requests, and the state of the queues.

The RESOURCE_POOLS table lets you view various resource pools defined in the system (both internal and user-defined), and the RESOURCE_POOL_STATUS table lets you view the current state of the resource pools.

Examples

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 Only User-Defined Resource Pools

To see only the user-defined resource pools, you can limit your query to return records where IS_INTERNAL is false.

Note: The user-defined pools shown in these examples also appear in subsequent sections related to Workload Management.

This example shows how to find information on user-defined resource pools:

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

 

Viewing the Status of All Resource Pools

The following example shows how to access the V_MONITOR.RESOURCE_POOL_STATUS table to return the current state of all resource pools on node0001:

=>\x
Expanded display is on
=> SELECT pool_name, memory_size_kb, memory_size_actual_kb, memory_inuse_kb,
   general_memory_borrowed_kb,running_query_count 
   FROM V_MONITOR.RESOURCE_POOL_STATUS where node_name ilike '%node0001';

-[ RECORD 1 ]--------------+---------
pool_name                  | general
memory_size_kb             | 2983177
memory_size_actual_kb      | 2983177
memory_inuse_kb            | 0
general_memory_borrowed_kb | 0
running_query_count        | 0
-[ RECORD 2 ]--------------+---------
pool_name                  | sysquery
memory_size_kb             | 65536
memory_size_actual_kb      | 65536
memory_inuse_kb            | 0
general_memory_borrowed_kb | 0
running_query_count        | 0
-[ RECORD 3 ]--------------+---------
pool_name                  | sysdata
memory_size_kb             | 102400
memory_size_actual_kb      | 102400
memory_inuse_kb            | 4096
general_memory_borrowed_kb | 0
running_query_count        | 0
-[ RECORD 4 ]--------------+---------
pool_name                  | wosdata
memory_size_kb             | 0
memory_size_actual_kb      | 0
memory_inuse_kb            | 0
general_memory_borrowed_kb | 0
running_query_count        | 0
-[ RECORD 5 ]--------------+---------
pool_name                  | tm
memory_size_kb             | 204800
memory_size_actual_kb      | 204800
memory_inuse_kb            | 0
general_memory_borrowed_kb | 0
running_query_count        | 0
-[ RECORD 6 ]--------------+---------
pool_name                  | refresh
memory_size_kb             | 0
memory_size_actual_kb      | 0
memory_inuse_kb            | 0
general_memory_borrowed_kb | 0
running_query_count        | 0
-[ RECORD 7 ]--------------+---------
pool_name                  | recovery
memory_size_kb             | 0
memory_size_actual_kb      | 0
memory_inuse_kb            | 0
general_memory_borrowed_kb | 0
running_query_count        | 0
-[ RECORD 8 ]--------------+---------
pool_name                  | dbd
memory_size_kb             | 0
memory_size_actual_kb      | 0
memory_inuse_kb            | 0
general_memory_borrowed_kb | 0
running_query_count        | 0
-[ RECORD 9 ]--------------+---------
pool_name                  | jvm
memory_size_kb             | 0
memory_size_actual_kb      | 0
memory_inuse_kb            | 0
general_memory_borrowed_kb | 0
running_query_count        | 0

Viewing Query Resource Acquisitions

The following example displays all resources granted to the queries that are currently running. The information shown is stored in the 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 ]---------+------------------------------
...

 

See Also