Calculate Request Queue Length

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!