From: Dave Cramer <pg@fastcrypt.com>
To: Barry Lind <blind@xythos.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [PERFORM] Performance problem using V3 protocol in jdbc driver
Date: Wed, 17 Aug 2005 08:59:30 -0400
Message-ID: <76FC392F-3B3D-409E-A790-1D15B784C03E@fastcrypt.com> (raw)
In-Reply-To: <03E7D3E231BB7B4A915A6581D4296CC6015D5352@NSNOVPS00411.nacio.xythos.com>
References: <03E7D3E231BB7B4A915A6581D4296CC6015D5352@NSNOVPS00411.nacio.xythos.com>
Barry,
One way to do this is to turn logging on for calls over a certain
duration
log_duration in the config file. This will only log calls over n
milliseconds.
There's a tool called iron eye SQL that monitors JDBC calls.
http://www.irongrid.com/
unfortunately I am getting DNS errors from that site right now. I do
have a copy of their code if you need it.
Dave
On 17-Aug-05, at 1:43 AM, Barry Lind wrote:
> That was my suspicion as well, which is why I tried the V2 protocol.
>
> I do not know of any specific queries that are causing the
> problem. As
> I monitor 'top' I see processes utilizing a significant amount of CPU
> running SELECT, UPDATE and DELETE, which would lead me to believe that
> it isn't any one specific query.
>
> How does one identify on a live system specific queries that are
> running
> slow, especially with the V3 protocol and when the system is executing
> about a 100 queries a second (which makes turning on any sort of
> logging
> very very verbose)? (I just subscribed to the performance list, so
> this
> is probably something that has been answered many times before on this
> list).
>
> I haven't tried to track down a performance problem like this
> before on
> postgres. Since most of our large customers run Oracle that is
> where I
> have the knowledge to figure something like this out.
>
> Thanks,
> --Barry
>
>
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Tuesday, August 16, 2005 10:02 PM
> To: Barry Lind
> Cc: pgsql-performance@postgresql.org; pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] Performance problem using V3 protocol in jdbc
> driver
>
>
> "Barry Lind" <blind@xythos.com> writes:
>
>> ... On a hunch I switched the jdbc driver to using the V2 protocol
>> and the load on the machine dropped down to what it was when using
>> Oracle and everything was fine.
>>
>
> First knee-jerk reaction is that it's an optimization problem stemming
> from V3 protocol feeding parameterized queries to the backend where V2
> did not, and the planner being unable to cope :-(
>
> Can you identify the specific queries causing the problem?
>
> regards, tom lane
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>
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-performance@postgresql.org
Cc: pg@fastcrypt.com, blind@xythos.com, tgl@sss.pgh.pa.us, pgsql-jdbc@postgresql.org
Subject: Re: [PERFORM] Performance problem using V3 protocol in jdbc driver
In-Reply-To: <76FC392F-3B3D-409E-A790-1D15B784C03E@fastcrypt.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