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

Re: Any better plan for this query?..

From: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
To: Dimitri <dimitrik(dot)fr(at)gmail(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 11:35:32
Message-ID: 4A095F04.8090200@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-performance
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: Simon RiggsDate: 2009-05-12 11:49:41
Subject: Re: Any better plan for this query?..
Previous:From: Simon RiggsDate: 2009-05-12 11:33:10
Subject: Re: Any better plan for this query?..

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