Re: [PROPOSAL] VACUUM Progress Checker.

On 7/22/15 6:58 AM, Amit Langote wrote:
> On Wed, Jul 22, 2015 at 8:19 PM, Alvaro Herrera
> <alvherre(at)2ndquadrant(dot)com> wrote:
>>
>> Not sure what Jim meant. Maybe he meant to be aware of when spilling to
>> disk happens? Obviously, things become slower, so maybe you need to
>> consider it for progress reporting purposes.
>>
>
> Perhaps the m_w_m determines how many dead tuples lazy_scan_heap() can
> keep track of before doing a lazy_vacuum_indexes() +
> lazy_vacuum_heap() round. Smaller the m_w_m, more the number of index
> scans, slower the progress?

Yes. Any percent completion calculation will have to account for the
case of needing multiple passes through all the indexes.

Each dead tuple requires 6 bytes (IIRC) of maintenance work mem. So if
you're deleting 5M rows with m_w_m=1MB you should be getting many passes
through the indexes. Studying the output of VACUUM VERBOSE will confirm
that (or just throw a temporary WARNING in the path where we start the
scan).
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Data in Trouble? Get it in Treble! http://BlueTreble.com