Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 578932E0175 for ; Thu, 17 Jan 2008 13:43:38 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 77136-04 for ; Thu, 17 Jan 2008 13:43:28 -0400 (AST) Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.224]) by postgresql.org (Postfix) with ESMTP id 875DB2E0037 for ; Thu, 17 Jan 2008 13:43:28 -0400 (AST) Received: by wr-out-0506.google.com with SMTP id 58so292221wri.10 for ; Thu, 17 Jan 2008 09:43:27 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; bh=EzYxSZyRyhIuf+Xmo0oublzoH106oRX2twl/cPHKzAU=; b=fKaJkSehbwsohOwaSVMk2JLBEUafgYbKm6McFjNxsudxQ3QcoYfGsNdpKkRQrJqdQksc/63CcYdY1Hojm7sGBD5zAmjRrsDVNmRNhtYZj7bCkPHPn7gpHBtKOkvC6Gp+mftfMHdWkASi0xJztH8Hlc9Dk1wtKym1V2yPZPOuezc= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:mime-version:content-type; b=Xw9ofMG2OCYL5beUVWEQMh6uUq6uSRCCpiXb4I1NY7W7HmxJeHiJ889/y5mLZulXdOvu0AuPnse8riGnjKh3u4XwteUQMrOOYClhBEcaeGgehl99SGdIzZdDBgkheY2Cd7DMq1anjZVQGvpZ+MZp4mrekcmmjniLQLuDFQr5iEI= Received: by 10.151.13.7 with SMTP id q7mr651104ybi.13.1200591807209; Thu, 17 Jan 2008 09:43:27 -0800 (PST) Received: by 10.35.65.11 with HTTP; Thu, 17 Jan 2008 09:43:27 -0800 (PST) Message-ID: Date: Thu, 17 Jan 2008 18:43:27 +0100 From: "Jerome Blomart" To: pgsql-admin@postgresql.org Subject: pgsql and net-snmp MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1309_30575124.1200591807162" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/204 X-Sequence-Number: 27978 ------=_Part_1309_30575124.1200591807162 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hello, **** I really should ask if i am mailing to the correct mailing list in regarding the question i will ask. **** But ... I am working for a small company offering IT services. We are in the acceptance stage for a migration of an old UNIX app managing a huge load of data. This acceptance environment is using a VMWARE ESX platform, a jboss as application server ( java servlet as client application ) and a pgsql database. As the developpers knew there will be a lot of data, the server specs are quite high... ( on RAM space for instance... jboss + pgsql on the same server ). As it is ... this environement gets poor results ... This could be a lot of things and as i am charged of the applications monitoring.... How-to monitor pgsql ? ( considering the server's general statistics and events are already covered : cpu usage, memory usage, shutdown and reboot events, unplanned down time ) - i have got a net-snmp at service - i have got an event listener too ( traplistener + event-rules) After looking through the pgsql manual and google ... - i want base info on the pgsql processes: cpu usage, memory usage and so on... - i want base healthiness information on the database lets get to pgsql... - base processes information: ------------------------------------- As i have understood it pgsql runs as as a single process and forks itself for each clients that connects to the database. As i have seen the number of starting processes is set into pgsql. ( when there is no connection to the server i see six process postgres in idle mode ) The pgsql client processes names are giving informations on themselves: using egrep and ps: #ps aux | egrep ".*postgres: .* .*:ffff:.*\..*\..*\..*:.*" returns a list of pgsqlclient processes net-snmp ? as i look into monitoring pgsql client processes i have written a shell/awk script returning little data on those. #./pgsql.cli.mon $ [ number of processes ] $ [ min cpu usage % ] $ [ average cpu usage % ] $ [ max cpu usage % ] $ [ min mem usage % ] $ [ average mem usage % ] $ [ max mem usage % ] $ [ min virtual mem ] $ [ average virtual mem ] $ [ max virtual mem ] $ [ min real mem ] $ [ average real mem ] $ [ max real mem ] and by feeding it into snmpd.conf (exec statement) i get those data through snmp The maximum and minimum number of processes allowed are already managed by the net-snmp base agent. *** That is a real tiny base for some performance testing. *** The script should be rewritten in an other language ( perl ): *** making sums and averages of doubles in shell lead me ( as too often... ) use pipes and awk.... - database healthiness ? ------------------------------ For that part i have only google'd and looked into the pgsql manual ( and extracted a "SELECT * from pg_stats" from pgsql ). I could use an extraction script that maps selected data into snmp ( as a have done for the processes informations ). But using such a way to pack pgsql's pg_stats into snmp will include regular query to this table. Is there any performance issues generated by this type of monitoring ? As i have not found any pgsql monitoring agent using snmp (yet) , i am trying to gather any data concerning pgsql and feed it to snmp. But i always feel like i have not looked enough for anything. Is there any snmp agent, for pgsql ? If i gather statistics from pg_stats, is there any values/properties, specific to pgsql that i should look into ? ------=_Part_1309_30575124.1200591807162 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hello,

**** I really should ask if i am mailing to the correct mailing list in regarding the question i will ask.
**** But ...

I am working for a  small company  offering IT services.
We are in  the acceptance stage  for a  migration of an old UNIX app managing a huge load of data.

This acceptance environment is using a VMWARE ESX platform, a jboss as application server ( java servlet as client application )
and a pgsql database. As the developpers knew there will be a lot of data, the server specs are quite high...
( on RAM space for instance... jboss + pgsql on the same server ).

As it is ... this environement gets poor results ...

This could be a lot of things and as i am charged of the applications monitoring....

How-to monitor pgsql ?
( considering the server's general statistics and events are already covered :
cpu usage, memory usage,  shutdown and reboot events, unplanned down time )

- i have got a net-snmp at service
- i have got an event listener too ( traplistener + event-rules)

After looking through the pgsql manual and google ...
- i want base info on the pgsql processes: cpu usage, memory usage and so on... 
- i want base healthiness information on the database

lets get to pgsql...

- base processes information:
-------------------------------------
As i have understood it pgsql runs as as a single process and forks itself for each clients that connects to the database.
As i have seen the number of starting processes is set into pgsql.
( when there is no connection to the server i see six process postgres in idle mode )
The pgsql client processes names are giving informations on themselves:

using egrep and ps:
   #ps aux | egrep ".*postgres: .* .*:ffff:.*\..*\..*\..*:.*"
   returns a list of pgsqlclient processes

net-snmp ?
   as i look into monitoring pgsql client processes i have written a shell/awk script returning little data on those.
   #./pgsql.cli.mon
   $ [ number of processes ]
   $ [ min cpu usage % ]
   $ [ average cpu usage % ]
   $ [ max cpu usage % ]
   $ [ min mem usage % ]
   $ [ average mem usage % ]
   $ [ max mem usage % ]
   $ [ min  virtual mem  ]
   $ [ average virtual mem ]
   $ [ max virtual mem ]
   $ [ min real mem ]
   $ [ average real mem ]
   $ [ max real mem ]
   and by feeding it into snmpd.conf (exec statement) i get those data  through snmp
 
The maximum and minimum number of processes allowed are already managed by the net-snmp base agent.

*** That is a real tiny base for some performance testing.
*** The script should be rewritten in an other language ( perl ):
***    making sums and averages of doubles in shell lead me ( as too often... ) use pipes and awk....
 

- database healthiness ?
------------------------------
For that part i have only google'd  and looked into the pgsql manual (  and extracted a "SELECT * from pg_stats" from pgsql ).
I could use an extraction script that maps selected data into snmp ( as a have done for the processes informations ).
But using such a way to pack pgsql's pg_stats into snmp will include regular query to this table.

Is there any performance issues generated by this type of monitoring ?



As  i have not found any pgsql monitoring agent using snmp (yet) ,  i am trying to gather any data concerning pgsql and feed it to snmp.
But i always feel like i have not looked enough for anything.

Is there any snmp agent, for pgsql ? If i gather statistics from pg_stats, is there any values/properties, specific to pgsql that i should look into  ?

 
 



------=_Part_1309_30575124.1200591807162--