Обсуждение: 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@pervasive.com Pervasive Software http://pervasive.com 512-569-9461
Josh Berkus <josh@agliodbs.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