Re: Reporting wait events for system processes - Mailing list pgsql-hackers

On Thu, Oct 13, 2016 at 3:46 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:
> I think that it would be fine to name it, say pg_stat_system_activity,
> and to define it as a system view fed by a SRF function that scans the
> arrays of PGPROC entries for background workers, autovacuum workers
> and system processes.
>
> Thoughts about that? I am expecting a couple of -1 for suggesting to
> expose the system PIDs now only local to postmaster.c ;)

Looking more into that, exposing the list of PIDs from the postmaster
may not be mandatory... The best way to do things would be to use the
existing AuxiliaryProcs, removing its NON_EXEC_STATIC declaration and
marking it as an extern variable in proc.h. The number of entries in
AuxiliaryProcs is now capped by NUM_AUXILIARY_PROCS, and each
auxiliary process started uses InitAuxiliaryProcess() to choose its
PGPROC entry via a liner search.

But the problem is that the order of those PGPROC is undefined. So in
order to make that ordered, I think that we could replace
NUM_AUXILIARY_PROCS by NUM_AUXPROCTYPES, and save in shared memory one
PGPROC entry per auxiliary process using AuxProcType as ordering
system. That will waste shared memory because not all the PGPROC slots
would be used all the time, but this way the user could know what are
the system processes running or not via SQL, which is actually a win
for this case in terms of visibility.

For non-EXEC_BACKEND procs, extending InitAuxiliaryProcess() with an
argument to define AuxProcType is no big deal because the type of the
process to start is known. The problem is for EXEC_BACKEND processes,
in SubPostmasterMain() exactly, where we would need to look for -x%d
in the list of arguments *before* calling AuxiliaryProcessMain() to
choose the appropriate PGPROC slot to set up MyProc for this process.

If all the process PIDs in postmaster.c are exposed though, there is
no need to do anything like that. We could just scan AuxiliaryProcs
one by one and check to which system process PID an entry matches,
then save into the system view a process name according to the match.
That's less performant, but there are not many auxiliary processes,
and the user will only now about active processes that are the ones
filling in the PGPROC slots in AuxiliaryProcs.

Extending AuxiliaryProcs with one entry per AuxProcType looks cleaner
to me, even if it involves one hack for the EXEC_BACKEND case.

Thoughts are welcome, one approach or the other have their cost in
terms of effort. So I am not convinced that it is worth spending time
on implementing something as long as nothing is decided regarding the
way to implement that, if being able to look into wait events for
system processes is wanted of course.

An extra problem is related to the syslogger, we could just add a
AuxProcType to show up its wait events.
Thanks,
-- 
Michael