pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Simon Riggs <simon@2ndquadrant.com>
To: Werner vd Merwe <werner@saicom.co.za>
Cc: pgsql-admin@postgresql.org
Subject: Re: Profiling
Date: Sun, 25 Jul 2004 23:08:35 +0100
Message-ID: <1090793315.3057.1643.camel@localhost.localdomain> (raw)
In-Reply-To: <!~!UENERkVCMDkAAQACAAAAAAAAAAAAAAAAABgAAAAAAAAAUwQqaqKa8EeTFMxLtmNJY8KAAAAQAAAARGQLD/jtXE6H0B79aWFZ1QEAAAAA@saicom.co.za>
References: <!~!UENERkVCMDkAAQACAAAAAAAAAAAAAAAAABgAAAAAAAAAUwQqaqKa8EeTFMxLtmNJY8KAAAAQAAAARGQLD/jtXE6H0B79aWFZ1QEAAAAA@saicom.co.za>

On Fri, 2004-07-23 at 12:23, Werner vd Merwe wrote:

> We are running a PG7.4 database, being accessed through jsp’s called
> through Tomcat. The problem is, that sometimes the database comes to a
> complete standstill, sometimes because of deadlocks, but I would
> venture to say that most times it is because it runs out of resources.
> 

It's fairly common in that kind of a set-up to issue commands that
deadlock against each other.

Worse, its possible to deadlock in your application in ways that the
database can't actually tell its a deadlock, because it can't see the
whole picture. Entity beans are just not good.

Running out of resources is a good indication of this kind of blockage,
which I hasten to add is nothing whatsoever to do with PostgreSQL - this
would occur whichever RDBMS you used.

Look at the table locking sequence in your application and try to avoid
differently ordered sequences. This may require you to redesign your
application to prevent certain page sequences that conflict badly with
the main routes.

You'll need to read up on this a lot more to understand it fully.

> My question is, are there any software or apps out there that can
> monitor CPU, memory and shared buffer usage etc so I can have a
> pro-active way of monitoring when we need to upgrade hardware, or just
> tweak the software. I am aware that running complex queries will take
> a large chunk of CPU time, but it would be good for me to have the
> normal running load of PG.
> 

Check the PostgreSQL manual pages entitled "Monitoring Database
Activity". In light of the above comments, you will find this useful,
but not the answer to your problems.

Best Regards, Simon Riggs




view thread (4+ messages)

Message-ID: <1090793315.3057.1643.camel@localhost.localdomain>
Permalink:  ../1090793315.3057.1643.camel@localhost.localdomain/
Also on:    postgresql.org/message-id/1090793315.3057.1643.camel@localhost.localdomain

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: simon@2ndquadrant.com, werner@saicom.co.za
  Subject: Re: Profiling
  In-Reply-To: <1090793315.3057.1643.camel@localhost.localdomain>
  References: <!~!UENERkVCMDkAAQACAAAAAAAAAAAAAAAAABgAAAAAAAAAUwQqaqKa8EeTFMxLtmNJY8KAAAAQAAAARGQLD/jtXE6H0B79aWFZ1QEAAAAA@saicom.co.za> <1090793315.3057.1643.camel@localhost.localdomain>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox