Re: pgsql and net-snmp

On Jan 17, 2008 11:43 AM, Jerome Blomart <j(dot)blomart(at)gmail(dot)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.