Find and fix issues from Vertica query events
Bryan_H
Vertica Employee Administrator
Vertica offers tools like the Workload Analyzer in Management Console 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. Have fun!
References:
QUERY_EVENTS table: https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/QUERY_EVENTS.htm
Guidelines for improving query performance: https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AnalyzingData/Optimizations/InitialProcessForImprovingQueryPerformance.htm