Re: [PROPOSAL] VACUUM Progress Checker.

On 10 August 2015 at 17:50, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> wrote:

> Masahiko Sawada wrote:
>
> > This topic may have been already discussed but, why don't we use just
> > total scanned pages and total pages?
>
> Because those numbers don't extrapolate nicely. If the density of dead
> tuples is irregular across the table, such absolute numbers might be
> completely meaningless: you could scan 90% of the table without seeing
> any index scan, and then at the final 10% be hit by many index scans
> cleaning dead tuples. Thus you would see progress go up to 90% very
> quickly and then take hours to have it go to 91%. (Additionally, and a
> comparatively minor point: since you don't know how many index scans are
> going to happen, there's no way to know the total number of blocks
> scanned, unless you don't count index blocks at all, and then the
> numbers become a lie.)
>
> If you instead track number of heap pages separately from index pages,
> and indicate how many index scans have taken place, you have a chance of
> actually figuring out how many heap pages are left to scan and how many
> more index scans will occur.
>

I think this overstates the difficulty.

Autovacuum knows what % of a table needs to be cleaned - that is how it is
triggered. When a vacuum runs we should calculate how many TIDs we will
collect and therefore how many trips to the indexes we need for given
memory. We can use the VM to find out how many blocks we'll need to scan in
the table. So overall, we know how many blocks we need to scan.

I think just storing (total num blocks, scanned blocks) is sufficiently
accurate to be worth holding, rather than make it even more complex.

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services