Re: Checkpoints questions - Mailing list pgsql-general

4 mar 2008 kl. 13.45 skrev Greg Smith:

> On Tue, 4 Mar 2008, Henrik wrote:
>
>> As a starter does anyone have some clues how to analyse this:
>>
>> db=# select * from pg_stat_bgwriter;
>> checkpoints_timed | checkpoints_req | buffers_checkpoint |
>> buffers_clean | maxwritten_clean | buffers_backend | buffers_alloc
>> -------------------+-----------------+--------------------
>> +---------------+------------------+-----------------+---------------
>>             118 |             435 |            1925161 |
>> 126291 | 7 |         1397373 |       2665693
>
> Ah, nobody has asked this question yet.  This is a good sample and
> I'm going to assimilate it into my document that someone already
> suggested to you.
>
> You had 118 checkpoints that happened because of checkpoint_timeout
> passing.  435 of them happened before that, typically those are
> because checkpoint_segments was reached.  This suggests you might
> improve your checkpoint situation by increasing checkpoint_segments,
> but that's not a bad ratio.  Increasing that parameter and spacing
> checkpoints further apart helps give the checkpoint spreading logic
> of checkpoint_completion_target more room to work over, which
> reduces the average load from the checkpoint process.
>
> During those checkpoints, 1,925,161 8K buffers were written out.
> That means on average, a typical checkpoint is writing 3481 buffers
> out, which works out to be 27.2MB each.  Pretty low, but that's an
> average; there could have been some checkpoints that wrote a lot
> more while others wrote nothing, and you'd need to sample this data
> regularly to figure that out.
>
> The background writer cleaned 126,291 buffers (cleaned=wrote out
> dirty ones) during that time.  7 times, it wrote the maximum number
> it was allowed to before meeting its other goals.  That's pretty
> low; if it were higher, it would be obvious you could gain some
> improvement by increasing bgwriter_lru_maxpages.
>
> Since last reset, 2,665,693 8K buffers were allocated to hold
> database pages.  Out of those allocations, 1,397,373 times a
> database backend (probably the client itself) had to write a page in
> order to make space for the new allocation.  That's not awful, but
> it's not great.  You might try and get a higher percentage written
> by the background writer in advance of when the backend needs them
> by increasing bgwriter_lru_maxpages, bgwriter_lru_multiplier, and
> decreasing bgwriter_delay--making the changes in that order is the
> most effective strategy.
>

Ah, thank you Greg. I actually studied your paper before writing to
this list but couldn't apply your example to mine. Now I know how I
can interpret those numbers. Also thank you for the performance
improvement suggestions. I think this is one of the most difficult
things to understand. Knowing what parameters to tweak according to
the output from pg_stat_bgwriter but you helped me a great deal.

Thanks!

//Henke