Message-ID: <4B0E6F03.4060902@postnewspapers.com.au>
Date: Thu, 26 Nov 2009 20:05:23 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB;
	rv:1.9.1.1) Gecko/20090715 Thunderbird/3.0b3
MIME-Version: 1.0
To: aymen marouani <marouani.aymen@gmail.com>
CC: pgsql-sql@postgresql.org
Subject: Re: Profiling tool for postgres under win32
References: <bebe9cb90911260148l3ce1084ay296e6c59374761c6@mail.gmail.com>
In-Reply-To: <bebe9cb90911260148l3ce1084ay296e6c59374761c6@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

On 26/11/2009 5:48 PM, aymen marouani wrote:
> Hi,
> I'm using the Postgres database system version 8.3 for a J2EE application.
> I'd  like to profile and monitor in "real time" the status of my queries
> because I notice some slow loading.
> Can anyone tell how to get a "good" profiling tool for the Postgres
> database system ?

I'm not aware of any tools that connect to the database to provide
profiling and monitoring.

What the right course of action is depends on what sort of slowdown
you're encountering. If it's particular activities within the program
that're a problem, then you should probably use logging in your
application to record database activity (including query runtimes) to
see what exactly it's doing.

If it's unpredictable slowdowns in operations that are normally fast,
then you need to look at the database end. Look into locking issues (
see: pg_catalog.pg_locks ), table bloat and VACUUM / autovacuum, and the
possibility of simple concurrent load spikes ( see
pg_catalog.pg_stat_activity ).

Typically what you'll do if you're seeing unpredictably slow queries is
use log_min_duration to log problem statements to the PostgreSQL log,
which you can then analyse. If you enable CSV logging, you can pull the
PostgreSQL log into a database, spreadsheet, or whatever for easier
analysis.

The new auto_explain module in 8.4 is excellent and very helpful too,
since it can help you find out what plans were used to execute problem
queries easily and conveniently.

Of course, this won't help you much if your problem is an application
issuing *huge* numbers of very small queries. You can set Pg to log
every query, but you'll rapidly have an apalling amount of data to troll
through, and it's hard to know which ones are related to user-perceived
slowdowns. Application logging is usually a better option for tracking
this sort of thing down.

Out of interest, how are you talking to PostgreSQL? Hand-written SQL via
JDBC? Hibernate / TopLink / some other ORM (possibly via JPA)?

If you're using Hibernate, just enable its query logging features via
log4j and watch what happens.

--
Craig Ringer