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

On Fri, 31 Aug 2007, Jan Wieck wrote:

> Again, the original theory for the bgwriter wasn't moving writes out of the 
> critical path, but smoothing responsetimes that tended to go completely down 
> the toilet during checkpointing, causing all the users to wake up and 
> overload the system entirely.

As far as I'm concerned, that function of the background writer has been 
replaced by the load distributed checkpoint features now controlled by 
checkpoint_completion_target, which is believed to be a better solution in 
several respects.  I'm been trying to motivate people happily using the 
current background writer to confirm or deny that during beta, while 
there's still time to put the all-scan portion that was removed back 
again.

The open issue I'm working on is whether the LRU cleaner running in 
advance of the Strategy point is still a worthwhile addition on top of 
that.

My own tests with pgbench that I'm busy wrapping up today haven't provided 
many strong conclusions here; the raw data is now on-line at 
http://www.westnet.com/~gsmith/content/bgwriter/ , am working on 
summarizing it usefully and bundling the toolchain I used to run all 
those.  I'll take a look at whether TCP-W provides a helpfully different 
view here because as far as I'm aware that's a test neither myself or 
Heikki has tried yet to investigate this area.

> It is well known that any kind of bgwriter configuration other than OFF does 
> increase the total IO cost. But you will find that everyone who has SLA's 
> that define maximum response times will happily increase the IO bandwidth to 
> give an aggressively configured bgwriter room to work.

The old background writer couldn't be configured to be aggressive enough 
to satisfy some SLAs because of interactions with the underlying operating 
system write caches.  It actually made things worse in some situations 
because at the point when you hit a checkpoint, the OS/disk controller 
caches were already filled to capacity with writes of active pages, many 
of which were now being written again.  Had you just left the background 
writer off those caches would have had less data in them and better been 
able to absorb the storm of writes that come with the checkpoint.  This is 
particularly true in the situtation where you have a large caching disk 
controller that might chew GB worth of shared_buffers almost instantly 
were it mostly clean when the checkpoint storm begins, but if the 
background writer has been busy pounding at it then it's already full of 
data at checkpoint time.

We just talked about this for a bit at Bruce's back in July; the hardware 
you did your development against and what people are deploying nowadays 
are so different that the entire character of the problem has changed. 
The ability of the processors and memory to create dirty pages has gone up 
by at least one order of magnitude, and the sophistication of the disk 
controller on a high-end PostgreSQL server is pretty high now; the speed 
of the underlying disks haven't kept pace, and that gap has been making 
this particular problem worse every year.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD