Re: Resume vacuum and autovacuum from interruption and cancellation

On Tue, 16 Jul 2019 at 13:57, Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> On Wed, Jun 12, 2019 at 1:30 PM Masahiko Sawada <sawada.mshk@gmail.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.

A few minor things I noticed in the first look,
+/*
+ * 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.

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

--
Regards,
Rafia Sabih