Monitoring Resource Pool Cascade Events: Quick Tip

Posted September 4, 2018 by Jim Knicely, Vertica Principal Solution Architect

Shot of two businesswomen using a digital tablet together during a collaboration at work
You can define secondary resource pools to which running queries can cascade if they exceed the initial pool’s RUNTIMECAP.

The RESOURCE_POOL_MOVE System Table displays the cascade event information on each node. There you can find helpful information like the source and target pools and why the cascading event occurred!

Example: dbadmin=> CREATE RESOURCE POOL pool_long RUNTIMECAP '20 SECONDS'; CREATE RESOURCE POOL dbadmin=> CREATE RESOURCE POOL pool_medium RUNTIMECAP '5 SECONDS' CASCADE TO pool_long; CREATE RESOURCE POOL dbadmin=> CREATE RESOURCE POOL pool_fast RUNTIMECAP '2 SECONDS' CASCADE TO pool_medium; CREATE RESOURCE POOL dbadmin=> CREATE USER rp_cascade_test; CREATE USER dbadmin=> GRANT USAGE ON RESOURCE POOL pool_fast TO rp_cascade_test; GRANT PRIVILEGE dbadmin=> ALTER USER rp_cascade_test RESOURCE POOL pool_fast; ALTER USER dbadmin=> GRANT USAGE ON SCHEMA public TO rp_cascade_test; GRANT PRIVILEGE dbadmin=> GRANT SELECT ON public.big_table_of_ints2 TO rp_cascade_test; GRANT PRIVILEGE dbadmin=> \q [dbadmin@s18384357 ~]$ vsql -U rp_cascade_test Welcome to vsql, the Vertica Analytic Database interactive terminal. Type: \h or \? for help with vsql commands \g or terminate with semicolon to execute query \q to quit rp_cascade_test=> SHOW resource_pool; name | setting ---------------+----------- resource_pool | pool_fast (1 row) rp_cascade_test=> \d public.big_table_of_ints2; List of Fields by Tables Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key --------+--------------------+--------+------+------+---------+----------+-------------+------------- public | big_table_of_ints2 | c | int | 8 | | f | f | (1 row) rp_cascade_test=> SELECT current_trans_id(); current_trans_id ------------------- 45035996273909381 (1 row) rp_cascade_test=> \timing on Timing is on. rp_cascade_test=> SELECT MAX(a.c) FROM big_table_of_ints2 a CROSS JOIN (SELECT c FROM big_table_of_ints2 LIMIT 20) b; MAX ------- 97658 (1 row) Time: First fetch (1 row): 11024.772 ms. All rows formatted: 11024.820 ms rp_cascade_test=> SELECT move_timestamp, source_pool_name, target_pool_name, move_cause, source_cap, target_cap, success, result_reason rp_cascade_test-> FROM resource_pool_move rp_cascade_test-> WHERE transaction_id = 45035996273909381 rp_cascade_test-> ORDER BY move_timestamp; move_timestamp | source_pool_name | target_pool_name | move_cause | source_cap | target_cap | success | result_reason -------------------------------+------------------+------------------+---------------------+------------+------------+---------+----------------------------------------------- 2018-09-04 09:15:59.160713-04 | pool_fast | pool_medium | RunTimeCap Exceeded | 2000000 | 5000000 | t | Statement successfully moved to target pool. 2018-09-04 09:16:02.180322-04 | pool_medium | pool_long | RunTimeCap Exceeded | 5000000 | 20000000 | t | Statement successfully moved to target pool. (2 rows) Time: First fetch (2 rows): 14.359 ms. All rows formatted: 14.425 ms) Helpful link:

https://my.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/SystemTables/MONITOR/RESOURCE_POOL_MOVE.htm

Have fun!