Re: performance issues

DHSC Webmaster <webmaster@dhs-club.com> writes:
> Thank you Tom. That has seemed to help substantially.
> I received another sugestion to increase -B 30000 which would give 240
> meg. to postgres. I plan to give that a try. I've already reset the
> SHMMAX val in sysctl to accomodate that size. Do you see any reason not
> to try this? My thought is if some is good, more must be better. :)

Not always; there are several reasons why more can be worse:

1. It's a good idea to leave plenty of free RAM for kernel-level disk
buffering.  Postgres has been tuned (to the extent that it is tuned)
to operate with a fair amount of kernel buffering underneath it.

2. If you are on a platform where the kernel thinks it can swap out
parts of shared memory (which is most platforms, these days), it's
counterproductive to make your shared mem large enough that it's not
all being used heavily.  If the kernel decides to swap out some buffer
space, you have a net loss in performance, because you just wasted
disk I/O (especially if the page in the buffer is dirty --- it'll
eventually have to be read in again, then written out again, to no
purpose).

3. Also, there are some operations (such as dropping a table) which
cause linear scans of the whole buffer array, so making it too big
can slow things down, depending on your query mix.

While I have not done a lot of measurement to back this up, my rule of
thumb is that you don't want to reserve more than about a quarter of
your physical RAM for Postgres' disk buffers, even on a dedicated
machine.  But feel free to experiment and see what results you get.
Please report back if you do any sort of organized tuning experiments;
AFAIK we don't have many hard numbers about these effects.

            regards, tom lane