Re: Any better plan for this query?..

For the moment I'm even not considering any scalability issues on the
Read+Write workload - it may always be related to the storage box, and
storage latency or controller/cache efficiency may play a lot.

As problem I'm considering a scalability issue on Read-Only workload -
only selects, no disk access, and if on move from 8 to 16 cores we
gain near 100%, on move from 16 to 32 cores it's only 10%...

I think I have to replay Read-Only with prepare/execute and check how
much it'll help (don't know if there are some internal locking used
when a planner is involved)..

And yes, I'll try to profile on 32 cores, it makes sense.

Rgds,
-Dimitri

On 5/12/09, Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> Dimitri wrote:
>> What I discovered so far with all your help:
>> - the impact of a planner
>> - the impact of the analyze target
>> - the impact of prepare / execute
>> - scalability limit on 32 cores
>
> You've received good advice on how to minimize the impact of the first
> three points, and using those techniques should bring a benefit. But I'm
> pretty surprised by the bad scalability you're seeing and no-one seems
> to have a good idea on where that limit is coming from. At a quick
> glance, I don't see any inherent bottlenecks in the schema and workload.
>
> If you could analyze where the bottleneck is with multiple cores, that
> would be great. With something like oprofile, it should be possible to
> figure out where the time is spent.
>
> My first guess would be the WALInsertLock: writing to WAL is protected
> by that and it an become a bottleneck with lots of small
> UPDATE/DELETE/INSERT transactions. But a profile would be required to
> verify that.
>
> --
> Heikki Linnakangas
> EnterpriseDB http://www.enterprisedb.com
>