Re: Vacuum running out of memory

[Jonathan Foy <thefoy@xxxxxxxxx>]

I was wondering if that was the problem.  So I'm correct in thinking that the failure occurred when the vacuum tried to pull its 256 MB as defined in the maintenance_work_mem value, and the system just did not have enough available...any idea why that would suddenly start happening?  The indexes I created shouldn't have affected that, should they?

And point taken with the update.  I'm pushing to get us to 8.4, unsuccessfully so far, but management might be more amenable to minor version upgrades, since as I understand it there shouldn't be any risk of application problems with minor version changes...

On Tue, Dec 8, 2009 at 11:22 AM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:

Jonathan Foy <thefoy@xxxxxxxxx> writes:
> My vacuums have suddenly started to fail, seemingly at random.  I am
> confused.

> I'm running 8.1.3, with close to a dozen servers, up to 150 databases each.
> I have 8GB of RAM.  Vacuums have started to fail on all servers (though only
> the occasional vacuum) with the following error:

> VACUUM,ERROR:  out of memory
> VACUUM,DETAIL:  Failed on request of size 268435452

I'd back off maintenance_work_mem if I were you.  I think you don't have
enough RAM to be running a lot of concurrent VACUUMs all with the same
large memory consumption.

Also, if it's really 8.1.3, consider an update to 8.1.something-recent.
Not only are you exposed to a number of very serious known bugs, but
this patch in particular would likely help you:
http://archives.postgresql.org/pgsql-committers/2007-09/msg00377.php

                       regards, tom lane