Greg, > There look to be a good number of buffers on this server that are only > being written at checkpoint time. The background writer will only deal > with buffers when their usage count is low. Fast servers can cycle over > shared_buffers such that as soon as their usage counts get low, they're > immediately reallocated by a hungry backend. You might try to quantify > how many buffers the BGW can possibly do something with using > pg_buffercache. Yeah, that's going to be the next step. > 2007's defaults can be a bummer in 2013. I don't hesitate to bump that > up to 500 on a server with decent hardware. Right, that's what I just tested. The results are interesting. I changed the defaults as follows: bgwriter_delay = 100ms bgwriter_lru_maxpages = 512 bgwriter_lru_multiplier = 3.0 ... and the number of buffers being written by the bgwriter went *down*, almost to zero. Mind you, I wanna gather a full week of data, but there seems to be something counterintuitive going on here. One potential factor is that they have their shared_buffers set unusually high (5GB out of 16GB). Here's the stats: postgres=# select * from pg_stat_bgwriter; -[ RECORD 1 ]---------+------------------------------ checkpoints_timed | 330 checkpoints_req | 47 checkpoint_write_time | 55504727 checkpoint_sync_time | 286743 buffers_checkpoint | 2809031 buffers_clean | 789 maxwritten_clean | 0 buffers_backend | 457456 buffers_backend_fsync | 0 buffers_alloc | 943734 stats_reset | 2013-07-17 17:09:18.945194-07 So we're not hitting maxpages anymore, at all. So why isn't the bgwriter doing any work? -[ RECORD 1 ]-----------+-------- pct_checkpoints_req | 12.0 avg_frequency_min | 2.78 avg_write_time_s | 146.91 avg_sync_time_s | 0.76 mb_written | 25617.8 mb_written_per_min | 24.42 mb_per_checkpoint | 58.27 pct_checkpoint_buffers | 86.0 pct_bgwriter_buffers | 0.0 pct_backend_buffers | 14.0 bgwriter_halt_freq | 0.00 bgwriter_halt_potential | 0.00 buffer_allocation_ratio | 0.288 And your query, with some rounding added: -[ RECORD 1 ]---+------ alloc_mbps | 0.116 checkpoint_mbps | 0.340 clean_mbps | 0.000 backend_mbps | 0.056 write_mbps | 0.396 -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
