2011/12/10 Tomas Vondra <tv@xxxxxxxx>
-- On 10.12.2011 23:40, Daniel Cristian Cruz wrote:A single query is processes by a single CPU, so even if the system is
> At work we have a 24 cores server, with a load average around 2.5.
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.
Not really, due to the "single query / single CPU" rule.
> 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.
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 productionWhat I was pointing out is that you probably should not enable loggin
> too, but it could be good to know how things work in "auto-pilot" mode.
"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
クルズ クリスチアン ダニエル
