On Wed, 2 Apr 2008, mark wrote:
> this really clear! Thannks!!
This is the first time someone new to this has ever said that about
checkpoint tuning, which is quite the victory for all of us who worked
toward the 8.3 functional and monitoring improvements in this area.
Please keep posting the results after each change, this will make a great
example for others to follow.
> Based on what Tomasz suggested a day ago, I had changed settings to
> checkpoint_segments = 16
> checkpoint_timeout = 20min
> checkpoint_completion_target = 0.8
>
> checkpoints_timed | checkpoints_req | buffers_checkpoint | buffers_clean |
> 21 | 46 | 2218439 | 34158 |
>
> maxwritten_clean | buffers_backend | buffers_alloc
> 145 | 60447 | 2707259
That's near the upper limit for checkpoint_completion_target; you could
bump that to 0.9 perhaps but I'd save that until after you get the
segments tuned right. It's very good that you're now getting a decent
balance of timed checkpoints in here, but as you can see from the
buffers_* numbers the checkpoint activity is still almost all the writes
on your system.
The nice place to be here is to where buffers_checkpoint is similar in
size to buffers_clean+buffers_backend, that's how you know you've spread
the checkpoint out enough that it's not swamping normal operation with its
overhead.
Small tuning bit: based on the maxwritten_clean figures you're seeing
here I would increase bgwriter_lru_maxpages from the default of 100 to
200.
> i am fine with both WAL disk usage increasing and increase in time to
> recover from crash...
In that case, I'd say stop fooling around and just go to 100 segments or
so based on how much things improved with the jump to 16. If that makes
things get to where you want, then you can explore dropping down to a
smaller setting one day. If it doesn't, you may need to consider other
options here.
Increasing to 100 is approximately a 6X increase, so what you'd like to
see is that instead of the current 96% checkpoint writes/4% other you'd
end up with something 76% checkpoint/24% other. You may need to push to
200 or more segments to get even a true 50/50 balance, but we'll see; the
way this works isn't completely linear. 100 is a pretty big setting for
normal use.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
