Questions about tuning on FreeBSD...

On Monday 10 September 2001 18:52, Philip Hallstrom wrote:

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.

Depending on the size of your DB, this should do OK...

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.

I run with shared buffers = 5120

- 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.

Sort Mem = 4096

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 Free# SYSV stuff BSD box with 512 ram, what are your
kernel settings?

options SYSVSHM #SYSV-style shared memory
options SHMMAXPGS=12288
options SHMMAX="(SHMMAXPGS*PAGE_SIZE+1)"
options SHMSEG=256
options SHMMNI=512
options SHMMIN=1

options SYSVMSG #SYSV-style message queues

options SYSVSEM #SYSV-style semaphores
options SEMMNI=256
options SEMMNS=512
options SEMMNU=256
options SEMMAP=256

Note: Some of these might be WAY TOO high!
I could not find enough docs to tell me, so I just added extra
everywhere.... Maybe some of the experts can pick this apart...

Thanks!

-philip

On that note: Could some of the PostgreSQL expert take a look my
number of buffers and the kernel config and tell me if I'm running too
much of anything?

GB

--
GB Clark II | Roaming FreeBSD Admin
gclarkii@VSServices.COM | General Geek
CTHULU for President - Why choose the lesser of two evils?

What about WAL buffer parameter ?

i thought if this buffer is too small, un necessary I/O will occur and slow
down performance

<postgres@vsservices.com> wrote in message
news:01091023443406.73075@prime.vsservices.com...

On Monday 10 September 2001 18:52, Philip Hallstrom wrote:

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.

Depending on the size of your DB, this should do OK...

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.

I run with shared buffers = 5120

- 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.

Sort Mem = 4096

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 Free# SYSV stuff BSD box with 512 ram, what are

your

[ very late comment on this thread ]

Philip Hallstrom <philip@adhesivemedia.com> writes:

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.

More accurately, what you want is for your heavily used tables to fit
into shared buffer cache plus kernel disk buffers. Normally, a Unix
kernel will automatically use whatever RAM isn't being used by
applications to hold copies of disk pages. So, fetching a page that's
in kernel buffers but not in Postgres' shared disk buffers requires the
overhead of a kernel call, but that's still lots less than going to disk.

It's not necessarily a good idea to crank up your shared buffer cache to
be a large fraction of memory. At best, you're ensuring that more pages
will be in shared buffers so you don't have to go to kernel buffers ---
but the gains from that are marginal, per above. At worst, you make the
shared memory area itself a target for swapping --- the kernel may swap
out pages of shared memory that haven't been touched recently. If that
happens, you negate the whole point of the buffering, which is to have
the info in RAM not on disk. You'll pay a disk access to get back the
swapped-out pages whenever you next want them. So, the shared buffer
arena should be kept small enough to ensure that all the buffers are
touched often and thus won't look like swap-out candidates to the kernel.
(On some Unixen it's possible to demand that shared memory segments not
be swapped, but I don't think we try to do that.)

While I have no measurements to back this up, my best guess is that
shared buffer settings larger than a few thousand buffers are not very
helpful, and may even be counterproductive because of these effects.

- 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.

Note that a complex query may involve several sorts and/or hashes, and
*each* such operation will feel free to use as much as SortMem space
before it starts to use temp files. So it's wise to assume that each
backend might use some small multiple of SortMem in the worst case.

regards, tom lane