Re: : PG9.0 - Checkpoint tuning and pg_stat_bgwriter

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@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us