Re: Solaris shared_buffers anomaly?

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