Questions about tuning on FreeBSD...
Philip Hallstrom
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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
post...@vsservices.com
> 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.
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
gcla...@VSServices.COM | General Geek
CTHULU for President - Why choose the lesser of two evils?
Christian MEUNIER
i thought if this buffer is too small, un necessary I/O will occur and slow
down performance
<post...@vsservices.com> wrote in message
news:0109102344...@prime.vsservices.com...
Tom Lane
Philip Hallstrom <phi...@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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majo...@postgresql.org)