Page 1 of 1

Reviewing EE profiling data when you didn't enable profiling

Posted: Fri Mar 21, 2014 8:58 pm
by scutter
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

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

Posted: Fri Mar 21, 2014 11:09 pm
by NorbertKrupa
Really neat tip! I thought this parameter was set to 3 seconds by default.

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

Posted: Fri Mar 21, 2014 11:34 pm
by scutter
Not in my version :-)

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

SaveDCEEProfileThresholdUS 1000000

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

Posted: Fri Mar 21, 2014 11:38 pm
by NorbertKrupa
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