Find and Fix Issues from Vertica Query Events

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: https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/QUERY_EVENTS.htm Guidelines for improving query performance: https://www.vertica.com/docs/latest/HTML/Content/Authoring/AnalyzingData/Optimizations/InitialProcessForImprovingQueryPerformance.htm

Have fun!