Find and Fix Issues from Vertica Query Events

Posted August 5, 2019 by Bryan Herger, Vertica Big Data Solution Architect at Micro Focus

Vintage businessman concept wearing futuristic helmet at office
Vertica offers tools like the Workload Analyzer in Management Console (MC) to tune up a Vertica Cluster, but there’s a simple way to find and fix issues that Vertica observes and records if you aren’t using MC.

The query_events table captures optimization issues and suggests fixes. Let’s take a look at my demo cluster, checking event description and suggested action grouped and sorted by event count: select event_description, suggested_action, count(*) from query_events where suggested_action is not null and suggested_action <> 'Informational; No user action is necessary' group by 1,2 order by 3 desc; event_description suggested_action count The optimizer encountered a predicate on a column for which it does not have a histogram analyze_statistics('public.dump1090new.generated'); 2762 WOS Full; spilling to a new ROS container Consider DIRECT load, more aggressive Moveout, or a larger WOS 2096 The optimizer ran a query using auto-projections Consider creating projections or run database designer on table public.dump1090new 1950 These suggestions can make a big difference in performance, so I’ll be adding a DIRECT hint to my streaming load, and running DataBase Designer as suggested.

Helpful Links:

QUERY_EVENTS table: Guidelines for improving query performance:

Have fun!