Re: [PROPOSAL] VACUUM Progress Checker.

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: Robert Haas <robertmhaas(at)gmail(dot)com>, Amit Langote <amitlangote09(at)gmail(dot)com>
Cc: pokurev(at)pm(dot)nttdata(dot)co(dot)jp, Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>, bannos(at)nttdata(dot)co(dot)jp
Subject: Re: [PROPOSAL] VACUUM Progress Checker.
Date: 2016-03-15 05:16:28
Message-ID: 56E79AAC.9010705@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

On 2016/03/15 3:41, Robert Haas wrote:
> On Sat, Mar 12, 2016 at 7:49 AM, Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
>> Instead, the attached patch adds a IndexBulkDeleteProgressCallback
>> which AMs should call for every block that's read (say, right before a
>> call to ReadBufferExtended) as part of a given vacuum run. The
>> callback with help of some bookkeeping state can count each block and
>> report to pgstat_progress API. Now, I am not sure if all AMs read 1..N
>> blocks for every vacuum or if it's possible that some blocks are read
>> more than once in single vacuum, etc. IOW, some AM's processing may
>> be non-linear and counting blocks 1..N (where N is reported total
>> index blocks) may not be possible. However, this is the best I could
>> think of as doing what we are trying to do here. Maybe index AM
>> experts can chime in on that.
>>
>> Thoughts?
>
> Well, I think you need to study the index AMs and figure this out.

OK. I tried to put calls to the callback in appropriate places, but
couldn't get the resulting progress numbers to look sane. So I ended up
concluding that any attempt to do so is futile unless I analyze each AM's
vacuum code carefully to be able to determine in advance the max bound on
the count of blocks that the callback will report. Anyway, as you
suggest, we can improve it later.

> But I think for starters you should write a patch that reports the following:
>
> 1. phase
> 2. number of heap blocks scanned
> 3. number of heap blocks vacuumed
> 4. number of completed index vac cycles
> 5. number of dead tuples collected since the last index vac cycle
> 6. number of dead tuples that we can store before needing to perform
> an index vac cycle
>
> All of that should be pretty straightforward, and then we'd have
> something we can ship. We can add the detailed index reporting later,
> when we get to it, perhaps for 9.7.

OK, I agree with this plan. Attached updated patch implements this.

Thanks,
Amit

Attachment Content-Type Size
0001-WIP-Implement-progress-reporting-for-VACUUM-command-v12.patch text/x-diff 12.9 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Kapila 2016-03-15 05:17:12 Re: Speed up Clog Access by increasing CLOG buffers
Previous Message Amit Kapila 2016-03-15 05:08:41 Re: Prepared Statement support for Parallel query