Re: [HACKERS] keeping track of connections - Mailing list pgsql-hackers

On Wed, 3 Jun 1998, David Gould wrote:

> Hal Synder writes:
> >
> > Can backend monitoring be compatible with one or more extant
> > monitoring techniques?
> >
> > 1. syslog
> > 2. HTML (like Apache's real time status)
> > 3. SNMP/SMUX/AgentX
>
> In Illustra, we use (gasp) SQL for this.
>
> > select * from procs;
>
> procc_pid     |proc_xid     |proc_database|proc_locktab |proc_locktid |proc_locktype|proc_prio
|proc_licenseid|proc_status |proc_user    |proc_host           |proc_display |proc_spins   |proc_buffers | 
>
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> |4787         |0            |58.201e      |tables       |(7,0)        |R            |0            |0
|lockwait    |miadmin      |warbler.illustra.com|/dev/pts/4   |[]           |[]           | 
> |3997         |0            |58.201e      |-            |(-1,0)       |             |0            |0
|clientinput |miadmin      |warbler.illustra.com|/dev/pts/11  |[]           |[]           | 
> |29597        |1320638      |58.201e      |-            |(-1,0)       |             |0            |0
|running    *|miadmin      |warbler.illustra.com|/dev/pts/5   |[]           |[]           | 
> |4790         |1320646      |58.7         |-            |(-1,0)       |             |0            |0
|running    *|miadmin      |warbler.illustra.com|/dev/pts/4   |[6]          |[]           | 
> -------------------------------------------------------------------------------
>
> "procs" is a pseudo-table that is generated on the fly from the process
> data structures in the shared memory when queried. There are also
> pseudo-tables for locks and traces and other information.
>
>
> The advantage of using SQL is that the data can be selected into other
> tables, grouped, projected, joined or whatever. The other advantage is
> that all the exiting clients can take advantage of the data. So if you
> wanted to write a graphical status monitor, you could do so quite simply
> in pgtcl.
>
> Illustra also provides a set of prewritten functions (which are just sql
> funcs) to provide convenient access to many kinds of common catalog
> queries.

    I definitely like this...it keeps us self-contained as far as the
data is concerned, and everyone that is using it knows enough about SQL
(or should) to be able to gleam information as required...

    What would it take to do this though?  The 'postmaster' itself,
unless I've misunderstand a good many of the conversations on this, can't
access the tables themselves, only 'flat files' (re: the password issue),
so it would have to be done in the fork'd process itself.  That, IMHO,
would pose a possible inconsequential problem though...what if the backend
dies?  Its 'record' in the proc table wouldn't be removed, which would be
like having our own internal 'process zombies'...

    I think this does bear further discussion though...one 'branch' of
this would be to have a dynamic table for 'live' processes, but also one
that contains a history of past ones...?


Marc G. Fournier
Systems Administrator @ hub.org
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org