the transaction part is wired, I have filtered BEGIN and COMMIT from a one day log by using: cat /usr/local/pgsql/data/pg_log/Saturday.log |grep -E "BEGIN|COMMIT" >trans.txt
and pasted it to gist(only three BEGIN and many COMMIT): https://gist.github.com/3080600 I didn't set log_min_duration_statement in the postgresql.conf, but execute *dbapi_con.cursor().execute("SET log_min_duration_statement to 30")* *for every connection.* *the system is in production and relatively heavy load, I thought it would be better not "reload" the db too frequently, use the **"SET log_min_duration_statement to 30" I could turn the log off within my application.* On Tue, Jul 10, 2012 at 9:25 AM, Craig Ringer <ring...@ringerc.id.au> wrote: > On 07/09/2012 05:20 PM, Yan Chunlu wrote: > >> >> the value of "log_min_messages" in postgresql.conf is error, I have >> changed it to "warning", so far does not received any warning, still >> waiting. >> > When trying to track down performance issues, increasing logging to at > least `info' would seem to be sensible. > > I suggest increasing your logging and enabling the auto_explain module so > it logs slow queries. If you can afford the substantial performance hit you > could enable its analyze mode to get details on why. > > > >> beside I do see some COMMIT which is relatively slow for example: >> 60 2012-07-08 00:00:29 CST [19367]: [131-1] LOG: duration: 375.851 ms >> statement: COMMIT >> 61 2012-07-08 00:00:30 CST [19367]: [132-1] LOG: duration: 327.964 ms >> statement: COMMIT >> > That certainly is slow. Again, I suspect checkpoint activity could be at > fault. You may need to tune to spread your checkpoints out and use more > aggressive bgwriter settings. See the wiki for performance tuning info. > > > >> but only one "BEGIN" in the same one day log file, did that influence the >> query time too? >> > > Only one BEGIN in the whole day? Or do you mean "only one BEGIN slow > enough to trigger slow query logging" ? > > Do you have a "log_min_duration_statement" directive set in your > postgresql.conf ? > > -- > Craig Ringer >
