Re: checkpoint spikes

On Thursday 10 June 2010 22:00:54 Greg Smith wrote:
> Janning wrote:
> > 1. With raising checkpoint_timeout, is there any downgrade other than
> > slower after-crash recovery?
>
> Checkpoint spikes happen when too much I/O has been saved up for
> checkpoint time than the server can handle. While this is normally
> handled by the checkpoint spreading logic, you may find that with your
> limited disk configuration there's no other way to handle the problem
> but to make checkpoints much more frequent, rather than slower.

Uhh! I had so much success with less frequent checkpoints. At least the spike
is the same but it does not happen so often.

> At
> http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm I
> write about how you had to tune PostgreSQL 8.0 to 8.2 in order to keep
> checkpoint spikes from getting too big. You may have to follow those
> same techniques for your server. Just don't try to increase the
> background writer settings in your case--the 8.3 one is different enough
> that you can't tune that the way was suggested for 8.2.

we are runing 8.4 and of course I read your article. I just raised
bgwriter_lru_maxpage to 200 :-(

most docs I found relates to 8.2 and 8.3. In Things of checkpoints, is 8.4
comparable to 8.3? It would be nice if you update your article to reflect 8.4.

> > 2. Is there a way to calculate the after-crash recovery time with a
> > certain checkpoint_timeout? How long would be approx. for a
> > checkpoint_timeout of 60 minutes?
>
> Simulate it. No way to estimate.

Ok. won't try it now :-)

> > 3. Is it sane to set checkpoint_timeout to 120min or even to 600min?
>
> Checkpoints happen when you reach either checkpoint_segments of WAL
> written *or* reach checkpoint_timeout, whichever happens first.

Sorry the question was not precise. I already raised checkpoint_segments to
reach that goal of less frequent checkpoints.

> You'd
> have to set both to extremely large values to get checkpoints to happen
> really infrequently. Which I suspect is the exactly opposite of what
> you want--you can't handle the spike from a long delayed checkpoint, and
> probably want to tune for shorter and smaller ones instead.
>
> Every now and then we run into someone who had to retune their system to
> something like:
>
> shared_buffers=512MB
> checkpoint_segments=3
>
> In order to avoid spikes from killing them. That may be the direction
> you have to head. The longer the time between checkpoints, the bigger
> the spike at the end is going to be to some extend; you can't completely
> spread that out.

I am really afraid of doing it right now. In my experience the spike is the
same but we only have it once an hour.

> > 5. Does anybody know if I can set dirty_background_ratio to 0.5? As we
> > have 12 GB RAM and rather slow disks 0,5% would result in a maximum of
> > 61MB dirty pages.
>
> Nope. Linux has absolutely terrible controls for this critical
> performance parameter. The sort of multi-second spikes you're seeing
> are extremely common and very difficult to get rid of.

ok, thanks.

> > PS: Do I need to post this question on pgsql-perfomance? If so, please
> > let me know.
>
> That would have been the better list for it originally. I also wrote
> something about a technique that uses pg_stat_bgwriter snapshots to help
> model what the server is doing in these cases better you might find
> useful on the admin list, it's at
> http://archives.postgresql.org/pgsql-admin/2010-06/msg00074.php

thank you very much for your help!

best regards
Janning