Re: Quick idea for reducing VACUUM contention

ITAGAKI Takahiro wrote:
> "Simon Riggs" <simon(at)2ndquadrant(dot)com> wrote:
>
> > Read the heap blocks in sequence, but make a conditional lock for
> > cleanup on each block. If we don't get it, sleep, then try again when we
> > wake up. If we fail the second time, just skip the block completely.

It would be cool if we could do something like sweep a range of pages,
initiate IO for those that are not in shared buffers, and while that is
running, lock and clean up the ones that are in shared buffers, skipping
those that are not lockable right away; when that's done, go back to
those buffers that were gotten from I/O and clean those up. And retry
the locking for those that couldn't be locked the first time around,
also conditionally. And when that's all done, a third pass could get
those blocks that weren't cleaned up in none of the previous passes (and
this time the lock would not be conditional).

Then do a vacuum_delay sleep.

> When we allow some skips in removing dead tuples, can we guarantee
> pg_class.relfrozenxid?

No we can't.

> I think we might need additional "freezing-xmax" operations to avoid
> XID-wraparound in the first path of vacuum, though it hardly occurs.

I'm not sure I follow. Can you elaborate? Do you mean storing a
separate relfrozenxmax for each table or something like that?

> It might be a future topic ... if we are in the direciton of
> "optimistic sweeping", is it possible to remove the second path of vacuum
> completely? We just add XID of the vacuum to dead tuples we see in the
> first path. When backends find a dead tuple and see the transaction
> identified by XID in it has commited, they can freely reuse the area of
> the dead tuple because we can assume index entries pointing the tuple
> have been removed by the vacuum.

I would be worried about leftover index entries being later used by new
tuples in the heap. Then when you visit the index, find that entry, go
to the heap and find the new tuple and return it, which could be bogus.
(Unless, I think, you check in the index when you are going to insert
the new index tuple -- if the CTID is already used, reuse that entry or
remove it before insertion).

I don't know. Maybe it's OK but it seems messy even if it is.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.