Calculate Request Queue Length

Jim_Knicely

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!

chaima

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_Knicely

Hi @chaima ,

The RESOURCE_QUEUES system table provides information (i.e. memory requested) about requests pending for various resource pools.

See:
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/SystemTables/MONITOR/RESOURCE_QUEUES.htm

https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/AdministratorsGuide/ManageWorkloads/ResourceManagerImpactOnQueryExecution.htm

scottpedersoli

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.

chaima

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..

emoreno

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

chaima

@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

emoreno

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

chaima

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