Re: vacuum, performance, and MVCC - Mailing list pgsql-hackers

Ühel kenal päeval, R, 2006-06-23 kell 13:08, kirjutas Tom Lane:
> Csaba Nagy <nagy@ecircle-ag.com> writes:
> >> 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... 
> 
> If you're doing heavy updates of a big table then it's likely to end up
> visiting most of the table anyway, no?  There is talk of keeping a map
> of dirty pages, but I think it'd be a win for infrequently-updated
> tables, not ones that need constant vacuuming.
> 
> I think a lot of our problems in this area could be solved with fairly
> straightforward tuning efforts on the existing autovacuum
> infrastructure.  In particular, someone should be looking into
> recommendable default vacuum-cost-delay settings so that a background
> vacuum doesn't affect performance too much.

One thing that would help updates quite a lot in some scenarios is
keeping the pages only partially-filled, so that most updates could keep
the new version in the same page. I think that has also been discussed
as an option to vacuum and maybe as part of initial inserts. Maybe some
of it even ended up as a todo item.

> Another problem with the
> current autovac infrastructure is that it doesn't respond very well to
> the case where there are individual tables that need constant attention
> as well as many that don't.  If you have N databases then you can visit
> a particular table at most once every N*autovacuum_naptime seconds, and
> *every* table in the entire cluster gets reconsidered at that same rate.
> I'm not sure if we need the ability to have multiple autovac daemons
> running at the same time, 

My patch enabling effective continuous vacuum of fast-update tables,
while still being able to vacuum huge slowly changing ones is still not
applied. Without that patch there is no reason to vacuum the small and
fast changingg tables while vacuum on bigger tables is running, as it
won't clean out dead tuples anyway.

> but we definitely could use something with a
> more flexible table-visiting pattern.  Perhaps it would be enough to
> look through the per-table stats for each database before selecting the
> database to autovacuum in each cycle, instead of going by "least
> recently autovacuumed".
> 
> Bottom line: there's still lots of low-hanging fruit.  Why are people
> feeling that we need to abandon or massively complicate our basic
> architecture to make progress?

Maybe we could start from reusing the index tuples which point to
invisible tuples ? The index is not MVCC anyway, so maybe it is easier
to do in-place replacement there ?

This probably has the same obstacles which have prevented us from
removing those in the first place (removing instead of marking as
invisible). Does it cause some locking issues ? Or does it go against
some other constraints of our index lookups ?

I think that just setting the invisible bit in an index leaf node causes
nearly as much disk io as removing the node.

If we could delete/reuse old index tuples, it would solve a sizable
chunk of index-growth problem, especially for cases where referenced key
value does not change.

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