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
>
>