Hi all -
I have some questions about tuning the various kernel parameters
on FreeBSD. I've read Bruce's article a couple of times and the various
parts of the documentation, but I still have some questions and was hoping
people here could confirm/deny my assumptions.
The machine in question has 512 megs of ram and doesn't do anything else
significant other than postgresql.
As I understand there are two major tuneable parameters:
- shared buffer cache: which I can think of as a sort of RAM-based
disk cache of recently accessed tables (or parts of tables). Ideally this
would be large enough to hold the entire database. The goal is to make
this large enough to hold the most commonly accessed tables.
- sort memory batch size: this is the amount of memory that *each backend*
uses to do it's sorts/merges/joins. If the backend needs more than this
then it writes to temporary files. Again the goal would be to make all
your sorts/merges/joins fit in this size.
The overall goal is to give enough memory to postgresql so that it writes
to disk as little as possible while making sure that the OS as a whole
doesn't have to start swapping. So as a starting point could one install
the OS, let it run for a bit, see how much memory it's using, see how much
memory is left and assign most of that to postgresql?
Regarding the KERNEL parameters. I can follow the general rules mentioned
in the docs for configuring things no problem. However is there any
danger in doing that? Can they be too big? Or can they only be too big
in conjunction with giving postgresql too much memory? In otherwords if I
set them to something, but don't run postgresql have I affected how the OS
will run by itself?
Is there a way to determine the maximum number of backends that can be run
given a given amount of RAM?
Also, if anyone has a FreeBSD box with 512 ram, what are your kernel
settings?
Thanks!
-philip