Monitor Resource Pool Cascading

alisalem

Hi,

We are using Vertica 8.0.1, and I would like to know if there is a way to monitor queries that get cascaded from pool X to pool Y. I currently have the a setup for two resource pools, pool X with a RUNTIME CAP of 3 seconds, which cascades to Pool Y. However, I am unable to see any log for queries that got cascaded to pool Y when they reach the RUNTIME CAP limit. I am pasting the configurations for both pool below, help is appreciated, thank you.

pool_id name is_internal memorysize maxmemorysize executionparallelism priority runtimepriority runtimeprioritythreshold queuetimeout plannedconcurrency maxconcurrency runtimecap singleinitiator cpuaffinityset cpuaffinitymode cascadeto
45,036,003,939,602,884 pool_x false 45G 45G 16 10 MEDIUM 10 00:16:40 20 96 00:00:03 false ANY mp3
54,043,196,336,280,420 pool_y false 30G 30G 16 10 MEDIUM 10 00:16:40 10 10 00:05:00 false ANY [NULL]

Thank you

Jim_Knicely

Hi,

Check the RESOURCE_POOL_MOVE system table:

Example:

dbadmin=> create resource pool bigger;
CREATE RESOURCE POOL

dbadmin=> create resource pool tiny runtimecap '1 second' cascade to bigger;
CREATE RESOURCE POOL

dbadmin=> grant usage on resource pool tiny to jim;
GRANT PRIVILEGE

dbadmin=> alter user jim resource pool tiny;
ALTER USER

dbadmin=> grant all on schema public to jim;
GRANT PRIVILEGE

dbadmin=> \c - jim
You are now connected as user "jim".

dbadmin=> \timing
Timing is on.

dbadmin=> create table big as select randomint(1000) from vs_columns c1 cross join vs_columns c2 cross join vs_columns c3 limit 100000000;
CREATE TABLE
Time: First fetch (0 rows): 24659.765 ms. All rows formatted: 24659.789 ms

dbadmin=> select move_timestamp, user_name, source_pool_name, target_pool_name, move_cause from resource_pool_move where session_id = current_session();
        move_timestamp        | user_name | source_pool_name | target_pool_name |     move_cause
------------------------------+-----------+------------------+------------------+---------------------
 2018-02-20 04:13:08.94434-05 | jim       | tiny             | bigger           | RunTimeCap Exceeded
(1 row)

Time: First fetch (1 row): 10.818 ms. All rows formatted: 10.894 ms