The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Find and fix issues from Vertica query events

Bryan_HBryan_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

Tagged:
Sign In or Register to comment.