Message-ID: <4DC9600C.5010109@2ndQuadrant.com>
Date: Tue, 10 May 2011 11:55:56 -0400
From: Greg Smith <greg@2ndQuadrant.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.1.16) Gecko/20110307 Icedove/3.0.11
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: 8.2.13 commit is taking too much time
References: <BANLkTimRRj1KX5YXWyjcc9wQZJuEMgxAsA@mail.gmail.com>
In-Reply-To: <BANLkTimRRj1KX5YXWyjcc9wQZJuEMgxAsA@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 05/10/2011 03:01 AM, AI Rumman wrote:
> I am using Postgresql 8.2.13 and I found that most of the commits and 
> insert or update statements  are taking more than 4s in the db and the 
> app performance is slow for that.
> My db settings are as follows;
> bgwriter_all_maxpages  | 300     |
>  bgwriter_all_percent  | 15      |
>  bgwriter_delay        | 300     | ms
>  bgwriter_lru_maxpages | 50      |
>  bgwriter_lru_percent  | 10      |

Reduce bgwriter_all_maxpages to 0, definitely, and you might drop 
bgwriter_lru_maxpages to 0 too.  Making the background writer in 
PostgreSQL 8.2 do more work as you've tried here increases the amount of 
repeated I/O done by a lot, without actually getting rid of any pauses.  
It wastes a lot of I/O capacity instead, making the problems you're 
seeing worse.

>  shared_buffers
> ----------------
>  4GB
>

On 8.2, shared_buffers should be no more than 128MB if you want to avoid 
long checkpoint pauses.  You might even find best performance at the 
default of 32MB.


> I have 32 gb RAM and its a 4*2=8 core processors.
> Any idea how to improve the performance?

There's nothing you can do here that will work better than upgrading to 
8.3.  See 
http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm for 
more information.  PostgreSQL 8.2 had serious problems with the sort of 
pauses you're seeing back when systems had only 4GB of memory; you'll 
never get rid of them on a server with 32GB of RAM on that version.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books