Обсуждение: Checkpoint and Background Writer Statistics
Hi, list.
We're trying to pull a few various metrics from the postgres catalogs to analyze database performance, and included is buffers_checkpoint and buffers_clean from pg_stat_bgwriter.
One of our clients has an lru_max_dirty setting of 1000 and a bgwriter_delay of 200, but we're still seeing much higher growth in the buffers_checkpoint than buffers_clean, with buffers_checkpoint increasing at about 20 times the rate that buffers_clean is. We don't see very much growth in the maxwritten_clean, though - something like one every couple of days.
So the questions is, why are there still so many more checkpoint buffers than clean buffers written? Does the buffers_checkpoint value include more than we think? If not, can we tune things further?
---
===========================
Samuel Nelson
Consistent State
www.consistentstate.com
303-955-0509
===========================
===========================
Samuel Nelson
Consistent State
www.consistentstate.com
303-955-0509
===========================
Err, sorry, I think I meant bgwriter_lru_maxpages, not lru_max_dirty.
---
===========================
Samuel Nelson
Consistent State
www.consistentstate.com
303-955-0509
===========================
---
===========================
Samuel Nelson
Consistent State
www.consistentstate.com
303-955-0509
===========================
On Tue, Feb 22, 2011 at 3:42 PM, Sam Nelson <samn@consistentstate.com> wrote:
Hi, list.We're trying to pull a few various metrics from the postgres catalogs to analyze database performance, and included is buffers_checkpoint and buffers_clean from pg_stat_bgwriter.One of our clients has an lru_max_dirty setting of 1000 and a bgwriter_delay of 200, but we're still seeing much higher growth in the buffers_checkpoint than buffers_clean, with buffers_checkpoint increasing at about 20 times the rate that buffers_clean is. We don't see very much growth in the maxwritten_clean, though - something like one every couple of days.So the questions is, why are there still so many more checkpoint buffers than clean buffers written? Does the buffers_checkpoint value include more than we think? If not, can we tune things further?---
===========================
Samuel Nelson
Consistent State
www.consistentstate.com
303-955-0509
===========================
Sam Nelson wrote: > One of our clients has an lru_max_dirty setting of 1000 and a > bgwriter_delay of 200, but we're still seeing much higher growth in > the buffers_checkpoint than buffers_clean, with buffers_checkpoint > increasing at about 20 times the rate that buffers_clean is. We don't > see very much growth in the maxwritten_clean, though - something like > one every couple of days. The background writer can only clean data pages that haven't been used in a while. In your case, it sounds like most of the things that are getting dirty are being touched enough that they never meet its criteria. There's nothing wrong with that. Writing out dirty buffers only once per checkpoint is in theory the most efficient way to handle regularly changed data. -- Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books