daveg <daveg@sonic.net> writes:
> I work with a client that runs 16Gb memory with 16Gb of swap on dual opterons
> dedicated to postgres. They have large tables and like hash joins as they are
> often the fastest way to a result, so work_mem is set fairly large. Sometimes
> postgres is very inaccurate predicting real memory use verses work_mem and
> will grow very much larger than expected.
FWIW, 8.1 should be a lot better at this --- it can dynamically readjust
the hash join parameters to keep memory usage under the work_mem limit.
> When this happens the machine runs out of memory and swap. Without the oom
> killer it simply hangs the machine which is inconvenient as it is at a remote
> location.
It shouldn't "hang" in any case ... something wrong there. I can
believe that the machine would go to its knees as it thrashes more
and more while approaching the totally-out-of-swap point, but it
shouldn't hang up. You might have a kernel bug to deal with.
regards, tom lane