From: | Dimitri <dimitrik(dot)fr(at)gmail(dot)com> |
---|---|
To: | Scott Carey <scott(at)richrelevance(dot)com> |
Cc: | Simon Riggs <simon(at)2ndquadrant(dot)com>, Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>, Dimitri Fontaine <dfontaine(at)hi-media(dot)com>, Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Aidan Van Dyk <aidan(at)highrise(dot)ca>, Merlin Moncure <mmoncure(at)gmail(dot)com>, PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Any better plan for this query?.. |
Date: | 2009-05-19 10:46:45 |
Message-ID: | 5482c80a0905190346t1f3b1550t499c9f7de461c9b2@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Dimitri | 2009-05-19 10:51:56 | Re: Any better plan for this query?.. |
Previous Message | Dimitri | 2009-05-19 10:24:58 | Re: Any better plan for this query?.. |