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
