Subject: Re: Why different execution times for different
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Kishore B <kishorebh@gmail.com>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <42567e060510251246s1b9eda40l54e73993f8bcfb34@mail.gmail.com>
References: <42567e060510251246s1b9eda40l54e73993f8bcfb34@mail.gmail.com>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <1130271872.2872.2.camel@state.g2switchworks.com>
Mime-Version: 1.0
Date: Tue, 25 Oct 2005 15:24:32 -0500

On Tue, 2005-10-25 at 14:46, Kishore B wrote:
> Hi All, 
>  
> We are executing a single query that returned very fast on the first
> instance. But when I executed the same query for multiple times, it is
> giving strange results. It is not coming back. 
>  
> When I checked with the processes running in the system, I observed
> that multiple instances of postmaster are running and all of them are
> consuming very high amounts of memory. I could also observe that they
> are sharing the memory in a uniform distribution across them. 
>  
> Please let me know if any body has experienced the same and how do
> they resolved it.

You may or may not have an actual problem.

For one, if they're each using 128 megs, but sharing 120 megs of that
then that's not too bad.  If they're each using 512 meg and sharing 100
meg of that, then you've got a problem.

What is your sort mem set to?  Going too high can cause memory
starvation and other problems.

Also, when you run top, how much memory is being used for cache and
buffer.  If you've still got a fair amount used for cache then you're
probably ok there.

What are your settings in postgresql.conf that aren't default?  How's
the behaviour as you run 1, then 2, then 3, then 4 and so on?  Where's
the "knee" with this behaviour and what are you running out of, disk IO
or memory or memory bandwidth.

Are you using iostat/vmstat/top/free/ipcs to check resource usage under
load?