Re: VACUUM FULL versus CLUSTER ON

On Mon, 2006-07-10 at 10:50 -0500, Scott Marlowe wrote:
> On Sat, 2006-07-08 at 10:20, Joshua D. Drake wrote:
> > >
> > > Unfortunately it would appear that I cannot vacuum full either as I get an
> > > out of memory error:
> > >
> > >
> > > # - Memory -
> > >
> > > shared_buffers = 5000 # min 16, at least max_connections*2, 8KB
> > > each work_mem = 131072 # min 64, size in KB
> > > maintenance_work_mem = 524288 # min 1024, size in KB
> > > max_stack_depth = 4096 # min 100, size in KB
> >
> > You could decrease your maintenance_work_mem,
> >
> > But honestly, at this point I would do the backup restore method.
>
> Also, this kind of points out that you might not have enough swap
> space. On most database servers there's enough hard drive space laying
> about to have as large a swap space as you'd like, and I can't count the
> number of times a large swap has given me enough to time to catch
> runaway processes and keep an ailing server up and running, albeit
> hobbling along, rather than having to worry about running out of virtual
> memory.
>
> Unless the memory being allocated here just has to be real memory. But
> I'm guessing not. Sure, swapping is slow, but at least it will let some
> memory hungry processes finish.

The box has 8G of RAM and 10G swap space available to it (almost none of
which touched). The problem was that the VACUUM FULL process never
released any memory. With maintenance work mem set to 512MB, I would
think that it would be enforced such that any given connection would
only be allowed 512MB for maintenance. Why it it growing beyond the 1.5G
I allow system-wide for any given process eludes me right now (and why I
suspect a bad memory leak).

As per the other suggestions, I will end up doing a pg_dump/restore to
reclaim the lost space.

Sven