Re: Common slow query reasons - help with a special log

2011/12/10 Tomas Vondra <tv(at)fuzzy(dot)cz>

> On 10.12.2011 23:40, Daniel Cristian Cruz wrote:
> > At work we have a 24 cores server, with a load average around 2.5.
>
> A single query is processes by a single CPU, so even if the system is
> not busy a single query may hit CPU bottleneck. The real issue is the
> instrumentation overhead - timing etc. On some systems (with slow
> gettimeofday) this may be a significant problem as the query hits the
> CPU boundary sooner.
>

Yes, I forgot it will run on the same PID. Since analyze will cause all
queries to slow down, maybe the 24 cores could became overloaded.

>
> > I don't know yet if a system which use some unused CPU to minimize the
> > load of a bad query identified early is bad or worse.
>
> Not really, due to the "single query / single CPU" rule.

I guess it will be a nice tool to run in the validation server.

> > Indeed, I don't know if my boss would let me test this at production
> > too, but it could be good to know how things work in "auto-pilot" mode.
>
> What I was pointing out is that you probably should not enable loggin
> "explain analyze" output by "auto_explain.log_analyze = true". There are
> three levels of detail:
>
> 1) basic, just log_min_duration_statement
>
> 2) auto_explain, without 'analyze' - just explain plain
>
> 3) auto_explain, with 'analyze' - explain plan with actual values
>
> Levels (1) and (2) are quite safe (unless the minimum execution time is
> too low).
>

I would start with 5 seconds.

Reading the manual again and I saw that enabling analyze, it analyze all
queries, even the ones that wasn't 5 second slower. And understood that
there is no way to disable for slower queries, since there is no way to
know it before it ends...

I read Bruce blog about some features going to multi-core. Could explain
analyze go multi-core too?

Another thing I saw is that I almost never look at times in explain
analyze. I always look for rows divergence and methods used for scan and
joins when looking for something to get better performance.

I had the nasty idea of putting a // before de gettimeofday in the code for
explain analyze (I guess it could be very more complicated than this).

Sure, its ugly, but I think it could be an option for an explain analyze
"with no time", and in concept, it's what I'm looking for.

--
Daniel Cristian Cruz
クルズ クリスチアン ダニエル