> > I currently use 1.4 gigs for "shared mem" in my database (out of 2G) - I
> > couldn't get PostgreSQL to run with more than that (it might be an OS
> > limit, Linux 2.2).
>
>Does it just fail to allocate? There may be a kernel parameter you
>have to tweak.
I'm simply not sure, since I was trying to allocate 1.5gb for it, and
modified the shm-max parameter in the /proc filesystem accordingly.
However, since I got it to work for about 1.3 as it turns out (numeric
value 170000) that seemed enough for the moment and I stopped investigating.
>What is the advantage, if any, to having postgres do the buffering
>in its shared memory rather than letting the OS do it?
I monitor the sar and vmstat's regularly and have noticed that PostgreSQL
performs nicely with these settings, and no swap usage occurs. I have this
unfounded assumption that PostgreSQL will keep better track of its shared
memory than the typical LIFO disk-block-caching strategy the OS uses. E.g.,
it seems obvious to me that for tables which can't fit entirely in memory,
it makes more sense to leave its indices in the shared memory than the
table itself, and so when it needs to rearrange its memory, it would drop
the data tables first. I am no DB programmer (leave that to tom, he seems
to do a good job :) but since it is a dedicated DB machine, it's so far fine.
>One disadvantage I can think of is that when a back end (or several
>back ends) allocates a lot of memory for sorting (assuming you let
>them do that), you might end up pushing the sort memory out to your
>swap disk, whereas if the OS is doing the buffer management, it
>can just buffer fewer file blocks while you're doing the sort,
>instead.
Yes, this has been noted several times. So far, however, I have not seen
any swap usage. I would be more likely to decrease sort mem first, and
would prefer if I could do all my queries without sorts in the first place!
(wishful thinking)
Cheers,
Doug