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):
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
<ringerc@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.
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
but only one "BEGIN" in the same one day log file, did that influence the query time too?
Do you have a "log_min_duration_statement" directive set in your postgresql.conf ?
--
Craig Ringer