Monitoring Resource Pool Cascade Events
Jim_Knicely
- Select Field - Administrator
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 out 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)
Have fun!
0