pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
How to interpret view pg_stat_bgwriter 4+ messages / 2 participants [nested] [flat]
* How to interpret view pg_stat_bgwriter @ 2014-09-16 06:42 Huang, Suya <Suya.Huang@au.experian.com> 2014-09-17 20:40 ` Re: How to interpret view pg_stat_bgwriter Peter Eisentraut <peter_e@gmx.net> 0 siblings, 1 reply; 4+ messages in thread From: Huang, Suya @ 2014-09-16 06:42 UTC (permalink / raw) To: 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to interpret view pg_stat_bgwriter 2014-09-16 06:42 How to interpret view pg_stat_bgwriter Huang, Suya <Suya.Huang@au.experian.com> @ 2014-09-17 20:40 ` Peter Eisentraut <peter_e@gmx.net> 2014-09-19 05:54 ` Re: How to interpret view pg_stat_bgwriter Huang, Suya <Suya.Huang@au.experian.com> 0 siblings, 1 reply; 4+ messages in thread From: Peter Eisentraut @ 2014-09-17 20:40 UTC (permalink / raw) To: Huang, Suya <Suya.Huang@au.experian.com>; pgsql-admin On 9/16/14 2:42 AM, Huang, Suya wrote: > # checkpoints_req is much bigger than checkpoints_timed, suggest that I > may increase checkpoint_segments in our system That's not strictly necessary, but personally I think both of those settings are too low for you. > #maxwritten_clean is high, suggests increase bgwriter_lru_maxpages Yes. > # buffers_backend is much smaller than buffers_alloc, suggests > increasing bgwriter_lru_maxpages, bgwriter_lru_multiplier, and > decreasing bgwriter_delay. You should rather compare buffers_backend against buffers_checkpoint. Also consider graphing these quantities over time so you can see how different workloads affects the results and what the effects of your tuning are. Also turn on log_checkpoints and look at the sync times. In my experience that dominates all the bgwriter tuning. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to interpret view pg_stat_bgwriter 2014-09-16 06:42 How to interpret view pg_stat_bgwriter Huang, Suya <Suya.Huang@au.experian.com> 2014-09-17 20:40 ` Re: How to interpret view pg_stat_bgwriter Peter Eisentraut <peter_e@gmx.net> @ 2014-09-19 05:54 ` Huang, Suya <Suya.Huang@au.experian.com> 2014-09-22 18:04 ` Re: How to interpret view pg_stat_bgwriter Peter Eisentraut <peter_e@gmx.net> 0 siblings, 1 reply; 4+ messages in thread From: Huang, Suya @ 2014-09-19 05:54 UTC (permalink / raw) To: Peter Eisentraut <peter_e@gmx.net>; pgsql-admin -----Original Message----- From: Peter Eisentraut [mailto:peter_e@gmx.net] Sent: Thursday, September 18, 2014 6:41 AM To: Huang, Suya; pgsql-admin@postgresql.org Subject: Re: [ADMIN] How to interpret view pg_stat_bgwriter >You should rather compare buffers_backend against buffers_checkpoint. >Also consider graphing these quantities over time so you can see how different workloads affects the results and what the effects of your tuning are. >Also turn on log_checkpoints and look at the sync times. In my experience that dominates all the bgwriter tuning. Thank you Peter, comes with more question: How to compare buffers_backend against buffers_checkpoint? Below is some statistics of check point activities happened on the day while backend is loading data: Day Hour Written buffers Write time Sync time Total time 06 107,338 1,475.583s 3.142s 1,478.828s 07 601,672 1,041.482s 348.215s 1,390.153s 08 3,613 361.422s 0.053s 361.535s 09 1,006,704 1,456.898s 14.501s 1,471.545s 10 1,116,463 1,987.896s 6.164s 1,995.11s Can we tell something about the configuration? Thanks. Suay -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to interpret view pg_stat_bgwriter 2014-09-16 06:42 How to interpret view pg_stat_bgwriter Huang, Suya <Suya.Huang@au.experian.com> 2014-09-17 20:40 ` Re: How to interpret view pg_stat_bgwriter Peter Eisentraut <peter_e@gmx.net> 2014-09-19 05:54 ` Re: How to interpret view pg_stat_bgwriter Huang, Suya <Suya.Huang@au.experian.com> @ 2014-09-22 18:04 ` Peter Eisentraut <peter_e@gmx.net> 0 siblings, 0 replies; 4+ messages in thread From: Peter Eisentraut @ 2014-09-22 18:04 UTC (permalink / raw) To: Huang, Suya <Suya.Huang@au.experian.com>; pgsql-admin On 9/19/14 1:54 AM, Huang, Suya wrote: > How to compare buffers_backend against buffers_checkpoint? This is a very general question, but generally you want buffers_backend to be as small as possible. > Below is some statistics of check point activities happened on the day while backend is loading data: > > Day Hour Written buffers Write time Sync time Total time > 06 107,338 1,475.583s 3.142s 1,478.828s > 07 601,672 1,041.482s 348.215s 1,390.153s > 08 3,613 361.422s 0.053s 361.535s > 09 1,006,704 1,456.898s 14.501s 1,471.545s > 10 1,116,463 1,987.896s 6.164s 1,995.11s > > Can we tell something about the configuration? If this is while loading data, there isn't much to say. I'd be worried about the 348s sync time if this were during normal transaction processing load. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2014-09-22 18:04 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2014-09-16 06:42 How to interpret view pg_stat_bgwriter Huang, Suya <Suya.Huang@au.experian.com> 2014-09-17 20:40 ` Peter Eisentraut <peter_e@gmx.net> 2014-09-19 05:54 ` Huang, Suya <Suya.Huang@au.experian.com> 2014-09-22 18:04 ` Peter Eisentraut <peter_e@gmx.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox