Reviewing EE profiling data when you didn't enable profiling

Moderator: NorbertKrupa

Post Reply
scutter
Master
Master
Posts: 302
Joined: Tue Aug 07, 2012 2:15 am

Reviewing EE profiling data when you didn't enable profiling

Post by scutter » Fri Mar 21, 2014 8:58 pm

Do you want to review execution engine level profiling data but didn't turn on profiling, and didn't PROFILE the query? Or you don't want to wait an hour to PROFILE a long-running query?

Vertica has a configuration parameter QueryEEProfiling that lets you turn on execution_engine_profiles profiling for every single query. This can be useful, but it's more profiling data than I typically want. There's another way to get the data.

There's an undocumented configuration parameter SaveDCEEProfileThresholdUS. I actually mentioned this during my presentation at last August's Big Data Conference. Well, technically Ben mentioned it while I sat there quietly with my severe laryngitis... By default SaveDCEEProfileThresholdUS is set to 1000000 or 1 second. This means that by default, Vertica will keep profiling data for any query that runs for at least one second. Usually I don't have a need to review profiling data for one-second queries, so I will commonly increase this to 30 or 60 seconds depending on the customer's use case.

The resulting profiling data is saved in the dc_execution_engine_profiles table, up to the retention policy for that table. You will likely want to increase the retention of that table using get_data_collector_policy()/set_data_collector_policy() to have a meaningful window of profiling data saved. You should also be able to query this data via EXECUTION_ENGINE_PROFILES since that's a view on top of vs_execution_engine_profiles / dc_execution_engine_profiles.

Another table to be aware of is dc_explain_plans, which is used by QUERY_PLAN_PROFILES. You can view the actual explain plan that was used during any query's execution, up to the dc table's retention policy.

This works great for me, with the one caveat that for some reason not all of the queries that should have profiling data saved end up having it saved. But 99% is good enough for me. It's far easier to review existing data for a one-hour query than to have to rerun it again.

Enjoy,

--Sharon
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Reviewing EE profiling data when you didn't enable profi

Post by NorbertKrupa » Fri Mar 21, 2014 11:09 pm

Really neat tip! I thought this parameter was set to 3 seconds by default.
Checkout vertica.tips for more Vertica resources.

scutter
Master
Master
Posts: 302
Joined: Tue Aug 07, 2012 2:15 am

Re: Reviewing EE profiling data when you didn't enable profi

Post by scutter » Fri Mar 21, 2014 11:34 pm

Not in my version :-)

select parameter_name, default_value from configuration_parameters where parameter_name = 'SaveDCEEProfileThresholdUS';

SaveDCEEProfileThresholdUS 1000000
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Reviewing EE profiling data when you didn't enable profi

Post by NorbertKrupa » Fri Mar 21, 2014 11:38 pm

You're right! I was told it was 3 seconds during the performance tuning class.

Code: Select all

       parameter_name       | default_value
----------------------------+---------------
 SessionProfilingAgeOut     | 30
 SaveDCEEProfileThresholdUS | 1000000
Checkout vertica.tips for more Vertica resources.

Post Reply

Return to “Vertica Tips, Lessons and Examples”