Thread: General DB Tuning
Help! After recently migrating to Postgres 8, I've discovered to my horror that I can't determine which queries are poorly performing anymore because the logging has drastically changed and no longer shows durations for anything done through JDBC. So I'm desperately trying to do performance tuning on my servers and have no way to sort out which statements are the slowest. Does anyone have any suggestions? How do you determine what queries are behaving badly when you can't get durations out of the logs? I have a perl script that analyzes the output from Postgres 7 logs and it works great! But it relies on the duration being there. I did some searches on postgresql.org mailing lists and have seen a few people discussing this problem, but noone seems to be too worried about it. Is there a simple work-around? Sincerely, Brent ____________________________________________________ Sell on Yahoo! Auctions no fees. Bid on great items. http://auctions.yahoo.com/
I have this in my postgresql.conf file and it works fine (set the min to whatever you want to log) log_min_duration_statement = 3000 # -1 is disabled, in milliseconds. Another setting that might get what you want: #log_duration = false uncomment and change to true. From the docs: (http://www.postgresql.org/docs/8.0/interactive/runtime-config.html) Causes the duration of every completed statement which satisfies log_statement to be logged. When using this option, if you are not using syslog, it is recommended that you log the PID or session ID using log_line_prefix so that you can link the statement to the duration using the process ID or session ID. The default is off. Only superusers can change this setting. Brent Henry wrote: > Help! After recently migrating to Postgres 8, I've > discovered to my horror that I can't determine which > queries are poorly performing anymore because the > logging has drastically changed and no longer shows > durations for anything done through JDBC. > > So I'm desperately trying to do performance tuning on > my servers and have no way to sort out which > statements are the slowest. > > Does anyone have any suggestions? How do you > determine what queries are behaving badly when you > can't get durations out of the logs? > > I have a perl script that analyzes the output from > Postgres 7 logs and it works great! But it relies on > the duration being there. > > I did some searches on postgresql.org mailing lists > and have seen a few people discussing this problem, > but noone seems to be too worried about it. Is there > a simple work-around? > > Sincerely, > > Brent > > > > ____________________________________________________ > Sell on Yahoo! Auctions – no fees. Bid on great items. > http://auctions.yahoo.com/ > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > > >
Yes, that is exactly what I want to use! Unfortunately, it doesn't work if you access postgres through a JDBC connection. I don't know why. I found a posting from back in February which talks aobut this a little: http://archives.postgresql.org/pgsql-admin/2005-02/msg00055.php But I can't find anywhere where someone has fixed it. Am I the only one accessing postgres through JDBC? -Brent --- Tom Arthurs <tarthurs@jobflash.com> wrote: > I have this in my postgresql.conf file and it works > fine (set the min to > whatever you want to log) > log_min_duration_statement = 3000 # -1 is disabled, > in milliseconds. > > Another setting that might get what you want: > > #log_duration = false > > uncomment and change to true. > > From the docs: > (http://www.postgresql.org/docs/8.0/interactive/runtime-config.html) > > Causes the duration of every completed statement > which satisfies > log_statement to be logged. When using this option, > if you are not using > syslog, it is recommended that you log the PID or > session ID using > log_line_prefix so that you can link the statement > to the duration using > the process ID or session ID. The default is off. > Only superusers can > change this setting. > > Brent Henry wrote: > > Help! After recently migrating to Postgres 8, > I've > > discovered to my horror that I can't determine > which > > queries are poorly performing anymore because the > > logging has drastically changed and no longer > shows > > durations for anything done through JDBC. > > > > So I'm desperately trying to do performance tuning > on > > my servers and have no way to sort out which > > statements are the slowest. > > > > Does anyone have any suggestions? How do you > > determine what queries are behaving badly when you > > can't get durations out of the logs? > > > > I have a perl script that analyzes the output from > > Postgres 7 logs and it works great! But it relies > on > > the duration being there. > > > > I did some searches on postgresql.org mailing > lists > > and have seen a few people discussing this > problem, > > but noone seems to be too worried about it. Is > there > > a simple work-around? > > > > Sincerely, > > > > Brent > > > > > > > > > ____________________________________________________ > > Sell on Yahoo! Auctions no fees. Bid on great > items. > > http://auctions.yahoo.com/ > > > > ---------------------------(end of > broadcast)--------------------------- > > TIP 5: don't forget to increase your free space > map settings > > > > > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
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. Brent Henry wrote: > Yes, that is exactly what I want to use! > > Unfortunately, it doesn't work if you access postgres > through a JDBC connection. I don't know why. I found > a posting from back in February which talks aobut this > a little: > > http://archives.postgresql.org/pgsql-admin/2005-02/msg00055.php > > But I can't find anywhere where someone has fixed it. > Am I the only one accessing postgres through JDBC? > > -Brent > > > --- Tom Arthurs <tarthurs@jobflash.com> wrote: > > >>I have this in my postgresql.conf file and it works >>fine (set the min to >>whatever you want to log) >>log_min_duration_statement = 3000 # -1 is disabled, >>in milliseconds. >> >>Another setting that might get what you want: >> >>#log_duration = false >> >>uncomment and change to true. >> >> From the docs: >> > > (http://www.postgresql.org/docs/8.0/interactive/runtime-config.html) > >> Causes the duration of every completed statement >>which satisfies >>log_statement to be logged. When using this option, >>if you are not using >>syslog, it is recommended that you log the PID or >>session ID using >>log_line_prefix so that you can link the statement >>to the duration using >>the process ID or session ID. The default is off. >>Only superusers can >>change this setting. >> >>Brent Henry wrote: >> >>>Help! After recently migrating to Postgres 8, >> >>I've >> >>>discovered to my horror that I can't determine >> >>which >> >>>queries are poorly performing anymore because the >>>logging has drastically changed and no longer >> >>shows >> >>>durations for anything done through JDBC. >>> >>>So I'm desperately trying to do performance tuning >> >>on >> >>>my servers and have no way to sort out which >>>statements are the slowest. >>> >>>Does anyone have any suggestions? How do you >>>determine what queries are behaving badly when you >>>can't get durations out of the logs? >>> >>>I have a perl script that analyzes the output from >>>Postgres 7 logs and it works great! But it relies >> >>on >> >>>the duration being there. >>> >>>I did some searches on postgresql.org mailing >> >>lists >> >>>and have seen a few people discussing this >> >>problem, >> >>>but noone seems to be too worried about it. Is >> >>there >> >>>a simple work-around? >>> >>>Sincerely, >>> >>>Brent >>> >>> >>> >>> >> >>____________________________________________________ >> >>>Sell on Yahoo! Auctions – no fees. Bid on great >> >>items. >> >>>http://auctions.yahoo.com/ >>> >>>---------------------------(end of >> >>broadcast)--------------------------- >> >>>TIP 5: don't forget to increase your free space >> >>map settings >> >>> >>> >>---------------------------(end of >>broadcast)--------------------------- >>TIP 2: Don't 'kill -9' the postmaster >> > > > > __________________________________________________ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > > >
Tom Arthurs 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? Dennis
>> 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
hmm, yea maybe -- we are using the 7.4 driver with 8.0.x db. Dennis wrote: > Tom Arthurs 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? > > Dennis > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > > >
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
> 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? The 8.0.2 jdbc driver uses real prepared statements instead of faked ones. The problem is the new protocol (that the 8.0.2 driver users) has a bug where protocol-prepared queries don't get logged properly. I don't know if it's been fixed... Chris
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 > > > >
On Wed, Jul 13, 2005 at 09:52:20AM +0800, Christopher Kings-Lynne wrote:
> The 8.0.2 jdbc driver uses real prepared statements instead of faked
> ones. The problem is the new protocol (that the 8.0.2 driver users) has
> a bug where protocol-prepared queries don't get logged properly.
> I don't know if it's been fixed...
It's not in 8.0.3, but I was having the same problems with DBD::Pg so
I backported some of it and also changed the code so that it listed the
values of the bind parameters, so you get something like
LOG: statement: SELECT sr.name,sr.seq_region_id, sr.length, 1 FROM seq_region sr WHERE sr.name = $1 AND
sr.coord_system_id= $2
LOG: binding: "dbdpg_2" with 2 parameters
LOG: bind "dbdpg_2" $1 = "20"
LOG: bind "dbdpg_2" $2 = "1"
LOG: statement: EXECUTE [PREPARE: SELECT sr.name,sr.seq_region_id, sr.length, 1 FROM seq_region sr WHERE sr.name =
$1AND sr.coord_system_id = $2]
LOG: duration: 0.164 ms
I've attached a patch in case anyone finds it useful.
-Mark
Attachment
On Wed, 2005-07-13 at 09:52 +0800, Christopher Kings-Lynne wrote: > > 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? > > The 8.0.2 jdbc driver uses real prepared statements instead of faked > ones. The problem is the new protocol (that the 8.0.2 driver users) has > a bug where protocol-prepared queries don't get logged properly. > > I don't know if it's been fixed... Yes, there is a fix for this in 8.1 Brent has been sent the details. Best Regards, Simon Riggs