Re: Any better plan for this query?..

On 5/19/09 3:46 AM, "Dimitri" <dimitrik(dot)fr(at)gmail(dot)com> wrote:

> 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.

Symmetric Multi Threading (HyperThreading in Intels marketing terms, other
marketing terms for Sun or IBM). One CPU core that can handle more than one
concurrently executing thread.
Technically, 'SMT' allows instructions in flight from multiple threads at
once in a superscalar Cpu core while some implementations differ and might
technically CMT (one thread or the other, but can switch fast, or a
non-superscalar core).

For many implementations of 'multiple threads on one CPU core' many of the
processor resources are reduced per thread when it is active -- caches get
split, instruction re-order buffers are split, etc. That is rather hardware
implementation dependant.

For Intel's SMT (and other similar), spin-locks hurt scalability if they
aren't using new special instructions for the spin to yield pipeline slots
to the other thread.

Generally, code that stresses common processor resources more than CPU
execution will scale poorly with SMT/CMT etc.

So I'm not sure about the Postgres details, but the general case of an
application that doesn't benefit from these technologies exists, and there
is a non-zero chance that Postgres has some characteristics of such an app.

>>
>> (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_CtxSwit
> ch_100
>

Well, on most systems over 100K context switches/sec is a lot. And those
reach 180000 /sec.
However, this is 'only' 10 context switches per transaction and less than
20% system CPU, so maybe those numbers aren't quite as big as they seem.

Out of curiosity, what was the context switch rate for MySql at its peak
throughput?
>
>>
>> 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
>

Ok, so that's good info that the planner or parser side seems to scale less
effectively than the execution (as the results show), but I'm wondering
about queries with longer execution times not longer planner times. I'm
wondering that, because its my opinion that most applications that will use
larger scale hardware will have more complicated queries than your test.
Its also greedy on my part since most queries in my applications are
significantly more complicated.
Regardless of my opinions -- this test is on one extreme (small fast
queries) of the spectrum. Its useful to know some data points on other
parts of the spectrum.