On Mon, 2007-02-05 at 11:55 +0000, Simon Riggs wrote:
> On Sat, 2007-02-03 at 22:11 -0500, Bruce Momjian wrote:
> > Tom Lane wrote:
> > > Peter Eisentraut <peter_e@gmx.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.
>
> 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.
>
> 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.
>
> Happy to drop a patch for this, if people agree.
Enclose 2 versions:
v1 - move test and WARNING
v2 - move test and WARNING, plus adjust hint according to relation size
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
