Re: how could select id=xx so slow?

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 <ringerc(at)ringerc(dot)id(dot)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
>