Re: Out of Memory errors while running pg_dump

On Feb 4, 2008, at 3:26 PM, Tom Lane wrote:

> Erik Jones <erik(at)myemma(dot)com> writes:
>> Sure. I've attached an archive with the full memory context and
>> error for each. Note that I'm already 99% sure that this is due to
>> our exorbitantly large relation set which is why I think pg_dump's
>> catalog queries are running out of work_mem (currently at just over
>> 32MB).
>
> work_mem doesn't seem to be your problem --- what it looks like to
> me is
> that it's CacheMemoryContext and subsidiary contexts that's growing to
> unreasonable size, no doubt because of all the relcache entries for
> all
> those tables pg_dump has to touch. I'm wondering a bit why
> CacheMemoryContext has so much free space in it, but even if it had
> none
> you'd still be at risk. There isn't any provision in the current
> backend to limit the number of relcache entries, so eventually you're
> gonna run out of space if you have enough tables.
>
> Even so, you seem to be well under 1Gb in the server process. How
> much
> RAM is in the machine?

16GB total. When this occurred we had over 9G free on the system.

> Are you sure the postmaster is being launched
> under ulimit unlimited?

ulimit -a gives:

core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
open files (-n) 256
pipe size (512 bytes, -p) 10
stack size (kbytes, -s) 10240
cpu time (seconds, -t) unlimited
max user processes (-u) 16357
virtual memory (kbytes, -v) unlimited

> 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

> In the long run you probably ought to rethink having so many tables;
> that doesn't sound like great database design to me.

No, this is definitely not a good design. It was one set up by
application developers about three years ago who apparently had the
very wrong idea about table inheritance. Since I took over the
database administration last summer it's been a game of whack-a-mole
trying deal with issues that keep popping up because of it. I do
have migration plans to remedy this, but it's slow going as I'm also
dependent on application developer time for the necessary migrations
there.

> A possible stopgap answer is to be selective about how many tables get
> dumped per pg_dump run, though I'm worried about the risk of leaving
> some out entirely.

Well, once a month I dump tables specific to accounts that have been
closed for more than 90 days, everything left is pretty critical as
it's either internal company data or active client data.

Thanks for all of your help and suggestions yet again, Tom, You're
willingness to take at look at these kind of things is more
appreciated than you probably know :)

Erik Jones

DBA | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com