Fw: Postgresql backend to perform vacuum automatically

----- Original Message -----
From: "Nicolas Bazin" <nbazin@ingenico.com.au>
To: "mlw" <markw@mohawksoft.com>
Sent: Wednesday, March 06, 2002 10:21 AM
Subject: Re: [HACKERS] Postgresql backend to perform vacuum automatically


> Also I think that each database should be vaccuumed sequentially and also
> vacuum should check whether another instance is already at work.
>
> I don't know whether a time delay should be used anyway. From the tests in
> the ORACLE vs POSTGRESQL thread it appears that 5 sec is the best delay.
> Vacuum should be smart enough to assess the performance gain / processing
> penalty ratio and decide to go forth. So may be the number of update
> threshold should be refined.
> The idea would be to assess how much the vacuum will penalize access to a
> specific database and then what performance improvement we can expect. The
> DBA can then set a ratio to start vacuum.
> The statistic should compute the ratio and set a flag when a vacuum is
> needed. A trigger can be set on the flag field update to start vacuuming.
>
> ----- Original Message -----
> From: "mlw" <markw@mohawksoft.com>
> To: "Bruce Momjian" <pgman@candle.pha.pa.us>
> Cc: "Neil Padgett" <npadgett@redhat.com>; "Tom Lane" <tgl@sss.pgh.pa.us>;
> "Nicolas Bazin" <nbazin@ingenico.com.au>; <pgsql-hackers@postgresql.org>
> Sent: Wednesday, March 06, 2002 8:56 AM
> Subject: Re: [HACKERS] Postgresql backend to perform vacuum automatically
>
>
> > Bruce Momjian wrote:
> > >
> > > Neil Padgett wrote:
> > > > On Tue, 2002-03-05 at 15:59, Bruce Momjian wrote:
> > > > > > > > If they do not affect performance, then why have them off?
> > > > > > >
> > > > > > > I think Jan said 2-3%.  If we can get autovacuum from it, it
> would be a
> > > > > > > win to keep it on all the time, perhaps.
> > > > > >
> > > > > > Assuming that the statistics get updated:
> > > > > >
> > > > > > How often should the sats table be queried?
> > > > > > What sort of configurability would be needed?
> > > > >
> > > > > You could wake up every few minutes and see how the values have
> changed.
> > > > > I don't remember if there is a way to clear that stats so you can
> see
> > > > > just the changes in the past five minutes.  Vacuum the table that
> had
> > > > > activity.
> > > >
> > > > Ick -- polling. The statistics process should be able to wake
somebody
> > > > up / notify the postmaster when the statistics change such that a
> vacuum
> > > > is required.
> > >
> > > Yes, that would tie that stats collector closer to auto-vacuum, but it
> > > certainly could be done.
> >
> > Using an alert can be done, but polling is easier for a proof of
concept.
> I
> > dont see too much difficulty there. We could use notify.
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >
>