From: aymen marouani <marouani.aymen@gmail.com>
To: Craig Ringer <craig@postnewspapers.com.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: Profiling tool for postgres under win32
Date: Mon, 30 Nov 2009 09:42:17 +0100
Message-ID: <bebe9cb90911300042k4691e35j73397d25b884bcc5@mail.gmail.com> (raw)
In-Reply-To: <4B0E6F03.4060902@postnewspapers.com.au>
References: <bebe9cb90911260148l3ce1084ay296e6c59374761c6@mail.gmail.com>
<4B0E6F03.4060902@postnewspapers.com.au>
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@postnewspapers.com.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
>
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: marouani.aymen@gmail.com, craig@postnewspapers.com.au
Subject: Re: Profiling tool for postgres under win32
In-Reply-To: <bebe9cb90911300042k4691e35j73397d25b884bcc5@mail.gmail.com>
* 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