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
- See the SQL Reference Manual for detailed descriptions of the monitoring tables.
- See Monitoring Resource Pools for descriptions of other ways to monitor resource usage.