How to interpret view pg_stat_bgwriter

From: "Huang, Suya" <Suya(dot)Huang(at)au(dot)experian(dot)com>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: How to interpret view pg_stat_bgwriter
Date: 2014-09-16 06:42:49
Message-ID: D83E55F5F4D99B4A9B4C4E259E6227CD01EBFA82@AUX1EXC02.apac.experian.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi,

I was reading an article of Gregory Smith http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm and tried to do some analysis on our database.

postgres=# select * from pg_stat_bgwriter;
-[ RECORD 1 ]------+------------
checkpoints_timed | 42435
checkpoints_req | 629448
buffers_checkpoint | 1821978480
buffers_clean | 117710078
maxwritten_clean | 23796
buffers_backend | 1284631340
buffers_alloc | 32829025268

postgres=# show checkpoint_segments ;
-[ RECORD 1 ]-------+----
checkpoint_segments | 128

postgres=# show checkpoint_timeout ;
-[ RECORD 1 ]------+------
checkpoint_timeout | 10min

bgwriter_delay bgwriter_lru_maxpages bgwriter_lru_multiplier
postgres=# show bgwriter_delay;
-[ RECORD 1 ]--+------
bgwriter_delay | 100ms

postgres=# show bgwriter_lru_maxpages;
-[ RECORD 1 ]---------+-----
bgwriter_lru_maxpages | 1000

postgres=# show bgwriter_lru_multiplier;
-[ RECORD 1 ]-----------+--
bgwriter_lru_multiplier | 5

based on one snapshot, below are my thoughts after reading the example reading the example Greg used, it might be completely wrong as I'm just starting the learning process of checkpoint mechanism in PG. If anything missing/wrong, appreciate if you can help to point out.

# checkpoints_req is much bigger than checkpoints_timed, suggest that I may increase checkpoint_segments in our system
#maxwritten_clean is high, suggests increase bgwriter_lru_maxpages
# buffers_backend is much smaller than buffers_alloc, suggests increasing bgwriter_lru_maxpages, bgwriter_lru_multiplier, and decreasing bgwriter_delay.

Thanks,
Suya

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Alexey Klyukin 2014-09-16 10:57:57 Re: Standby is not removing restored WAL segments
Previous Message gabrielle 2014-09-16 00:47:27 log message from autovac doesn't include db name