Skip site navigation (1) Skip section navigation (2)

Re: Any better plan for this query?..

From: Dimitri <dimitrik(dot)fr(at)gmail(dot)com>
To: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
Cc: Dimitri Fontaine <dfontaine(at)hi-media(dot)com>, Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Aidan Van Dyk <aidan(at)highrise(dot)ca>, Simon Riggs <simon(at)2ndquadrant(dot)com>, 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-12 12:28:50
Message-ID: 5482c80a0905120528m65fcfc80uccccce6742408b64@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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
>

In response to

Responses

pgsql-performance by date

Next:From: Stefan KaltenbrunnerDate: 2009-05-12 12:36:22
Subject: Re: Any better plan for this query?..
Previous:From: Cory CoagerDate: 2009-05-12 12:05:19
Subject: Re: Query planner making bad decisions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group