Simple query of `query_requests` takes a long time
A simple query of the query_requests
table seems very slow takeing 8-12 seconds each time, whereas almost every other simple Vertica query we run returns far faster.
Is there a way to speed query this up? For context this is useful to monitor the status of queries that are excpected to be long-running, like large CREATE TABLE
statements.
select statement_id, session_id, node_name, user_name, request_type, LEFT(request,100), request_label, memory_acquired_mb, error_count, start_timestamp, end_timestamp, error_count from query_requests where is_executing='t' order by start_timestamp desc;
0
Answers
The time it takes depends on your Vertica version, system load, and catalog size.
In the latest Vertica versions, the catalog is smaller, and queries from the catalog are faster.
To reduce the need to query the catalog multiple times, consider creating a repository of important system tables.
From Vertica version 12.0.4 onwards, stored procedures can be scheduled to execute automatically with the privileges of the trigger definer.
You can use this to automate various tasks for logging database activity, monitoring performance, and more.
Recurring triggers run at a specified date or time and can be used, for example, to create a daily trigger that logs the status of the ROS container max count.
You can use a similar method to create a repository for query_requests
query_requests is a system view that joins multiple system and DC tables, so you might be able to speed it up by simplifying the view down to the fields you need. Have a look at the definition in vs_system_views where view_name = 'query_reqeusts'
If you just need to see whether a query is still running, you can see that in the sessions table:
select node_name,user_name,session_id,statement_start,current_statement from sessions;
Bryan, Mosheg -- Thanks! These replies are spot on and very helpful. Querying
sessions
directly returns instantly.