Message-ID: <4FFB848D.7080900@ringerc.id.au>
Date: Tue, 10 Jul 2012 09:25:33 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:13.0) Gecko/20120605 Thunderbird/13.0
MIME-Version: 1.0
To: Yan Chunlu <springrider@gmail.com>
CC: Albe Laurenz <laurenz.albe@wien.gv.at>, pgsql-performance@postgresql.org
Subject: Re: how could select id=xx so slow?
References: 
 <CAOA66tEJLDOt4JpJ0RwOEsgizfQPWkCHd+SgT7kbiHix_io18A@mail.gmail.com>
	<D960CB61B694CF459DCFB4B0128514C2081BF021@exadv11.host.magwien.gv.at>
	<CAOA66tF8dAzAS9wG381bhp+AiC2LrzcF7MDmOxvgW0p07rMNtw@mail.gmail.com>
In-Reply-To: 
 <CAOA66tF8dAzAS9wG381bhp+AiC2LrzcF7MDmOxvgW0p07rMNtw@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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