Re: Profiling tool for postgres under win32

Hi,
I'm sorry and thanks for the help, concerning your question :

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

I'm using JPA/Toplink to talk to the PostgresSQL, and I asked my question
about profiling because of a slow simple query

"SELECT i FROM Item i"

which takes 4s to execute.

Cordially and best regards.

2009/11/26 Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>

> 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
>