[ 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