Thread: Vacuum running out of memory

Hello

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 have some terrible tables that I inherited for which I recently created tons of indexes in order to make them useful.  I had a post a couple of weeks ago detailing my problem with trying to get a function working to simplify the data...I fell back on indexes where the column values were not null/empty.  Since they are almost always null/empty, I was able to dramatically speed up access without eating up much disk space, but I did throw an extra 200 indexes into each database.  Shortly after I started getting occasional vacuum failures with the above error.

I'm not sure if it's a coincidence or not, but my maintenance_work_mem is set to 262144 KB, which matches the failed request size above.

I initially assumed that with 200*150 additional relations, I was messing up my max_fsm_relations setting, which is 60,000.  However, as a test a ran a verbose vacuum analyze on a small table to get the statistics at the end, from which I got the following:

INFO:  free space map contains 2239943 pages in 28445 relations
DETAIL:  A total of 2623552 page slots are in use (including overhead).
2623552 page slots are required to track all free space.
Current limits are:  8000000 page slots, 60000 relations, using 50650 KB.

which seems to indicate I'm well within my limits.

(for curiosity's sake, which relations count towards that limit?  From what I can tell it's only tables and indexes...functions, views, triggers, etc shouldn't contribute, should they?)

Am I interpreting this wrong?  Anyone have any insight as to what is going wrong?  I can provide more information if needed...

Thanks,

Jonathan Foy <thefoy@gmail.com> 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

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 4:31 PM, Jonathan Foy <thefoy@gmail.com> wrote:
> 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...

Correct

> any idea why that would suddenly start happening?  The indexes I
> created shouldn't have affected that, should they?

Well the 8.1 vacuum was pretty inefficient in how it scanned indexes
so adding lots of indexes will make it take a lot longer. That might
mean you're running more vacuums at the same time now. The 8.2 vacuum
is much improved on that front, though adding lots of indexes will
still make vacuum take longer (along with updates and inserts).

> 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...

You're always better off running the most recent minor release. Minor
releases fix security holes, data corruption bugs, crashing bugs, etc.
Occasionally those bugs do fix behavioural bugs, especially early in
the release cycle before the next major release is out but mostly
they're real bugs that if you had run into you would know. You should
still read all the release notes for them though.


--
greg

Jonathan Foy <thefoy@gmail.com> writes:
> 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?

Not directly, AFAICS, but they could stretch out the time required to
vacuum their tables, thus possibly leading to vacuums overlapping that
didn't overlap before.  Just a guess though.  Another likely bet is
that this is just an effect of the overall system load increasing
over time (more backends == more memory needed).

            regards, tom lane