Calculate Request Queue Length: Quick Tip

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

Have fun!