Re: Checkpoints questions

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.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD