Re: General DB Tuning - Mailing list pgsql-performance
From | Tom Arthurs |
---|---|
Subject | Re: General DB Tuning |
Date | |
Msg-id | 42D47407.10301@jobflash.com Whole thread Raw |
In response to | Re: General DB Tuning (Brent Henry <bh_pgperf@yahoo.com>) |
List | pgsql-performance |
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@familyhealth.com.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 > > > >
pgsql-performance by date: