Fw: Postgresql backend to perform vacuum automatically

----- Original Message -----
From: "Nicolas Bazin" <nbazin(at)ingenico(dot)com(dot)au>
To: "mlw" <markw(at)mohawksoft(dot)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(at)mohawksoft(dot)com>
> To: "Bruce Momjian" <pgman(at)candle(dot)pha(dot)pa(dot)us>
> Cc: "Neil Padgett" <npadgett(at)redhat(dot)com>; "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>;
> "Nicolas Bazin" <nbazin(at)ingenico(dot)com(dot)au>; <pgsql-hackers(at)postgresql(dot)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(at)postgresql(dot)org
> >
>