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 buffers to write.....

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

See the pgsql-performance archives for more information.

regards, tom lane

i think there is a bug in the checkpoint warning system.....
i had set =>
checkpoint_timeout = 2000
checkpoint_warning = 2100
checkpoint_segments = 256 (for 1st run) and 64 (for 2nd run)

now i ran my test application.... my Wal-logs in PGDATA/pg_xlog increased by around 1.9 GB while in my log file there were only two warnings.... so i re-run the test application this time turned the archive on... the archive logs generated were also of around 2 GB..... but this time i had just one warning in my log file.....

now theoritically it should give me a warning each-time a checkpoint occurs.... but i am getting just one....

am i missing something or there is a problem....
....... and is there any other way by which i can check how frequently my database is checkpointing....

Regards
Himanshu

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

got it.... i think i need to sleep....
i forgot each segment is 16 Mb... sorry for buggung u all....
himanshu

Tom Lane <tgl@sss.pgh.pa.us> wrote:
Himanshu Baweja 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 buffers to write.....

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

See the pgsql-performance archives for more information.

regards, tom lane

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com