Re: VACUUM FULL out of memory

Tom Lane wrote:
> Michael Akinde <michael(dot)akinde(at)met(dot)no> writes:
>> INFO: vacuuming "pg_catalog.pg_largeobject"
>> ERROR: out of memory
>> DETAIL: Failed on request of size 536870912
>
> Are you sure this is a VACUUM FULL, and not a plain VACUUM?
Very sure.

Ran a VACUUM FULL again yesterday (the prior query was a VACUUM FULL
ANALYZE) and received essentially the same error, simply with different
failure size.

INFO: vacuuming "pg_catalog.pg_largeobject"
ERROR: invalid memory alloc request size 1073741824

No changes done on the system from the previous iteration. VACUUM ran OK
on the 8.3beta2 instance I tested with before Christmas (current setup
is 8.2.5)

> I suspect that it's the latter, and the reason it's failing is that
> you are
> running the postmaster under a ulimit that is less than 512MB (or at
> least not enough more to allow an allocation of that size).
We went over this somewhat prior to Christmas. Here's how its currently
set up.

$> ulimit -a
core file size (blocks, -c) 100000000
data seg size (kbytes, -d) unlimited
max nice (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) unlimited
max locked memory (kbytes, -l) unlimited
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) unlimited
max rt priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) unlimited
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited

Anything we should try to change?

Andrew Sullivan wrote:
> Something is using up the memory on the machine, or (I'll bet this is
more
> likely) your user (postgres? Whatever's running the postmaster) has a
> ulimit on its ability to allocate memory on the machine.

If one looks at the system resources while the VACUUM FULL is going up,
its pretty obvious that its a postgres process going on a memory
allocation rampage that eats up all the resources.

> No, it's not really that big. I've never seen a problem like this. If it
> were the 8.3 beta, I'd be worried; but I'm inclined to suggest you
look at
> the OS settings first given your set up.

Have the same problem with the 8.3beta, but won't be using it anyway
until its been out for a while.

> Note that you should almost never use VACUUM FULL unless you've really
> messed things up. I understand from the thread that you're just testing
> things out right now. But VACUUM FULL is not something you should _ever_
> need in production, if you've set things up correctly.

That's good to hear. I'm not particularly worried about this with
respect to my own system. So far, we have found Postgres amazingly
robust in every other issue that we have deliberately (or unwittingly)
provoked. More reason to be puzzled about this problem, though.

Holger Hoffstaette wrote:
> Then why does it exist? Is it a historical leftover? If it is
> only needed for emergency, should it not have a different name?

Or in this case: if VACUUM FULL is never required (except in very
special circumstances), it might be a good idea not to have VACUUM
recommend running it (cf. the VACUUM I ran before New Year on a similar
size table).

INFO: vacuuming "pg_catalog.pg_largeobject"

INFO: scanned index "pg_largeobject_loid_pn_index" to remove 106756133
row versions
DETAIL: CPU 38.88s/303.43u sec elapsed 2574.24 sec.
INFO: "pg_largeobject": removed 106756133 row versions in 13190323 pages
DETAIL: CPU 259.42s/113.20u sec elapsed 14017.17 sec.

INFO: index "pg_largeobject_loid_pn_index" now contains 706303560 row
versions in 2674471 pages
DETAIL: 103960219 index row versions were removed.
356977 index pages have been deleted, 77870 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.02 sec.

INFO: "pg_largeobject": found 17489832 removable, 706303560 nonremovable
row versions in 116049431 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 36000670 unused item pointers.
64493445 pages contain useful free space.
0 pages are entirely empty.
CPU 1605.42s/1107.48u sec elapsed 133032.02 sec.

WARNING: relation "pg_catalog.pg_largeobject" contains more than
"max_fsm_pages" pages with useful free space HINT: Consider using VACUUM
FULL on this relation or increasing the configuration parameter
"max_fsm_pages".

Anyway, thanks for the responses.

I do have the test setup available for hopefully some weeks, so if there
is anyone interested in digging further into the matter, we do have the
possibility to run further test attempts for a while (it takes about a
week to load all the data, so once we take it back down, it may be a
while before we set it up again).

Regards,

Michael Akinde
Database Architect, met.no