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@xxxxxxxxxxxxx> wrote:
On 07/09/2012 05:20 PM, Yan Chunlu wrote:When trying to track down performance issues, increasing logging to at least `info' would seem to be sensible.
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.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.
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
Only one BEGIN in the whole day? Or do you mean "only one BEGIN slow enough to trigger slow query logging" ?
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
