Re: how to calculate checkpoint_segments

Himanshu Baweja <himanshubaweja@yahoo.com> writes:
> i am trying to optimise postgres 8 running on a system.... is there any way to know how wht should be the value of
checkpoint_segments...

Enough so you usually aren't checkpointing more often than is specified
by checkpoint_timeout.  If you do not know what your system's normal
consumption of WAL is, try setting checkpoint_warning to the same value
as checkpoint_timeout and then keep an eye on the postmaster log to see
how often it complains.  You really really *don't* want the thing
checkpointing more often than once every five or ten or so minutes.

> increasing checkpoint_segments degrade the performace while checkpointing as it will have a whole lot of dirty
buffersto write..... 

This is a fundamental misconception --- the bgwriter exists to prevent that.

See the pgsql-performance archives for more information.

            regards, tom lane