Re: Let's drop some GUCs (bgwriter)

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.

That given, do we really need to add more settings? Can't we drop these
and just have them be constants in the code?

I'm thinking for 8.2 we should just have
bgwriter_activity = 1 #frequency and size of bgwriter batches,
# on a 1 .. 10 scale
... since that's as much of a handle as anyone needs, if they need any at
all.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

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

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> 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.

Perhaps that just proves that you're using a test that's insensitive to
bgwriter effects; or that the defaults are so far off optimal that the
range of values you experimented with are all equally bad. Certainly
you cannot point to tests from last December as meaning anything
relevant to CVS tip, because the bgwriter algorithms have changed
completely since then.

The fact is that we don't know optimal settings for these values, and
removing the variables that make it convenient to experiment with them
will accomplish nothing except to guarantee that no one ever does
experiment enough to find out what the optimal values are.

regards, tom lane

Jim,

> 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.

Hmmm ... can you give me the settings you used? I'd like to check the
response times.

--Josh

--
Josh Berkus
Aglio Database Solutions
San Francisco