Thread: 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@mac.se> To: <pgsql-general@postgresql.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@gregsmith.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