Re: Resumable vacuum proposal and design overview

On Mon, 2007-02-26 at 18:21 +0900, Galy Lee wrote:

> This implementation accepts stop request at *blocks level* in step 1-4.
>
> D) How to stop and resume
>
> - stop:
>
> When vacuum stop in step 1-4, vacuum perform following things:
> vacuum saves dead tuple list, the heap block number on which it
> stop, unswept index relations, unswept dead tuple and FreezeLimit
> into a disk file.
>
> Free space information collected in step 1-3 can be registered to
> FSM when vacuum is interrupted.
>
> - resume:
>
> When vacuum is resuming, it reads out saved information and skip the
> finished operations, then continue to finish remaining operations.

I understand and agree with the need to reduce the time taken for long
VACUUM operations. Doing VACUUM in stages make a lot of sense to me.

What I'm not sure about is the stop/resume logic you've presented.
Stopping this at *any* delay point means you have to dump the dead tuple
list and reload it again later. That means we can't really trust the
list we are given when we resume - the user may provide an incorrect or
old dead tuple list. If the system manages the dead tuple list we may
need to keep such files around for long periods, which doesn't sound
great either.

ISTM simpler to make the optional stop/restart point be after one full
cycle of cleaning, so exactly at the point where we discard one tuple
list and we move on the next. That way, we have no need to store and
reload the dead tuple list, with all of the complexity that involves.
The only thing we'd need to remember would be the block number that the
last partial VACUUM had reached, which would be simple to store in the
catalog. Restarting VACUUM would then occur from the stored block
number, not from the first block. This approach seems like a
stepping-stone on the way to the full proposal you've suggested, so you
could extend further if still required.

Interrupting a running VACUUM could be made possible by having VACUUM
check for a smart stop request at normal vacuum delay points. This would
be made with a system function: e.g. stop_vacuums(). Once a smart stop
request is received, it would finish the rest of its current cycle
before ending. If in step 1, it would not scan further, but would
complete steps 2 and 3 for the dead tuples harvested so far. The smart
stop request would then be a system-wide request for manually run
VACUUMs to end as soon as possible, because normal operation is about to
re-commence.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com