Re: how could select id=xx so slow?

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