Thread: Help interpreting pg_stat_bgwriter output
Hello all,
I wonder if I could have some help interpreting the output of pg_stat_bgwriter. Here's my output:
checkpoints_timed | checkpoints_req | buffers_checkpoint | buffers_clean | maxwritten_clean | buffers_backend | buffers_alloc
-------------------+-----------------+--------------------+---------------+------------------+-----------------+---------------
333 | 0 | 617784 | 0 | 0 | 740 | 19163
(1 row)
In case that gets garbled in email transmission, it transcribes to:
checkpoints_timed = 333
checkpoints_req = 0
buffers_checkpoint = 617784
buffers_clean = 0
maxwritten_clean = 0
buffers_backend = 740
buffers_alloc = 19163
So I've had a look at the docs, but I'm still not entirely clear what all those values mean.
I think what I'm concerned about is the fact that buffers_clean = 0. I've read Greg Smith's introduction to the bgwriter (http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm), and if I understand it correctly does the fact that buffers_clean = 0 imply that the bgwriter is actually not writing *any* buffers?
So my questions are:
* is my interpreting of buffers_clean = 0 correct?
* If so, why would the bgwriter not be writing out any buffers?
My bgwriter config settings are:
bgwriter_delay = 200ms
bgwriter_lru_maxpages = 800
bgwriter_lru_multiplier = 8.0
Checkpoint settings are default except for:
checkpoint_timeout = 30min
checkpoint_segments = 10
checkpoint_completion_target = 0.9
Not sure what other config settings are pertinent here, but I can supply them if required.
I am using postgresql 8.3.7-0ubuntu8.04.1, running on an OpenVZ VPS.
Thanks for any pointers.
Sam Mulube
I wonder if I could have some help interpreting the output of pg_stat_bgwriter. Here's my output:
checkpoints_timed | checkpoints_req | buffers_checkpoint | buffers_clean | maxwritten_clean | buffers_backend | buffers_alloc
-------------------+-----------------+--------------------+---------------+------------------+-----------------+---------------
333 | 0 | 617784 | 0 | 0 | 740 | 19163
(1 row)
In case that gets garbled in email transmission, it transcribes to:
checkpoints_timed = 333
checkpoints_req = 0
buffers_checkpoint = 617784
buffers_clean = 0
maxwritten_clean = 0
buffers_backend = 740
buffers_alloc = 19163
So I've had a look at the docs, but I'm still not entirely clear what all those values mean.
I think what I'm concerned about is the fact that buffers_clean = 0. I've read Greg Smith's introduction to the bgwriter (http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm), and if I understand it correctly does the fact that buffers_clean = 0 imply that the bgwriter is actually not writing *any* buffers?
So my questions are:
* is my interpreting of buffers_clean = 0 correct?
* If so, why would the bgwriter not be writing out any buffers?
My bgwriter config settings are:
bgwriter_delay = 200ms
bgwriter_lru_maxpages = 800
bgwriter_lru_multiplier = 8.0
Checkpoint settings are default except for:
checkpoint_timeout = 30min
checkpoint_segments = 10
checkpoint_completion_target = 0.9
Not sure what other config settings are pertinent here, but I can supply them if required.
I am using postgresql 8.3.7-0ubuntu8.04.1, running on an OpenVZ VPS.
Thanks for any pointers.
Sam Mulube
On Wed, 12 Aug 2009, sam mulube wrote: > is my interpreting of buffers_clean = 0 correct? Yes. > If so, why would the bgwriter not be writing out any buffers? The purpose of the cleaner is to prepare buffers that we expect will be needed for allocations in the near future. Let's do a little math on your system to guess why that's not happening. > checkpoints_timed = 333 > checkpoints_req = 0 You're never triggering checkpoints from activity. This suggests that your system is having a regular checkpoint every 5 minutes, and therefore the time your server has been up is about 1665 minutes. > bgwriter_delay = 200ms With the background writer running 5 times per second, the data you've sampled involved it running 1665 * 60 * 5 = 499500 times. During none of those runs did it actually write anything; why? > buffers_alloc = 19163 During those runs, 19163 buffers were allocated. This means that during the average background writer delay nap, 19163 / 499500 = 0.04 buffers were allocated. That's very little demand for buffers that need to be cleaned on average, and the evidence here suggests the system is finding plenty of cleaned up and ready to go buffers from the background checkpoint process. It doesn't need to do any work on top of what the checkpoint buffer cleanup is doing. > buffers_backend = 740 This number represents the behavior the background writer is trying to prevent--backends having to clean their own buffers up. Your result here suggests that on average, during any 5 minute period there are 740 / 333 = 2.2 buffers being written that we might have had the background writer take care of instead. Again, that's so little activity that the averages the background writer estimates with aren't even detecting anything worth doing. In short, your system isn't nearly active enough for the background writer to find itself with useful work to do, and one of the design goals for it was to keep it from spinning around doing nothing in that situation. If your system load goes up, I expect you'll discover cleaning starts happening too. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
On Thu, Aug 13, 2009 at 3:00 AM, Greg Smith<gsmith@gregsmith.com> wrote: > >> buffers_backend = 740 > > This number represents the behavior the background writer is trying to > prevent--backends having to clean their own buffers up. > so what we want on busy systems is buffers_backend to be (at least) equal or (better) lower than buffers_clean, rigth? or i'm understanding wrong? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157