Checkpoints questions

Hi list,

I'm using 8.3 and I've started looking at the new checkpoint features.

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

Thanks!
//Henke

Hi,
Hope this helps
http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm

Thanks
DEVI.G
----- Original Message -----
From: "Henrik" <henke(at)mac(dot)se>
To: <pgsql-general(at)postgresql(dot)org>
Sent: Tuesday, March 04, 2008 3:28 PM
Subject: [GENERAL] Checkpoints questions

> Hi list,
>
> I'm using 8.3 and I've started looking at the new checkpoint features.
>
> 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
>
>
> Thanks!
> //Henke
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>
>
> --
> No virus found in this incoming message.
> Checked by AVG Free Edition. Version: 7.5.516 / Virus Database:
> 269.21.4/1309 - Release Date: 3/3/2008 6:50 PM
>
>

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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

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