Обсуждение: pgsql and net-snmp
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 ?
**** 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 ?
> 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) http://pgfoundry.org/projects/pgsnmpd/ http://pgsnmpd.projects.postgresql.org/ -- Consonance: an Open Source .NET OpenGroupware client. Contact:awilliam@whitemiceconsulting.com http://freshmeat.net/projects/consonance/
On Jan 17, 2008 11:43 AM, Jerome Blomart <j.blomart@gmail.com> wrote: > > 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 ... You don't mention your I/O subsystem or what type of load you're putting on your database. Lots of disks on a fast RAID controller are often needed to get good db performance. You can use some kind of system monitoring tool that will tell you how much wait time you have versus idle versus user CPU versus system CPU usage. > How-to monitor pgsql ? It's a good idea to keep abreast of what's in the logs, especially ERROR and PANIC type messages. > - 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 ? Of course there's some load, but it's a small table, and generally speaking this won't be a noticeable load. Also run occasional vacuum verbose against the main db and email the output to yourself for review. Try to keep an eye on how many dead pages your db is collecting.