Monitor Resource Pool Cascading

alisalemalisalem Registered User

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

  • Jim_KnicelyJim_Knicely Employee, Registered User, VerticaExpert

    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
    

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file