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

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             |lock
wait   |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 often see posts on this list that overlook the fact that postgresql is
a "relational database system" and also "an SQL system". Relational
systems are meant to be both "complete" and "reflexive". That is, the
query language (SQL) should suffice to do _any_ task needed. And any
meta-information about the system itself should be available and manageable
through the query language.

That is why we have system catalogs describing things like columns, tables,
types, indexes etc. The system maintains its metadata by doing queries and
updates to the catalogs in the same way that a user can query the catalogs.
This reflexivity is the main reason relational systems have such power.

So, whenever you are thinking about managing information related to a
database system, think about using the system itself to do it.  Managing
information is what database systems are _for_. That is, if the current
SQL facilities cannot implement your feature, extend the SQL system,
don't invent some other _kind_ of facility.


The observation that Apache provides status in HTML means that the Apache
team _understand_ that *Apache is a web server*. The natural form of
interaction with a web server is HTML.

Postgres is a SQL database server. The natural form of interaction with
a database server is relational queries and tuples.


Sorry if this is a bit of a rant, but I really think we will have a much
better system if we understand what our system _is_ and try to extend it
in ways that make it better at that rather than to let it go all shapeless
and bloated with unrelated features and interfaces.


-dg

David Gould            dg@illustra.com           510.628.3783 or 510.305.9468
Informix Software  (No, really)         300 Lakeside Drive  Oakland, CA 94612
If simplicity worked, the world would be overrun with insects.