Monitor Resource Pool Cascading
alisalem
Vertica Customer ✭
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
Comments
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