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!
About the Author
James Knicely
Vertica Field Chief Technologist
I've had the privilege of working with many database technologies in my career. But after being introduced to Vertica in May of 2011 as a client, I was hooked on the new technology after witnessing a query run in milliseconds that had previously ran for hours on the legacy database we had in place. It was then that I knew I wanted to eventually join the Vertica team, and 4 years later I did! I am currently a Vertica evangelist and am ready to help you get on board! Please feel free to reach out to me with any questions you have about Vertica and make sure to follow my Vertica Quick Tips!
