Thread: Performance Monitoring of PostGRE

On Wed, 2011-07-06 at 14:27 +0530, BangarRaju Vadapalli wrote:
>    We want to monitor the performance of PostGRE database

I am not aware of a database called PostGRE.
--
Devrim GÜNDÜZ
Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz

In response to BangarRaju Vadapalli <BangarRaju.Vadapalli@infor.com>:

> Hi Everybody,
>
>    We want to monitor the performance of PostGRE database. Could anyone please suggest any tools tried/working
successfully...
>
>
> 1.       We want AWR kind of report in Postgres

You shouldn't use common acronyms in a question like this.  I have no idea
what AWR means, and a google search didn't turn up anything related to
databases.

On a wild guess, check out pgfouine.  I've found that it does an
excellent job of reporting on what the DB is up to.

> 2.       We would like to do CPU Utilization monitoring based on the postgres processes

I don't know of any PostgreSQL-specific tools for this, and it wouldn't
(in my opinion) really make any sense anyway.  This is the OS's job, and
(depending on your OS) there are lots of tools available on the OS side.
You didn't mention what OS you're using, so I can't really suggest much.

FYI, to follow up on another post.  "postgre" is not really an acceptable
shortening of PostgreSQL.  "postgres" is generally accepted, although there
seem to be some people who dislike that as well.  "PG" or "PGSQL" also seem
to be generally accepted shortenings of the name, but for some reason
there seem to be a lot of people who dislike "postgre".

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

In response to Bobby Dewitt <Bdewitt@appriss.com>:

> I think by AWR he is referring to Oracle's Automatic Workload Repository.  It automatically gathers information on
waitevents, object usage, session and system statistics, etc.  It also provides a nice report of what is going on in
yourdatabase and can help identify bottlenecks that may be causing performance issues.  I'm still new to PostgreSQL,
butI don't believe there is anything available like this. 
>
> As far as monitoring database availability goes, I'm working on a plugin for Oracle's OEM (Oracle Enterprise Manager)
thatmonitors if the server is up or down, if there are any blocking sessions, and what percentage of max_connections is
beingused.  It sends alerts for these events based on different thresholds.  However, I'm still in the beginning stages
ofdevelopment and it probably won't be available for a few months. 
>
> I've heard of others using Nagios to monitor PostgreSQL, and EnterpriseDB is supposedly building an OEM type tool but
itwon't be available until later this year. 
>
> Monitoring PostgreSQL has been a big issue for us since beginning to migrate from Oracle, so if anyone else has any
experiencewith this I would love to hear other suggestions. 

Most of our monitoring is done through Nagios and Cacti by extracting data
from log files or pg_stat_activity, pg_locks and other system tables.  It
takes a bit of know-how to know what tables to get the data you want from,
and a comprehensive monitoring tool would definitely make it easier on
newbies.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

On 07/06/11 6:42 AM, Bill Moran wrote:
> FYI, to follow up on another post.  "postgre" is not really an acceptable
> shortening of PostgreSQL.  "postgres" is generally accepted, although there
> seem to be some people who dislike that as well.  "PG" or "PGSQL" also seem
> to be generally accepted shortenings of the name, but for some reason
> there seem to be a lot of people who dislike "postgre".

indeed, since the etymology(?) of the name is Ingres -> Postgres ->
PostgreSQL



--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast

On Jul 6, 8:44 am, wmo...@potentialtech.com (Bill Moran) wrote:

>
> > Monitoring PostgreSQL has been a big issue for us since beginning to migrate from Oracle, so if anyone else has any
experiencewith this I would love to hear other suggestions. 
>
> Most of our monitoring is done through Nagios and Cacti by extracting data
> from log files or pg_stat_activity, pg_locks and other system tables.  It
> takes a bit of know-how to know what tables to get the data you want from,
> and a comprehensive monitoring tool would definitely make it easier on
> newbies.
>

Apologies for the vendor promotion - but it's on point: LogicMonitor
has pretty comprehensive postgres monitoring. It does similar things -
getting data from the system tables - but it automatically discovers
all databases, shows data for all them, graphs and trends, and "knows"
quite a bit about Postgres, so it removes the need for the bit of know-
how on the users part to get effective monitoring and alerting.
http://www.logicmonitor.com/monitoring/databases/postgres-monitoring/

(Of course, also monitors all the standard OS stuff (CPU, swap rate,
etc) and non-standard stuff.)

>> Monitoring PostgreSQL has been a big issue for us since beginning to
>> migrate from Oracle, so if anyone else has any experience with this I
>> would love to hear other suggestions.
>
> Most of our monitoring is done through Nagios and Cacti by extracting data
> from log files or pg_stat_activity, pg_locks and other system tables.  It
> takes a bit of know-how to know what tables to get the data you want from,
> and a comprehensive monitoring tool would definitely make it easier on
> newbies.

I think pgwatch from Cybertec resembles the Oracle management console a
bit. Sure, it's not that advanced and it does not know how to analyze
the collected data, but it's something to start with - see this

  http://www.cybertec.at/en/pgwatch

I guess it's almost the same as the Nagios plugin, just without Nagios.

A long time ago I've started a project to do something like this
(collect and analyze the data) - it's called pgmonitor and it's
available on SourceForge:

  http://pgmonitor.sf.net.

I've been working on it occasionally (and the patch I've submitted a
related patch in January), but the development stagnates recently. So if
there anyone who'd like to cooperate on that with me (bring some new
ideas, code a bit, design a better UI, whatever), let me know.

regards
Tomas

On Wednesday 06 July 2011 14:27:53 BangarRaju Vadapalli wrote:
>    We want to monitor the performance of PostGRE database. Could anyone
> please suggest any tools tried/working successfully...

Munin will graph some usefull postgres stats. It's easy enough to graph
another datapoint by creating a new plugin if you need to (for example, in
addition to the global stats we graph the size of some specific tables).

Pgfouine will create an aggregated report of all your queries, provided you've
setup postgre swith sufficient logging.

Explain analyze is you friend for individual queries.


http://munin.projects.linpro.no/
http://pgfouine.projects.postgresql.org/
http://www.postgresql.org/docs/current/static/sql-explain.html


--
Vincent de Phily