Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id A5503634002 for ; Mon, 30 Nov 2009 04:43:57 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 03945-01-3 for ; Mon, 30 Nov 2009 08:43:42 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-bw0-f213.google.com (mail-bw0-f213.google.com [209.85.218.213]) by mail.postgresql.org (Postfix) with ESMTP id 06F64633876 for ; Mon, 30 Nov 2009 04:42:19 -0400 (AST) Received: by bwz5 with SMTP id 5so2332971bwz.3 for ; Mon, 30 Nov 2009 00:42:17 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type; bh=jxAo9f34Rr40e4e3pEo7T5tvAJwGbvqB1L1dGJzoZQ4=; b=bxZ66fnfj/nMj64Ud0rcDfKP0sVEJdiV887fZZLm1KeQNNIbHELzwTF4tMk4EOon3F GKKrhlr4JdA3Ul4H3Mid9Cj3c9vJmQMKs81Kpuze0W0Ss73TfPKV8YsF/jDcZ/klA/1d oXYUFkFmB3gXJCadbHhCvot0FsJoQQzBE+WJs= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=k7E9FHLajDYVUSHJioH1LkARWsm7m5pNgnk8pH02nul6k4ZK3Ovdwee2ektT6hpmoa V+6s9hI07MKKn+AJSLphLPnEe1QS7Jvjhrwhn0qMFjspXxjhdcXyfn3iY5m2nWrSQT/+ d7P4ZNUTXvI8Eu4WHJerjXZ/xo+vbV+o7Ymv0= MIME-Version: 1.0 Received: by 10.204.154.216 with SMTP id p24mr4281427bkw.16.1259570537285; Mon, 30 Nov 2009 00:42:17 -0800 (PST) In-Reply-To: <4B0E6F03.4060902@postnewspapers.com.au> References: <4B0E6F03.4060902@postnewspapers.com.au> Date: Mon, 30 Nov 2009 09:42:17 +0100 Message-ID: Subject: Re: Profiling tool for postgres under win32 From: aymen marouani To: Craig Ringer Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=0015175cfd8471c99f0479929ef5 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.793 tagged_above=-10 required=5 tests=AWL=0.805, BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200911/78 X-Sequence-Number: 33429 --0015175cfd8471c99f0479929ef5 Content-Type: text/plain; charset=ISO-8859-1 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 > 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 > --0015175cfd8471c99f0479929ef5 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
Hi,
I'= ;m sorry and thanks for the help, concerning your question :

"Out of interest, how are you ta= lking to PostgreSQL? Hand-written SQL via
JDB= C? 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@postnewspaper= s.com.au>
On 26/11/2009 5:48 PM, aymen marouani wro= te:
> Hi,
> I'm using the Postgres database system version 8.3 for a J2EE appl= ication.
> I'd =A0like to profile and monitor in "real time" the st= atus of my queries
> because I notice some slow loading.
> Can anyone tell how to get a "good" profiling tool for the P= ostgres
> 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 progr= am
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 querie= s 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 trol= l
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

--0015175cfd8471c99f0479929ef5--