Re: VACUUM ANALYZE out of memory

Michael Akinde wrote:
> 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).

this is most likely not at all related to your shared memory settings
but to your setting of maintenance_work_mem which is the amount of
memory a single backend(!) can use for maintainance operations (which
VACUUM is for example).
notice that your first error refers to an allocation of about 500MB
which your ulimit/kernel process limit simply might not be able to give
a single process.
And for very large tables VACUUM FULL is generally not a good idea at
all - either look into regular normal vacuum scheduling or if you need
to recover from a a bloated database use a command that forced a rewrite
of the table (like CLUSTER) which will be heaps faster but also require
about twice the amount of diskspace.

Stefan