Re: Swappiness setting on a linux pg server

[Ron - Thu at 03:10:35PM -0400]
> Jim is correct that traditional 7.x folklore regarding shared buffer
> size is nowhere near as valid for 8.x. Jim tends to know what he is
> talking about when speaking about pg operational issues.

I would not doubt it, but it's always better to hear it from more people
:-)

> Nonetheless, "YMMV". The only sure way to know what is best for your
> SW running on your HW under your load conditions is to test, test, test.

Certainly. My time and possibilities for testing is not
that great at the moment, and in any case I believe some small
adjustments won't cause the really significant results.

In any case, our database server is not on fire at the moment and people
are not angry because of slow reports at the moment. (actually, I
started this thread out of nothing but curiousity ... triggered by
somebody complaining about his desktop windows computer swapping too
much :-) So, for this round of tunings I'm more than satisfied just
relying on helpful rules of the thumb.

> A= Find out how much RAM your OS image needs.
> Usually 1/3 to 2/3 of a GB is plenty.

A quick glance on "free" already revealed we are using less than 400 MB
out of 6G totally (with the 7.x-mindset that the OS should take care of
cacheing), and according to our previous settings, the shared buffers
was eating 200 MB of this - so most of our memory is free.

> B= Find out how much RAM pg tasks need during typical peak usage and
> how much each of those tasks is using.

I believe we have quite good control of the queries ... there are
safeguards to prevent most of the heavy queries to run concurrently, and
the lighter queries shouldn't spend much memory, so it should be safe
for us to bump up the setting a bit.

In any case, I guess the OS is not that bad at handling the memory
issue. Unused memory will be used relatively intelligently (i.e.
buffering the temp files used by sorts) and overuse of memory will cause
some swapping (which is probably quite much worse than using temp files
directly, but a little bit of swapping is most probably not a disaster).