Skip site navigation (1) Skip section navigation (2)

Re: [PERFORM] Performance problem using V3 protocol in jdbc driver

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: Barry Lind <blind(at)xythos(dot)com>
Cc: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>,<pgsql-performance(at)postgresql(dot)org>, <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: [PERFORM] Performance problem using V3 protocol in jdbc driver
Date: 2005-08-17 12:59:30
Message-ID: 76FC392F-3B3D-409E-A790-1D15B784C03E@fastcrypt.com (view raw or flat)
Thread:
Lists: pgsql-jdbcpgsql-performance
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(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Tuesday, August 16, 2005 10:02 PM
> To: Barry Lind
> Cc: pgsql-performance(at)postgresql(dot)org; pgsql-jdbc(at)postgresql(dot)org
> Subject: Re: [JDBC] Performance problem using V3 protocol in jdbc  
> driver
>
>
> "Barry Lind" <blind(at)xythos(dot)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
>
>


In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2005-08-17 14:52:48
Subject: Re: Need for speed
Previous:From: Oliver JowettDate: 2005-08-17 12:29:13
Subject: Re: Data Selection Slow From VB 6.0

pgsql-jdbc by date

Next:From: John A MeinelDate: 2005-08-17 15:18:35
Subject: Re: [PERFORM] Data Selection Slow From VB 6.0
Previous:From: Oliver JowettDate: 2005-08-17 12:29:13
Subject: Re: Data Selection Slow From VB 6.0

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group