Re: impact of auto explain on overall performance

От: Adrien NAYRAT
Тема: Re: impact of auto explain on overall performance
Дата: ,
Msg-id: 335b5599-2a6d-4d3e-a801-794642ec16d7@anayrat.info
(см: обсуждение, исходный текст)
Ответ на: Re: impact of auto explain on overall performance  (Justin Pryzby)
Список: pgsql-performance

Скрыть дерево обсуждения

impact of auto explain on overall performance  (Stephan Schmidt, )
 Re: impact of auto explain on overall performance  (Justin Pryzby, )
  Re: impact of auto explain on overall performance  (Adrien NAYRAT, )
 Re: impact of auto explain on overall performance  (Jeff Janes, )
 Re: impact of auto explain on overall performance  (Jeremy Schneider, )

On 3/14/19 9:23 AM, Justin Pryzby wrote:
> On Thu, Mar 14, 2019 at 07:29:17AM +0000, Stephan Schmidt wrote:
>> i’m currently working on a high Performance Database and want to make sure that whenever there are slow queries
duringregular operations i’ve got all Information about the query in my logs. So auto_explain come to mind, but the
documentationexplicitly states that it Comes at a cost. My Question is, how big is the latency added by auto_explain in
percentageor ms ?
 
> 
> https://www.postgresql.org/docs/current/auto-explain.html
> |log_analyze
> ...
> |When this parameter is on, per-plan-node timing occurs for all statements executed, whether or not they run long
enoughto actually get logged. This can have an extremely negative impact on performance. Turning off
auto_explain.log_timingameliorates the performance cost, at the price of obtaining less information.
 
> 
> |auto_explain.log_timing (boolean)
> |auto_explain.log_timing controls whether per-node timing information is printed when an execution plan is logged;
it'sequivalent to the TIMING option of EXPLAIN. The overhead of repeatedly reading the system clock can slow down
queriessignificantly on some systems, so it may be useful to set this parameter to off when only actual row counts, and
notexact times, are needed. This parameter has no effect unless auto_explain.log_analyze is enabled. This parameter is
onby default. Only superusers can change this setting.
 
> 
> I believe the cost actually varies significantly with the type of plan "node",
> with "nested loops" incurring much higher overhead.
> 
> I think you could compare using explain(analyze) vs explain(analyze,timing
> off).  While you're at it, compare without explain at all.
> 
> I suspect the overhead is inconsequential if you set log_timing=off and set
> log_min_duration such that only the slowest queries are logged.
> 
> Then, you can manually run "explain (analyze,costs on)" on any problematic
> queries to avoid interfering with production clients.
> 
> Justin
> 

You should also consider auto_explain.sample_rate: 
auto_explain.sample_rate causes auto_explain to only explain a fraction 
of the statements in each session. The default is 1, meaning explain all 
the queries. In case of nested statements, either all will be explained 
or none. Only superusers can change this setting.

This option is available since 9.6

Regards



В списке pgsql-performance по дате сообщения:

От: Gunther
Дата:
Сообщение: Re: Distributing data over "spindles" even on AWS EBS, (followup tothe work queue saga)
От: Jeremy Schneider
Дата:
Сообщение: Re: Distributing data over "spindles" even on AWS EBS, (followup tothe work queue saga)