Re: VACUUM FULL performance issues with pg_largeobject table

Hi Tom,

As always, your insight is VERY helpful. We'll try your suggestions and see
if that helps things out... Thanks!

Sam

On Fri, Jan 22, 2010 at 4:01 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> PG User 2010 <pguser2010(at)gmail(dot)com> writes:
> > 1) is there any easy way to fiddle with the vacuum process so that it is
> not
> > CPU bound and doing very little I/O? Why would vacuum full be CPU bound
> > anyway???
>
> The only part of VAC FULL that seems like it could be CPU-bound is index
> cleanup. If the table is sufficiently bloated with dead tuples, that
> could take awhile. It might be useful to try this:
>
> 1. REINDEX TABLE pg_largeobject;
> 2. VACUUM pg_largeobject;
> 3. VACUUM FULL pg_largeobject;
>
> I have never tried this in a serious bloat situation, but in principle
> I think it should improve matters. The idea is to get rid of as many dead
> index and heap entries as you can before letting VAC FULL loose on it, and
> also do as much of the work as possible with a less-than-exclusive lock.
> Don't forget that large maintenance_work_mem will help the first two
> steps, as long as you don't set it so high as to drive the machine into
> swapping.
>
> > 2) is it possible to interrupt VACUUM FULL, then re-start it later on and
> > have it pick up where it was working before?
>
> NO. Doing that will in fact make things worse --- a failed VAC FULL
> results in even more dead entries to be cleaned up.
>
> regards, tom lane
>