
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/latest/HTML/index.htm#Authoring/SQLReferenceManual/SystemTables/MONITOR/RESOURCE_ACQUISITIONS.htm
Have fun!