Let's drop some GUCs (bgwriter)

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Let's drop some GUCs (bgwriter)

Josh berkus
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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

Re: Let's drop some GUCs (bgwriter)

Jim C. Nasby
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      [hidden email]
Pervasive Software        http://pervasive.com        512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: Let's drop some GUCs (bgwriter)

Tom Lane-2
In reply to this post by Josh berkus
Josh Berkus <[hidden email]> 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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

Re: Let's drop some GUCs (bgwriter)

Josh berkus
In reply to this post by Jim C. Nasby
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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match