Re: Let's drop some GUCs (bgwriter)

On Mon, Aug 22, 2005 at 03:56:48PM -0700, Josh Berkus wrote:
> Folks,
>
> First off, I was going through PostgreSQL.conf.sample, and noticed that the
> bgwriter GUCs had multiplied:
>
> #bgwriter_delay = 200 # 10-10000 milliseconds between rounds
> #bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers scanned in each
> round
> #bgwriter_lru_maxpages = 5 # 0-1000 buffers max written per round
> #bgwriter_all_percent = 0.333 # 0-100% of all buffers scanned in each
> round
> #bgwriter_all_maxpages = 5 # 0-1000 buffers max written per round
>
> I find the addition a little baffling, since previous tests ... both mine,
> and discussion of tests last December ... showed that manipulating the
> bgwriter variables had no useful effects, and one might as well leave them
> alone. For example, I ran this test series on the 7/3 CVS:
>
> bgwriter_delay Default bgwriter_maxpages Default 1260
> bgwriter_delay 200 bgwriter_maxpages 100 1266
> bgwriter_delay 100 bgwriter_maxpages 100 1270
> bgwriter_delay 100 bgwriter_maxpages 200 1223
> bgwriter_delay 100 bgwriter_maxpages 500 1261
> bgwriter_delay 200 bgwriter_maxpages 500 1256
> bgwriter_delay 50 bgwriter_maxpages 100 1221
>
> I need to re-run this with some of the new wal_buffer and checkpoint stuff,
> but as you can see the only variation I've encountered so far has been
> random noise.

But have you looked at how this affects response time, especially around
checkpoints? Testing I've done shows that changing the variables in
8.0.3 can markedly reduce the impact of checkpoints. In many
applications, maintaining low response times is more important than
overall throughput, and that's where bgwriter tuning currently comes
into play. Depending on hardware it may not be possible to simplify all
these into a single number, either.

BTW, is there any way to monitor bgwriter activity, or the number of
dirty pages? It would make bgwriter tuning much easier if you had some
idea of how much work was being done by checkpoints vs bgwriter.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com 512-569-9461