Re: Final background writer cleanup for 8.3 - Mailing list pgsql-hackers

Gregory Stark wrote:
> "Tom Lane" <tgl@sss.pgh.pa.us> writes:
> 
>> Greg Smith <gsmith@gregsmith.com> writes:
>>> In the interest of closing work on what's officially titled the "Automatic 
>>> adjustment of bgwriter_lru_maxpages" patch, I wanted to summarize where I 
>>> think this is at ...
>>> 2) Having backends write their own buffers out does not significantly 
>>> degrade performance, as those turn into cached OS writes which generally 
>>> execute fast enough to not be a large drag on the backend.
>> [ itch... ]  That assumption scares the heck out of me.  It is doubtless
>> true in a lightly loaded system, but once the kernel is under any kind
>> of memory pressure I think it's completely wrong.  I think designing the
>> system around this assumption will lead to something that performs great
>> as long as you're not pushing it hard.
> 
> I think Heikki's experiments showed it wasn't true for at least some kinds of
> heavy loads. However I would expect it to depend heavily on just what kind of
> load the machine is under. At least if it's busy writing then I would expect
> it to throttle writes. Perhaps in TPCC there are enough reads to throttle the
> write rate to something the kernel can buffer. 

I ran a bunch of DBT-2 in different configurations, as well as simple
single-threaded tests like random DELETEs on a table with index, steady
rate of INSERTs to a table with no indexes, and bursts of INSERTs with
different bursts sizes and delays between them. I tried the tests with
different bgwriter settings, including turning it off and with the patch
applied, and with different shared_buffers settings.

I was not able to find a test where turning bgwriter on performed better
than turning it off.

If anyone out there has a repeatable test case where bgwriter does help,
I'm all ears. The theory of moving the writes out of the critical path
does sound reasonable, so I'm sure there is test case to demonstrate the
effect, but it seems to be pretty darn hard to find.

The cold, rational side of me says we need a test case to show the
benefit, or if one can't be found, we should remove bgwriter altogether.The emotional side of me tells me we can't go
thatfar. A reasonable
 
compromise would be to apply the autotuning patch on the grounds that it
removes a GUC variable that's next to impossible to tune right, even
though we can't show a performance benefit compared to bgwriter=off. And
it definitely makes sense not to restart the scan from the clock sweep
hand on each bgwriter round; as Tom pointed out, it's a waste of time.

--  Heikki Linnakangas EnterpriseDB   http://www.enterprisedb.com