>>> On Fri, Aug 24, 2007 at 7:41 AM, in message
<46CED1EF(dot)8010707(at)enterprisedb(dot)com>, "Heikki Linnakangas"
<heikki(at)enterprisedb(dot)com> wrote:
> I was not able to find a test where turning bgwriter on performed better
> than turning it off.
Any tests which focus just on throughput don't address the problems which
caused us so much grief. What we need is some sort of test which generates
a moderate write load in the background, while paying attention to the
response time of a large number of read-only queries. The total load should
not be enough to saturate the I/O bandwidth overall if applied evenly.
The problem which the background writer has solved for us is that we have
three layers of caching (PostgreSQL, OS, and RAID controller), each with its
own delay before writing; when something like fsync triggers a cascade from
one cache to the next, the write burst bottlenecks the I/O, and reads exceed
acceptable response times. The two approaches which seem to prevent this
problem are to disable all OS delays in writing dirty pages, or to minimize
the delays in PostgreSQL writing dirty pages.
Throughput is not everything. Response time matters.
> If anyone out there has a repeatable test case where bgwriter does help,
> I'm all ears.
All we have is a production system where PostgreSQL failed to perform at a
level acceptable to the users without it.
> 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.
I would be fine with that if I could configure the back end to always write a
dirty page to the OS when it is written to shared memory. That would allow
Linux and XFS to do their job in a timely manner, and avoid this problem.
I know we're doing more in 8.3 to move this from the OS's realm into
PostgreSQL code, but until I have a chance to test that, I want to make sure
that what has been proven to work for us is not broken.
-Kevin