From: Greg Smith <greg@2ndQuadrant.com>
To: pgsql-performance@postgresql.org
Subject: Re: 8.2.13 commit is taking too much time
Date: Tue, 10 May 2011 11:55:56 -0400
Message-ID: <4DC9600C.5010109@2ndQuadrant.com> (raw)
In-Reply-To: <BANLkTimRRj1KX5YXWyjcc9wQZJuEMgxAsA@mail.gmail.com>
References: <BANLkTimRRj1KX5YXWyjcc9wQZJuEMgxAsA@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: greg@2ndQuadrant.com
Subject: Re: 8.2.13 commit is taking too much time
In-Reply-To: <4DC9600C.5010109@2ndQuadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox