Gregory Stark wrote: > "Tom Lane" <[EMAIL PROTECTED]> writes: > >> Greg Smith <[EMAIL PROTECTED]> 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 that far. 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 ---------------------------(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