Re: : PG9.0 - Checkpoint tuning and pg_stat_bgwriter

Thanks Greg !

Sorry for delayed response.

We are actually waiting to change the checkpoint_segments in our production
systems (waiting for the downtime).

Thanks
VB

On Wed, Oct 5, 2011 at 11:02 AM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:

> On 10/04/2011 07:50 PM, Venkat Balaji wrote:
>
>> I was thinking to increase checkpoint_segments to around 16 or 20.
>>
>> I think 50 is a bit higher.
>>
>>
> Don't be afraid to increase that a lot. You could set it to 1000 and that
> would be probably turn out fine; checkpoints will still happen every 5
> minutes.
>
> Checkpoints represent a lot of the I/O in a PostgreSQL database. The main
> downside to making them less frequent is that recovery after a crash will
> take longer; a secondary one is that WAL files in pg_xlog will take up more
> space. Most places don't care much about either of those things. The
> advantage to making them happen less often is that you get less total
> writes. People need to be careful about going a long *time* between
> checkpoints. But there's very few cases where you need to worry about the
> segment count going too high before another one is triggered.
>
>
> --
> Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
> PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
>
>