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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
