Thread: Questions about tuning on FreeBSD...

Questions about tuning on FreeBSD...

From
Philip Hallstrom
Date:
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




Re: Questions about tuning on FreeBSD...

From
postgres@vsservices.com
Date:
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?

Re: Questions about tuning on FreeBSD...

From
"Christian MEUNIER"
Date:
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
> > 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?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster



Re: Questions about tuning on FreeBSD...

From
Tom Lane
Date:
[ 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