Date: Thu, 19 May 2005 14:29:27 -0400
From: Donald Courtney <Donald.Courtney@Sun.COM>
Subject: Re: PostgreSQL strugling during high load
In-reply-to: 
 <4BAFBB6B9CC46F41B2AD7D9F4BBAF785098A32@vt-pe2550-001.vantage.vantage.com>
To: Anjan Dave <adave@vantage.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-performance@postgresql.org
Message-id: <428CDB07.2030503@sun.com>
Organization: Sun Microsystems
MIME-version: 1.0
Content-type: text/plain; charset=UTF-8; format=flowed
Content-transfer-encoding: 7BIT
User-Agent: Mozilla/5.0 (X11; U; SunOS i86pc; en-US; rv:1.7) Gecko/20041221
References: 
 <4BAFBB6B9CC46F41B2AD7D9F4BBAF785098A32@vt-pe2550-001.vantage.vantage.com>

Anjan Dave wrote:

>What platform is this?
> 
>  
>
Its a DELL RH 4 with the xlog on a seperate external mounted file system.
The data directory is on a external mounted file system as well.

>We had similar issue (PG 7.4.7). Raising number of checkpoint segments to 125, seperating the WAL to a different LUN helped, but it's still not completely gone.
> 
>  
>
I'll try raising the number.  I guess the bg* config variables don't do 
much?

thanks

>As far as disk I/O is concerned for flushing the buffers out, I am not ruling out the combination of Dell PERC4 RAID card, and the RH AS 3.0 Update3 being a problem.
> 
>Thanks,
>Anjan
>
>	-----Original Message----- 
>	From: Donald Courtney [mailto:Donald.Courtney@Sun.COM] 
>	Sent: Thu 5/19/2005 12:54 PM 
>	To: Tom Lane 
>	Cc: pgsql-performance@postgresql.org 
>	Subject: Re: [PERFORM] PostgreSQL strugling during high load
>	
>	
>
>	Tom 
>
>	Thanks for the post - I think I am getting this problem for 
>	a synthetic workload at high connection loads.  The whole 
>	system seems to stop. 
>
>	Can you give some examples on what to try out in the .conf file? 
>
>	I tried 
>	bgwriter_all_percent =  30, 10, and 3 
>
>	Which I understand to mean 30%, 10% and 3% of the dirty pages should be 
>	written out *between* checkpoints. 
>
>	I didn't see any change in effect. 
>
>	/regards 
>	Don C. 
>
>	Tom Lane wrote: 
>
>	>"Mindaugas Riauba" <mind@bi.lt> writes: 
>	>  
>	> 
>	>>  It looks like that not only vacuum causes our problems. vacuum_cost 
>	>>seems to lower vacuum impact but we are still noticing slow queries "storm". 
>	>>We are logging queries that takes >2000ms to process. 
>	>>  And there is quiet periods and then suddenly 30+ slow queries appears in 
>	>>log within the same second. What else could cause such behaviour? 
>	>>    
>	>> 
>	> 
>	>Checkpoints?  You should ensure that the checkpoint settings are such 
>	>that checkpoints don't happen too often (certainly not oftener than 
>	>every five minutes or so), and make sure the bgwriter is configured 
>	>to dribble out dirty pages at a reasonable rate, so that the next 
>	>checkpoint doesn't have a whole load of stuff to write. 
>	> 
>	>                       regards, tom lane 
>	> 
>	>---------------------------(end of broadcast)--------------------------- 
>	>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org 
>	>  
>	> 
>
>
>	---------------------------(end of broadcast)--------------------------- 
>	TIP 7: don't forget to increase your free space map settings 
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>      subscribe-nomail command to majordomo@postgresql.org so that your
>      message can get through to the mailing list cleanly
>  
>