Resource Pool

Vertica Customer

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

  • Vertica Employee Administrator

    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
    ;
  • Vertica Customer
    edited July 2021

    Thanks @mosheg

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

  • Vertica Employee Administrator

    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
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file
You can use Markdown in your post.