Re: General DB Tuning

Here's the answer for you from the jdbc list:

> Alvin Hung wrote:
>
>
>>> Currently, 8.0.2 / JDBC 8.0-310, log_min_duration_statement does not
>>> work with JDBC. Nothing will get logged. This makes it very
>>> difficult to tune a java application. Can you tell me when will this
>>> be fixed? Thanks.
>
>
> This is a server limitation: it does not handle logging of the V3
> extended query protocol very well. There's gradual progress being made
> on it; you might want to search the pgsql-hackers and pgsql-patches
> archives for details.
==========================================================================================

We are using prepared statements, but we are using the 7.4 driver with
the 8.0.3 server. I think it comes down to locally (on the client)
prepared statements vs using server side prepared statments. I never
got past this issue (changing the code is in our todo list, but pretty
far down it) so I never noticed the logging issues.)

I had a problem with prepared statements with the 8.x drivers -- here's
what I got from the jdbc list when I asked the question:

>>1. What changed between the driver versions that generate this error?
>
>
> The driver started to use server-side prepared statements for
> parameterization of queries (i.e. the driver translates ? to $n in the
> main query string, and sends the actual parameter values out-of-band
> from the query itself). One sideeffect of this is that parameters are
> more strongly typed than in the 7.4.x versions where the driver would do
> literal parameter substitution into the query string before sending it
> to the backend. Also, you can use parameters in fewer places (they must
> fit the backend's idea of where parameterizable expressions are allowed)
> -- e.g. see the recent thread about "ORDER BY ?" changing behaviour with
> the newer driver.
>
>
>>> 2. What is the downside of continuing to use the 7.x version of the
>>> driver -- or are there better alternatives (patch, new version, etc). I
>>> am using build 311 of the driver.
>
>
> Most active development happens on the 8.0 version; 7.4.x is maintained
> for bugfixes but that's about it, you won't get the benefit of any
> performance improvements or added features that go into 8.0. Also, the
> 7.4.x driver won't necessarily work with servers >= 8.0.
>
> In the longer term, the 7.4.x version will eventually become unmaintained.

So for the short term, you could downgrade your driver.

Brent Henry wrote:
> We are running Postgres 8.0.2 with the 8.0.2 jdbc
> driver. And yes we are using prepared statements.
> I've spent hours trying to get the
> 'log_min_duration_statement' and 'log_duration'
> options to work with no luck. I never get any
> duration from the statement. I also never see 'begin'
> or 'commit' in the log so I can't tell how long my
> batch commands are taking to commit to the DB.
>
> Is there a different kind of 'prepared' statements
> that we should be using in the driver to get logging
> to work properly? What is the 'new' protocol?
>
> Tom, what version are you using? Are you using
> prepared statements in JDBC?
>
> -Brent
>
>
> --- Christopher Kings-Lynne
> <chriskl(at)familyhealth(dot)com(dot)au> wrote:
>
>
>>>>we are using jdbc -- the
>>
>>"log_min_duration_statement = 3000 "
>>
>>>>statement works fine for me. Looks like there's
>>
>>no other work around
>>
>>>>for the bug(?). Not sure since I have no
>>
>>interest in logging a
>>
>>>>million statements a day, I only want to see the
>>
>>poorly performing hits.
>>
>>>Doesn't it depend on what jdbc driver you are
>>
>>using?
>>
>>It depends if he's using new-protocol prepared
>>queries which don't get
>>logged properly. Wasn't that fixed for 8.1 or
>>something?
>>
>>Chris
>>
>>
>>---------------------------(end of
>>broadcast)---------------------------
>>TIP 3: Have you checked our extensive FAQ?
>>
>> http://www.postgresql.org/docs/faq
>>
>
>
>
>
>
> ____________________________________________________
> Start your day with Yahoo! - make it your home page
> http://www.yahoo.com/r/hs
>
>
>
>