Simple query of `query_requests` takes a long time

Pieter_Sheth-Vo · November 6

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;

mosheg · November 6

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

select node_name, projection_schema, projection_name, ros_count
from projection_storage
where ros_count =(select max(ros_count) from projection_storage)
order by projection_id,node_name;


Create the table to log the max ROS counts:
CREATE TABLE public.log_counts
(
    logtime timestamptz,
    node_name varchar(128),
    projection_schema varchar(128),
    projection_name varchar(128),
    ros_count int
);

Create a stored procedure to log max ROS counts to the table:
CREATE OR REPLACE PROCEDURE log_max_ros_count()
LANGUAGE PLvSQL
AS $$
BEGIN
    PERFORM insert into public.log_counts 
                   select now(), 
                          node_name, 
                          projection_schema, 
                          projection_name, 
                          ros_count 
                   from   projection_storage
                   where ros_count = (select max(ros_count) from projection_storage)
                   order by projection_id, node_name;
END;
$$;

Create the schedule for a Cron job every day at midnight:
CREATE SCHEDULE daily_midnight USING CRON '0 0 * * *';

Create the trigger with the stored procedure and schedule:
CREATE TRIGGER Max_ROS_log_trigger ON SCHEDULE daily_midnight EXECUTE PROCEDURE log_max_ros_count() AS DEFINER;

Simple query of `query_requests` takes a long time

Answers