Calculate Request Queue Length

Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

The RESOURCE_ACQUISITIONS system table retains information about resources (memory, open file handles, threads) acquired by each running request. Each request is uniquely identified by its transaction and statement IDs within a given session.

From this system table, you can calculate how long a request was queued in a resource pool before acquiring the resources it needed to execute from the difference between QUEUE_ENTRY_TIMESTAMP and ACQUISITION_TIMESTAMP.

Example:

I’d like to know if there were any requests in the GENERAL pool which queued longer than 1 second.

dbadmin=> SELECT node_name,
dbadmin->        request_type,
dbadmin->        transaction_id,
dbadmin->        statement_id,
dbadmin->        pool_name,
dbadmin->        (acquisition_timestamp - queue_entry_timestamp) request_queue_length
dbadmin->   FROM v_monitor.resource_acquisitions
dbadmin->  WHERE pool_name = 'general'
dbadmin->    AND (acquisition_timestamp - queue_entry_timestamp) > '1 second'
dbadmin->  ORDER BY (acquisition_timestamp - queue_entry_timestamp) DESC;
node_name | request_type | transaction_id | statement_id | pool_name | request_queue_length
-----------+--------------+----------------+--------------+-----------+----------------------
(0 rows)

There were none!

So which request in the GENERAL pool did queue the longest?

dbadmin=> SELECT node_name,
dbadmin->        request_type,
dbadmin->        transaction_id,
dbadmin->        statement_id,
dbadmin->        pool_name,
dbadmin->        (acquisition_timestamp - queue_entry_timestamp) request_queue_length
dbadmin->   FROM v_monitor.resource_acquisitions
dbadmin->  WHERE pool_name = 'general'
dbadmin->  ORDER BY (acquisition_timestamp - queue_entry_timestamp) DESC
dbadmin->  LIMIT 1;
     node_name      | request_type |  transaction_id   | statement_id | pool_name | request_queue_length
--------------------+--------------+-------------------+--------------+-----------+----------------------
v_test_db_node0001 | Reserve      | 45035996274412358 |            1 | general   | 00:00:00.000447
(1 row)

Helpful Link:
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/SystemTables/MONITOR/RESOURCE_ACQUISITIONS.htm

Have fun!

Comments

  • chaimachaima Employee, Registered User

    Many thanks for the tip Jim!
    Is there a way to know the reason for which a query was queued? Something similar to what we have with RESOURCE_REJECTIONS

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert
  • scottpedersoliscottpedersoli Registered User

    Great question chaima! Based on Jim's tip I'm now reviewing results of resource_acquisitions and do see queuing, wondering if/how i can dig deeper into root cause.

  • chaimachaima Employee, Registered User

    Many thanks @Jim_Knicely ! I was hoping to find a system table where we have the history of queued queries not the currently pending ones..

  • emorenoemoreno Employee, Registered User, VerticaExpert
    via Email
    In the resource_acquisitions table you have 2 times queue_entry_timestamp and acquisition_timestamp, however this dates are not correct if the query has joins that "AcquireAdditional". This was fixed in 9.1SP1 so not sure what version you have. If you have an older version and your queries have Joins that adquiere additional you can get the information with this query.


    select acq.node_name,
    acq.transaction_id,
    acq.statement_id,
    'RESERVE' as request_type,
    acq.pool as pool_id,
    pool_name,
    max(acq.threads) as thread_count,
    max(acq.filehandles) as open_file_handle_count,
    max(acq.memory_kb) as memory_inuse_kb,
    min(acq.start_time) as queue_entry_timestamp,
    min(acq.time) as acquisition_timestamp,
    max(rel.time) as release_timestamp ,
    case when max(rel.time) = NULL then NULL::int else datediff('millisecond', min(acq.time), max(rel.time)) end as duration_ms,
    case when max(rel.time) = NULL then true else false end as is_executing
    from v_internal.dc_resource_acquisitions acq left join v_internal.dc_resource_releases rel on
    (acq.node_name=rel.node_name and acq.transaction_id=rel.transaction_id and acq.statement_id=rel.statement_id) where acq.transaction_id != -1 and acq.request_type != 'Acquire' and acq.succeeded = true group by acq.node_name,acq.transaction_id,acq.statement_id,acq.pool,acq.pool_name;


    Thanks,
    Eugenia
  • chaimachaima Employee, Registered User

    @emoreno does this mean that prior to 9.1SP1 we can have queue_wait>0 even if it's not the case if the query requires additionnal ressources?
    Thank you

  • emorenoemoreno Employee, Registered User, VerticaExpert
    via Email
    If you use the resource_acquisition to see it, yes. If you were seeing if a query was queue based on the resource_queue, no.
    Note that the only queries that MAY need additional resources are the one with Hash Join, all other queries should be using up to the budget.

    Thanks,
    Eugenia
  • chaimachaima Employee, Registered User
    edited November 12

    I was checking resource_acquisitions and the difference between acquisition_timestamp and queue_entry_timestamp columns,
    I found the Jira for this issue
    This is very helpful, many thanks Eugenia!
    Chaima

Sign In or Register to comment.