From: Mark Kirkwood <markir@paradise.net.nz>
To: mischa@ca.sophos.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Solaris shared_buffers anomaly?
Date: Wed, 14 Jun 2006 12:10:43 +1200
Message-ID: <448F5403.7030903@paradise.net.nz> (raw)
In-Reply-To: <448F3A6E.6080908@ca.sophos.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: markir@paradise.net.nz, mischa@ca.sophos.com
Subject: Re: Solaris shared_buffers anomaly?
In-Reply-To: <448F5403.7030903@paradise.net.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox