Re: "Compacting" a relation

Simon Riggs wrote:
> On Sat, 2007-02-03 at 22:11 -0500, Bruce Momjian wrote:
> > Tom Lane wrote:
> > > Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> > > > vacuumlazy.c contains a hint "Consider compacting this relation" but AFAICT,
> > > > there is no indication anywhere how "compacting" is supposed to be achieved.
> > > > I guess this means VACUUM FULL or CLUSTER, but I don't think the hint can be
> > > > processed effectively by a user.
> > >
> > > So change it ...
> >
> > New message is:
> >
> > errhint("Consider using VACUUM FULL on this relation or increasing the configuration parameter \"max_fsm_pages\".")));
> >
>
> The change of wording may be appropriate, but it is triggered when
>
> if (vacrelstats->tot_free_pages > MaxFSMPages)
>
> So if you VACUUM a 15+GB table and it has only 1% freespace then it will
> still generate this message. Hopefully you'd agree that the message
> would be inappropriate in that case.

Interesting. So if you have 1% free on a 15GB table, and that doesn't
fit into the free space, we emit the message. I would think the hint
is accurate, though. Are you saying they should increase FSM and not do
VACUUM FULL in those cases? Should we recommend the fsm increase before
the VACUUM FULL?

> It's also inappropriate because this message is generated *prior* to
> doing lazy_truncate_heap(), which could easily remove lots of empty
> pages anyhow. That might reduce it to less than MaxFSMPages anyhow, so
> it can currently be triggered in wholly inappropriate situations.

Yes, we should move the test if there is a better place.

>
> So I suggest that we move this wording after lazy_truncate_heap() in
> lazy_vacuum_rel() *and* we alter the hint so that it only suggests
> VACUUM FULL if the table has 20% fragmentation, whatever its size.

Interesting. OK, so we have two message, one recommends both, and the
other just FSM increase.

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +