Re: Out of Memory errors while running pg_dump

Erik Jones <erik(at)myemma(dot)com> writes:
> On Feb 4, 2008, at 3:26 PM, Tom Lane wrote:
>> Are you sure the postmaster is being launched
>> under ulimit unlimited?

> ulimit -a gives:

One possible gotcha is that ulimit in an interactive shell isn't
necessarily the same environment that an init script will see;
you might want to temporarily put it into the init script, or
if you have plperlu or another untrusted language installed,
you could try system("ulimit -a >/tmp/foo") to check what the
backend is really seeing. But assuming that that's not it...

>> If it's a 32-bit machine, maybe you need to
>> back off shared_buffers or other shmem size parameters so that more
>> address space is left for backend private memory.

> It is a 32-bit machine and we're currently set @ 2GB for
> shared_buffers. For the others: 512 max_connections, 512
> max_locks_per_transaction and 0 max_prepared_transactions. While
> having both of those two 512s may seem large, much less and I get Out
> of Memory errors that specifically suggest increasing
> max_lock.s_per_transaction

Yeah, because pg_dump will need a lock for each table it's touching,
so you need a lot of lock table slots. But the fact that you're using
half the address space for shared buffers explains why it's tanking not
too far past the 1Gb point for local memory. I'd suggest backing that
off to 1Gb or less of shared_buffers to give yourself breathing room
for a large relcache. Given the amount of RAM in the machine, I doubt
you'll lose much in performance --- the disk blocks that might have been
in shared buffers will live in OS disk buffers instead, which is not
that much more expensive to access.

regards, tom lane