In-Reply-To: 
 <03E7D3E231BB7B4A915A6581D4296CC6015D5352@NSNOVPS00411.nacio.xythos.com>
References: 
 <03E7D3E231BB7B4A915A6581D4296CC6015D5352@NSNOVPS00411.nacio.xythos.com>
Mime-Version: 1.0 (Apple Message framework v733)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <76FC392F-3B3D-409E-A790-1D15B784C03E@fastcrypt.com>
Cc: "Tom Lane" <tgl@sss.pgh.pa.us>,
	<pgsql-performance@postgresql.org>, <pgsql-jdbc@postgresql.org>
Content-Transfer-Encoding: 7bit
From: Dave Cramer <pg@fastcrypt.com>
Subject: Re: [PERFORM] Performance problem using V3 protocol in jdbc driver 
Date: Wed, 17 Aug 2005 08:59:30 -0400
To: Barry Lind <blind@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
>
>