Thread: how to calculate checkpoint_segments
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...
i always keep fync = false since my server is fully reliable....
increasing checkpoint_segments degrade the performace while checkpointing as it will have a whole lot of dirty buffers to write.....
decreasing it will degrade overall performance as it will keep on doing checkpointing again and again.....
is there any parameters using which i can determine the value of checkpoint_segments.... like how much data my application is generating etc etc....
thx
Himanshu
Do you Yahoo!?
Make Yahoo! your home page
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
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:
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-perf ormance 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