Re: Resume vacuum and autovacuum from interruption and cancellation

On Thu, Aug 8, 2019 at 10:42 PM Rafia Sabih <rafia(dot)pghackers(at)gmail(dot)com> wrote:
>
> On Tue, 16 Jul 2019 at 13:57, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> >
> > On Wed, Jun 12, 2019 at 1:30 PM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> > >
> > > Hi all,
> > >
> > > Long-running vacuum could be sometimes cancelled by administrator. And
> > > autovacuums could be cancelled by concurrent processes. Even if it
> > > retries after cancellation, since it always restart from the first
> > > block of table it could vacuums blocks again that we vacuumed last
> > > time. We have visibility map to skip scanning all-visible blocks but
> > > in case where the table is large and often modified, we're more likely
> > > to reclaim more garbage from blocks other than we processed last time
> > > than scanning from the first block.
> > >
> > > So I'd like to propose to make vacuums save its progress and resume
> > > vacuuming based on it. The mechanism I'm thinking is simple; vacuums
> > > periodically report the current block number to the stats collector.
> > > If table has indexes, reports it after heap vacuum whereas reports it
> > > every certain amount of blocks (e.g. 1024 blocks = 8MB) if no indexes.
> > > We can see that value on new column vacuum_resume_block of
> > > pg_stat_all_tables. I'm going to add one vacuum command option RESUME
> > > and one new reloption vacuum_resume. If the option is true vacuums
> > > fetch the block number from stats collector before starting and start
> > > vacuuming from that block. I wonder if we could make it true by
> > > default for autovacuums but it must be false when aggressive vacuum.
> > >
> > > If we start to vacuum from not first block, we can update neither
> > > relfrozenxid nor relfrozenxmxid. And we might not be able to update
> > > even relation statistics.
> > >
>
> Sounds like an interesting idea, but does it really help? Because if
> vacuum was interrupted previously, wouldn't it already know the dead
> tuples, etc in the next run quite quickly, as the VM, FSM is already
> updated for the page in the previous run.

Since tables are modified even during vacuum, if vacuum runs again
after interruption it could need to vacuum the part of table again
that has already been cleaned by the last vacuum. But the rest part of
the table is likely to have more garbage in many cases. Therefore I
think this would be helpful especially for a case where table is large
and heavily updated. Even if the table has not gotten dirtied since
the last vacuum it can skip already-vacuumed pages by looking vm or
the last vacuumed block. I think that it doesn't make thing worse than
today's vacuum in many cases.

>
> A few minor things I noticed in the first look,

Thanks for reviewing the patch.

> +/*
> + * When a table has no indexes, save the progress every 8GB so that we can
> + * resume vacuum from the middle of table. When table has indexes we save it
> + * after the second heap pass finished.
> + */
> +#define VACUUM_RESUME_BLK_INTERVAL 1024 /* 8MB */
> Discrepancy with the memory unit here.
>

Fixed.

> /* No found valid saved block number, resume from the first block */
> Can be better framed.

Fixed.

Attached the updated version patch.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center