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