Re: Any better plan for this query?..

On 5/19/09, Scott Carey <scott(at)richrelevance(dot)com> wrote:
>
> On 5/18/09 3:32 PM, "Dimitri" <dimitrik(dot)fr(at)gmail(dot)com> wrote:
>
>> On 5/18/09, Scott Carey <scott(at)richrelevance(dot)com> wrote:
>>> Great data Dimitri!'
>>
>> Thank you! :-)
>>
>>>
>>> I see a few key trends in the poor scalability:
>>>
>>> The throughput scales roughly with %CPU fairly well. But CPU used
>>> doesn't
>>> go past ~50% on the 32 core tests. This indicates lock contention.
>>>
>>
>> You should not look on #1 STATs, but on #2 - they are all with the
>> latest "fixes" - on all of them CPU is used well (90% in pic on
>> 32cores).
>> Also, keep in mind these cores are having 2 threads, and from Solaris
>> point of view they are seen as CPU (so 64 CPU) and %busy is accounted
>> as for 64 CPU
>>
>
> Well, if the CPU usage is actually higher, then it might not be lock waiting
> -- it could be spin locks or context switches or cache coherency overhead.
> Postgres may also not be very SMT friendly, at least on the hardware tested
> here.

do you mean SMP or CMT? ;-)
however both should work well with PostgreSQL. I also think about CPU
affinity - probably it may help to avoid CPU cache misses - but makes
sense mostly if pooler will be added as a part of PG.

>
> (what was the context switch rate? I didn't see that in the data, just
> mutex spins).

increasing with a load, as this ex.:
http://dimitrik.free.fr/Report_20090505/5539_dim_STAT_100.html#bmk_CPU_CtxSwitch_100

>
> The scalability curve is definitely showing something. Prepared statements
> were tried, as were most of the other suggestions other than one:
>
> What happens if the queries are more complicated (say, they take 15ms server
> side with a more complicated plan required)? That is a harder question to
> answer

What I observed is: if planner takes more long time (like initially
with 8.3.7 and analyze target 1000) the scalability problem is
appearing more strange -
http://dimitrik.free.fr/Report_20090505/5521_dim_STAT_18.html - as you
see CPU even not used more than 60% , and as you may see spin locks
are lowering - CPUs are not spinning for locks, there is something
else..
I'm supposing a problem of some kind of synchronization - background
processes are not waking up on time or something like this...
Then, if more time spent on the query execution itself and not planner:
- if it'll be I/O time - I/O will hide everything else until you
increase a storage performance and/or add more RAM, but then you come
back to the initial issue :-)
- if it'll be a CPU time it may be interesting! :-)

Rgds,
-Dimitri