Re: [PROPOSAL] VACUUM Progress Checker.

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.

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

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.

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/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services