Date: Tue, 13 Jun 2006 22:35:39 -0500
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Mischa Sandberg <mischa@ca.sophos.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Solaris shared_buffers anomaly?
Message-ID: <20060614033539.GY34196@pervasive.com>
References: <1150151282.14661.42.camel@tigger>
	<20060613000441.GI8588@kenobi.snowman.net>
	<20060613200810.GZ34196@pervasive.com>
	<448F3A6E.6080908@ca.sophos.com>
	<20060613224759.GM34196@pervasive.com>
	<448F4842.4010305@ca.sophos.com>
	<20060613231730.GS34196@pervasive.com>
	<448F51DE.2070604@ca.sophos.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <448F51DE.2070604@ca.sophos.com>
User-Agent: Mutt/1.5.11

On Tue, Jun 13, 2006 at 05:01:34PM -0700, Mischa Sandberg wrote:
> Jim C. Nasby wrote:
> >On Tue, Jun 13, 2006 at 04:20:34PM -0700, Mischa Sandberg wrote:
> >>Jim C. Nasby wrote:
> >>>What's sort_mem set to? I suspect you simply ran the machine out of
> >>>memory.
> >>8192 (8MB). No issue when shared_buffers was 2000; same apps always.
> > 
> >So if all 50 backends were running a sort, you'd use 400MB. The box has
> >4G, right?
> 
> Umm ... yes. "if". 35-40 of them are doing pure INSERTS. 
> Not following your train.

If sort_mem is set too high and a bunch of sorts fire off at once,
you'll run the box out of memory and it'll start swapping. Won't really
matter much whether it's swapping shared buffers or not; performance
will just completely tank.

Actually, I think that Solaris can be pretty aggressive about swapping
stuff out to try and cache more data. Perhaps that's what's happening?

> >>Yep, tested /etc/system segmap_percent at 20,40,60. 
> >>No significant difference between 20 and 60.
> >That's pretty disturbing... how large is your database?
> 
> ~10GB. Good locality. Where heading?

I guess I should have asked what your working set size was... unless
that's very small, it doesn't make sense that changing the cache size
that much wouldn't help things.

BTW, on some versions of Solaris, segmap_percent doesn't actually work;
you have to change something else that's measured in bytes.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461