Re: [PROPOSAL] VACUUM Progress Checker.

Le 2 juil. 2015 7:28 AM, "Simon Riggs" <simon(at)2ndquadrant(dot)com> a écrit :
>
> On 2 July 2015 at 03:00, Rahila Syed <rahilasyed90(at)gmail(dot)com> wrote:
>
>>
>> >Yes, I suggest just a single column on pg_stat_activity called
pct_complete
>>
>> Reporting remaining time also can be crucial to make decisions regarding
continuing or aborting VACUUM.
>> The same has been suggested in the thread below,
>>
>> http://www.postgresql.org/message-id/13072.1284826206@sss.pgh.pa.us
>>
>> >trace_completion_interval = 5s (default)
>>
>> >Every interval, we report the current % complete for any operation that
supports it. We just show NULL if the current operation has not reported
anything or never will.
>>
>> >We do this for VACUUM first, then we can begin adding other operations
as we work out how (for that operation).
>>
>> Thank you for explaining. This design seems good to me except, adding
more than one columns(percent_complete, remaining_time)
>
>
> It is attractive to have a "remaining_time" column, or a
"predicted_completion_timestamp", but those columns are prediction
calculations rather than actual progress reports. I'm interested in seeing
a report that relates to actual progress made.
>

Agreed.

> Predicted total work required is also interesting, but is much less
trustworthy figure.
>

And it is something a client app or an extension can compute. No need to
put this in core as long as we have the actual progress.

> I think we'll need to get wider input about the user interface for this
feature.
>
>
>>
>> if required to pg_stat_activity can be less user intuitive than having a
separate view for VACUUM.
>
>
> I think it is a mistake to do something just for VACUUM.
>
> Monitoring software will look at pg_stat_activity. I don't think we
should invent a separate view for progress statistics because it will cause
users to look in two places rather than just one. Reporting progress is
fairly cheap instrumentation, calculating a prediction of completion time
might be expensive.
>

+1

> Having said that, monitoring systems currently use a polling mechanism to
retrieve status data. They look at information published by the backend. We
don't currently have a mechanism to defer publication of expensive
monitoring information until requested by the monitoring system. If you
have a design for how that might work then say so, otherwise we need to
assume a simple workflow: the backend publishes whatever it chooses,
whenever it chooses and then that is made available via the monitoring
system via views.
>
>
> Your current design completely misses the time taken to scan indexes,
which is significant.
>
> There might be a justification to put this out of core, but measuring
progress of VACUUM wouldn't be it, IMHO.
>
> --
> Simon Riggs http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services