Re: Background Processes and reporting

On Tue, Mar 15, 2016 at 1:32 AM, Andres Freund <andres(at)anarazel(dot)de> wrote:
>
> On 2016-03-12 16:29:11 +0530, Amit Kapila wrote:
> > On Sat, Mar 12, 2016 at 3:10 AM, Andres Freund <andres(at)anarazel(dot)de>
wrote:
> > >
> > >
> > > > Similarly for the wait event stuff - checkpointer, wal writer,
> > > > background writer are in many cases processes that very often are
> > > > blocked on locks, IO and such. Thus restricting the facility to
> > > > database connected processes seems like a loss.
> > >
> > > I think one way to address this would be to not only report
> > > PgBackendStatus type processes in pg_stat_activity. While that'd
> > > obviously be a compatibility break, I think it'd be an improvement.
> > >
> >
> > I think here another point which needs more thoughts is that many of the
> > pg_stat_activity fields are not relevant for background processes,
ofcourse
> > one can say that we can keep those fields as NULL, but still I think
that
> > indicates it is not the most suitable way to expose such information.
>
> But neither are all of them relevant for autovacuum workers, and we
> already show them.
>

Right, currently any process which has been assigned BackendId is probable
candidate for being displayed in pg_stat_activity and all the relative
information is being captured in PGBackendStatus.

> pg_stat_activity as a name imo doesn't really imply
> that it's about plain queries. ISTM we should add a 'backend_type'
> column that is one of
backend|checkpointer|autovacuum|autovacuum-worker|wal writer| bgwriter|
bgworker
> (or similar). That makes querying easier.
>

+1 for going that way if we decide to display background process
information in pg_stat_activity view. However I think we might need some
additional space in shared memory to track some of the statistics as we
track in PGBackendStatus or may be for now just display some minimal stats
like wait events for background processes.

>
> > Another way could be to have new view like pg_stat_background_activity
with
> > only relevant fields or try expose via individual views like
> > pg_stat_bgwriter.
>
> I think the second is a pretty bad alternative; it'll force us to add
> new views with very similar information; and it'll be hard to get
> information about the whole system. I mean if you want to know which
> locks are causing problems, you don't primarily care whether it's a
> background process or a backend that has contention issues.
>

Agreed. OTOH adding information from two different kind of processes (one
which has Backendid and one which doesn't have) also doesn't sound to be
neat from the internal code perspective, but may be this is just an initial
fear or may be because we haven't comeup with a patch which can show it is
actually a simple thing to achieve.

Yet another idea could be for 9.6, lets just define statistics functions to
get wait events for background process like we have for backends (similar
to pg_stat_get_backend_idset() and pg_stat_get_backend_wait_event()). I
think we most probably anyway need those kind of functions once we expose
such information for background processes, so having them now will at least
provide someway to user to have some minimal information about background
processes. I think that won't need much additional work.

>
> > Do you intend to get this done for 9.6 considering an add-on patch for
wait
> > event information displayed in pg_stat_activity?
>
> I think we should fix this for 9.6; it's a weakness in a new
> interface. Let's not yank people around more than we need to.
>
> I'm willing to do some work on that, if we can agree upon a course.
>

Good to hear.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com