Re: 8.2.13 commit is taking too much time

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(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books