Re: VACUUM ANALYZE out of memory

Thanks for the rapid responses.

Stefan Kaltenbrunner wrote:
> this seems simply a problem of setting maintenance_work_mem too high
> (ie higher than what your OS can support - maybe an
> ulimit/processlimit is in effect?) . Try reducing maintenance_work_mem
> to say 128MB and retry.
> If you promise postgresql that it can get 1GB it will happily try to
> use it ...
I set up the system together with one of our Linux sysOps, so I think
the settings should be OK. Kernel.shmmax is set to 1.2 GB, but I'll get
him to recheck if there could be any other limits he has forgotten to
increase.

The way the process was running, it seems to have basically just
continually allocated memory until (presumably) it broke through the
slightly less than 1.2 GB shared memory allocation we had provided for
PostgreSQL (at least the postgres process was still running by the time
resident size had reached 1.1 GB).

Incidentally, in the first error of the two I posted, the shared memory
setting was significantly lower (24 MB, I believe). I'll try with 128 MB
before I leave in the evening, though (assuming the other tests I'm
running complete by then).

Simon Riggs wrote:
> On Tue, 2007-12-11 at 10:59 +0100, Michael Akinde wrote:
>
>
>> I am encountering problems when trying to run VACUUM FULL ANALYZE on a
>> particular table in my database; namely that the process crashes out
>> with the following problem:
>>
>
> Probably just as well, since a VACUUM FULL on an 800GB table is going to
> take a rather long time, so you are saved from discovering just how
> excessively long it will run for. But it seems like a bug. This happens
> consistently, I take it?
>
I suspect so, though it has only happened a couple of times yet (as it
does take a while) before it hits that 1.1 GB roof. But part of the
reason for running the VACUUM FULL was of course to find out how long
time it would take. Reliability is always a priority for us,
so I like to know what (useful) tools we have available and stress the
system as much as possible... :-)
> Can you run ANALYZE and then VACUUM VERBOSE, both on just
> pg_largeobject, please? It will be useful to know whether they succeed.
>
I ran just ANALYZE on the entire database yesterday, and that worked
without any problems.

I am currently running a VACUUM VERBOSE on the database. It isn't done
yet, but it is running with a steady (low) resource usage.

Regards,

Michael A.