Re: vacuum, performance, and MVCC

Ühel kenal päeval, R, 2006-06-23 kell 18:05, kirjutas Csaba Nagy:
> > > > Usually it gets really bad if you *don't* run vacuum continuously, maybe
> > > > hopeing to do it in slower times at night. For high-update db you have
> > > > to run it continuously, maybe having some 5-15 sec pauses between runs.
> > >
> > > And how much I/O does this take?
> >
> > Surprisingly its mostly WAL traffic, the heap/index pages themselves are
> > often not yet synced to disk by time of vacuum, so no additional traffic
> > there. If you had made 5 updates per page and then vacuum it, then you
> > make effectively 1 extra WAL write meaning 20% increase in WAL traffic.
>
> Is this also holding about read traffic ? I thought vacuum will make a
> full table scan... for big tables a full table scan is always badly
> influencing the performance of the box. If the full table scan would be
> avoided, then I wouldn't mind running vacuum in a loop...

I was referring to a design that keeps frequently updated tuples in a
separate table.

> In fact I think that it would make sense to replace the whole current
> vacuum stuff with a background thread which does that continuously using
> a dead space map. That could be a heap sorted by tuple deletion time,
> and always cleaned up up to the oldest running transaction's start
> time... there would be no need for any other autovacuum then.

This has been on todo list for some time already.

>
--
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com