From: | aymen marouani <marouani(dot)aymen(at)gmail(dot)com> |
---|---|
To: | Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Profiling tool for postgres under win32 |
Date: | 2009-11-30 08:42:17 |
Message-ID: | bebe9cb90911300042k4691e35j73397d25b884bcc5@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Ringer | 2009-11-30 09:00:30 | Re: Profiling tool for postgres under win32 |
Previous Message | A. Kretschmer | 2009-11-26 12:26:37 | Re: How to start the "auto_explain" module |