Re: [previously on HACKERS] "Compacting" a relation

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Peter Eisentraut <peter_e(at)gmx(dot)net>, pgsql-patches(at)postgresql(dot)org
Subject: Re: [previously on HACKERS] "Compacting" a relation
Date: 2007-02-21 22:16:02
Message-ID: 200702212216.l1LMG2a06555@momjian.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-patches


I applied the optional VACUUM FULL version, but modified to code to say
20% rather than a factor of 5, attached.

---------------------------------------------------------------------------

Simon Riggs wrote:
> 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(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.
> >
> > 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
>

[ Attachment, skipping... ]

[ Attachment, skipping... ]

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

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

Attachment Content-Type Size
/rtmp/diff text/x-diff 1.7 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andrew Hammond 2007-02-21 22:20:51 Re: Plan invalidation design
Previous Message Florian G. Pflug 2007-02-21 22:14:25 Re: Column storage positions

Browse pgsql-patches by date

  From Date Subject
Next Message Alvaro Herrera 2007-02-21 22:29:48 Re: [previously on HACKERS] "Compacting" a relation
Previous Message daveg 2007-02-21 22:00:11 Re: WIP patch - INSERT-able log statements