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