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