Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Monitor Resource Pool Cascading

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 Administrator

    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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.