Date: Wed, 14 Jun 2006 12:10:43 +1200
From: Mark Kirkwood <markir@paradise.net.nz>
Subject: Re: Solaris shared_buffers anomaly?
In-reply-to: <448F3A6E.6080908@ca.sophos.com>
To: mischa@ca.sophos.com
Cc: pgsql-performance@postgresql.org
Message-id: <448F5403.7030903@paradise.net.nz>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7bit
User-Agent: Thunderbird 1.5 (X11/20060417)
References: <1150151282.14661.42.camel@tigger>
	<20060613000441.GI8588@kenobi.snowman.net>
	<20060613200810.GZ34196@pervasive.com> <448F3A6E.6080908@ca.sophos.com>

Mischa Sandberg wrote:
> Jim C. Nasby wrote:
> ...
>> Actually, in 8.1.x I've seen some big wins from greatly increasing the
>> amount of shared_buffers, even as high as 50% of memory, thanks to the
>> changes made to the buffer management code. ...
> 
> Anyone else run into a gotcha that one of our customers ran into?
> PG 7.4.8 running on Solaris 2.6, USparc w 4GB RAM.
> Usually about 50 active backends.
> (No reason to believe this wouldn't apply to 8.x).
> 
> Initially shared_buffers were set to 1000 (8MB).
> Then, we moved all apps but the database server off the box.
> 
> Raised shared_buffers to 2000 (16MB).
> Modest improvement in some frequent repeated queries.
> 
> Raised shared_buffers to 16000 (128MB).
> DB server dropped to a CRAWL.
> 
> vmstat showed that it was swapping like crazy.
> Dropped shared_buffers back down again. Swapping stopped.
> 
> Stared at "ps u" a lot, and realized that the shm seg appeared to
> be counted as part of the resident set (RSS).
> Theory was that the kernel was reading the numbers the same way,
> and swapping out resident sets, since they obviously wouldn't
> all fit in RAM :-)
> 
> Anyone from Sun reading this list, willing to offer an opinion?
>

A while ago I ran 7.4.? on a Solaris 2.8 box (E280 or E220 can't recall) 
with 2G of ram - 40 users or so with shared_buffers = approx 12000 - 
with no swapping I recall (in fact I pretty sure there was free memory!).

I suspect something else is your culprit - what is work_mem (or 
sort_mem) set to? I'm thinking that you have this high and didn't have 
much memory headroom to begin with, so that upping shared_buffers from 
16MB -> 128MB tipped things over the edge!

Cheers

Mark