On Fri, 24 Aug 2007, Kevin Grittner wrote:
> 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.
You should take a look at the "io storm on checkpoints" thread on the
pgsql-performance@postgresql.org started by Dmitry Potapov on 8/22 if you
aren't on that list. He was running into the same problem as you (and me
and lots of other people) and had an interesting resolution based on
turning the Linux kernel so that it basically stopped caching writes.
What you suggest here would be particularly inefficient because of how
much extra I/O would happen on the index blocks involved in the active
tables.
> 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.
The background writer code that's in 8.2 can be configured as a big
sledgehammer that happens to help in this area while doing large amounts
of collateral damage via writing things prematurely. Some of the people
involved in the 8.3 code rewrite and testing were having the same problem
as you on a similar scale--I recall Greg Stark commenting that he had a
system that was freezing for a full 30 seconds the way yours was.
I would be extremely surprised to find that the code that's already in 8.3
isn't a big improvement over what you're doing now based on how much it
has helped others running into this issue. And much of the code that
you're relying on now to help with the problem (the all-scan portion of
the BGW) has already been removed as part of that.
Switching to my Agent Smith voice: "No Kevin, your old background writer
is already dead". You'd have to produce some really unexpected and
compelling results during the beta period for it to get put back again.
The work I'm still doing here is very much fine-tuning in comparision to
what's already been committed into 8.3.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD