Subject: Re: Very high effective_cache_size == worse performance?
From: "Joshua D. Drake" <jd@commandprompt.com>
Reply-To: jd@commandprompt.com
To: David Kerr <dmk@mr-paradox.net>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <20100420173936.GA50886@mr-paradox.net>
References: <20100420173936.GA50886@mr-paradox.net>
Content-Type: text/plain; charset="UTF-8"
Organization: Command Prompt, Inc.
Date: Tue, 20 Apr 2010 10:41:36 -0700
Message-ID: <1271785296.25602.8.camel@jd-desktop.unknown.charter.com>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Tue, 2010-04-20 at 10:39 -0700, David Kerr wrote:
> Howdy all,
> 
> I've got a huge server running just postgres. It's got 48 cores and 256GB of ram. Redhat 5.4, Postgres 8.3.9.
> 64bit OS. No users currently.
> 
> I've got a J2EE app that loads data into the DB, it's got logic behind it so it's not a simple bulk load, so
> i don't think we can use copy.
> 
> Based on the tuning guides, it set my effective_cache_size to 128GB (1/2 the available memory) on the box.
> 
> When I ran my load, it took aproximately 15 hours to do load 20 million records. I thought this was odd because
> on a much smaller machine I was able to do that same amount of records in 6 hours.
> 
> My initial thought was hardware issues so we got sar, vmstat, etc all running on the box and they didn't give
> any indication that we had resource issues.
> 
> So I decided to just make the 2 PG config files look the same. (the only change was dropping effective_cache_size 
> from 128GB to 2GB).
> 
> Now the large box performs the same as the smaller box. (which is fine).
> 
> incidentally, both tests were starting from a blank database.
> 
> Is this expected? 

Without a more complete picture of the configuration, this post doesn't
mean a whole lot. Further, effective_cash_size is not likely to effect a
bulk load at all.

Joshua D. Drake


> 
> Thanks!
> 
> Dave
> 


-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering