Re: Re: bgwriter autotuning might be unnecessarily penalizing bursty workloads

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