
> > > > 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.

I cannot envision querying the stats every 4 seconds, especially if the stats 
thread already has most of the info in hand. 

I still think, that for best results the vacuums should happen continuously
for single pages based on a hook in wal or the buffer manager. Do I remember 
correctly, that the active page (the one receiving the next row) already has 
a strategy for slot reuse ? Maybe this strategy should be the followed more
aggressively ?

Seems the worst case is a few row table that permanently get updated,
it should be possible to harness this situation with above method.

Andreas

Wed, 6 Mar 2002 09:35:16 +0100
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
Re: Postgresql backend to perform vacuum automatically
Zeugswetter Andreas SB SD 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.
> 
> I cannot envision querying the stats every 4 seconds, especially if the stats
> thread already has most of the info in hand.
> 
> I still think, that for best results the vacuums should happen continuously
> for single pages based on a hook in wal or the buffer manager. Do I remember
> correctly, that the active page (the one receiving the next row) already has
> a strategy for slot reuse ? Maybe this strategy should be the followed more
> aggressively ?
> 
> Seems the worst case is a few row table that permanently get updated,
> it should be possible to harness this situation with above method.

Perhaps the statistics thread can trigger a semaphore when it sees that vacuum
needs to be run?

Wed, 06 Mar 2002 07:12:09 -0500
mlw <markw@mohawksoft.com>
Re: Postgresql backend to perform vacuum automatically
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
> I still think, that for best results the vacuums should happen continuously
> for single pages based on a hook in wal or the buffer manager.

Not possible unless you are willing to have SELECTs grab much stronger
locks than they do now (viz, the same kind of lock that VACUUM does).

			regards, tom lane

Wed, 06 Mar 2002 11:11:52 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Postgresql backend to perform vacuum automatically 
Tom Lane wrote:
> "Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
> > I still think, that for best results the vacuums should happen continuously
> > for single pages based on a hook in wal or the buffer manager.
> 
> Not possible unless you are willing to have SELECTs grab much stronger
> locks than they do now (viz, the same kind of lock that VACUUM does).

Remember, you can't recycle the page until all transactions are done
seeing it as active.  I think something periodic will do the job just
fine.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 6 Mar 2002 12:44:29 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Postgresql backend to perform vacuum automatically