2011/12/10 Tomas Vondra
<tv@fuzzy.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
クルズ クリスチアン ダニエル