Thread: Finding slow queries?
Hello, I am a true newbie. I am brand new to postgres, and databases in general. If anyone has any tips to offer, I'd really appreciate it. I am trying to find which queries are the slowest from a web site which runs on top of postgres 7.4.1. I'm not sure what I need to do, though. I set the log_min_duration_statement to 10000, but I don't see anything new being logged to the pg.log. Are these queries logged somewhere else, or do I need to change anything else in the conf? Like I said, any help anyone could offer would be great. I've inherited ownership of this web site just because I'm the most technical person around the nonprofit that I work for, but all that means is that I can program the time into my VCR. :) Thanks! Jess __________________________________ Do you Yahoo!? Yahoo! Search - Find what you�re looking for faster http://search.yahoo.com
Jessica Dalman <jessica_dalman@yahoo.com> writes: > I am trying to find which > queries are the slowest from a web site which runs on > top of postgres 7.4.1. I'm not sure what I need to > do, though. I set the log_min_duration_statement to > 10000, but I don't see anything new being logged to > the pg.log. Are you sure that you have any queries that take more than 10 seconds? Maybe you need a smaller value of log_min_duration_statement. Another thought: did you remember to SIGHUP the postmaster (or equivalently "pg_ctl reload") after changing the config file? It won't notice your change unless nudged. You can check whether the change has taken effect by psql'ing in and doing a SHOW on the variable. regards, tom lane
Jessica Dalman wrote: >Hello, > > I am a true newbie. I am brand new to postgres, and >databases in general. If anyone has any tips to offer, >I'd really appreciate it. I am trying to find which >queries are the slowest from a web site which runs on >top of postgres 7.4.1. I'm not sure what I need to >do, though. I set the log_min_duration_statement to >10000, but I don't see anything new being logged to >the pg.log. Are these queries logged somewhere else, >or do I need to change anything else in the conf? > > Like I said, any help anyone could offer would be >great. I've inherited ownership of this web site just >because I'm the most technical person around the >nonprofit that I work for, but all that means is that >I can program the time into my VCR. :) > >Thanks! >Jess > >__________________________________ >Do you Yahoo!? >Yahoo! Search - Find what you’re looking for faster >http://search.yahoo.com > >---------------------------(end of broadcast)--------------------------- >TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > > On the off-chance that you haven't seen these docs yet, here are links to fine tuning your postgresql settings: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html And remember that you should VACUUM FULL ANALYZE your database regularly (more often if there are a lot of updates/deletes). If this hasn't been performed recenlty you would probably notice a large performance increase after running it. Ron