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

Resource Pool

Hi Team,

I'm trying to find if there is way we can get the details of Resource pool usage as below?

1) Number of queries run each hour in the pool over a 7 day span
2) Min, Max and average memory usage each of those hours per pool
3) Min, Max and average query duration each hour per pool

Thanks,
Rocky

Answers

  • moshegmosheg Employee

    Try this:

    select
        time_slice(qr.start_timestamp, 1, 'hour','start') ,
        upper(regexp_substr(qr.request, '\w+')::char(8)) as qtype,
        qr.request_type ,
        ra.pool_name ,
        count(*),
        min(qr.request_duration_ms) as min_ms,
        max(qr.request_duration_ms) as max_ms,
        avg(qr.request_duration_ms)::int as avg_ms,
        min(qr.memory_acquired_mb) as min_mb,
        max(qr.memory_acquired_mb) as max_mb,
        avg(qr.memory_acquired_mb)::int as avg_mb
    from
        v_monitor.query_requests qr
        left outer join v_monitor.resource_acquisitions ra
        on qr.statement_id = ra.statement_id and
           qr.transaction_id = ra.transaction_id
    where
        qr.is_executing is false and
        (sysdate() - qr.start_timestamp) < '7 days'
    group by
        1, 2, 3, 4
    order by
        1, 2, 3
    ;
    
  • edited July 29

    Thanks @mosheg

    Most of the pool_name I a get NULL value. Any ideas?

  • moshegmosheg Employee

    To monitor only "QUERY" request types try this:

    select
        time_slice(qr.start_timestamp, 1, 'hour','start') ,
        upper(regexp_substr(qr.request, '\w+')::char(8)) as qtype,
        qr.request_type ,
        ra.pool_name ,
        count(*),
        min(qr.request_duration_ms) as min_ms,
        max(qr.request_duration_ms) as max_ms,
        avg(qr.request_duration_ms)::int as avg_ms,
        min(qr.memory_acquired_mb) as min_mb,
        max(qr.memory_acquired_mb) as max_mb,
        avg(qr.memory_acquired_mb)::int as avg_mb
    from
        v_monitor.query_requests qr
        left outer join v_monitor.resource_acquisitions ra
        on qr.statement_id = ra.statement_id and
           qr.transaction_id = ra.transaction_id
    where
        qr.is_executing is false and
        qr.request_type = 'QUERY' and
        (sysdate() - qr.start_timestamp) < '7 days'
    group by
        1, 2, 3, 4
    order by
        1, 2, 3
    ;
    

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.