Date: Sat, 19 Apr 2008 15:29:38 -0400 (EDT)
From: Greg Smith <gsmith@gregsmith.com>
To: James Mansion <james@mansionfamily.plus.com>
cc: Marinos Yannikos <mjy@geizhals.at>, pgsql-performance@postgresql.org
Subject: Re: Background writer underemphasized ...
In-Reply-To: <480A0A5D.8060604@mansionfamily.plus.com>
Message-ID: <Pine.GSO.4.64.0804191514120.15360@westnet.com>
References: <480619CA.2080904@geizhals.at>
	<Pine.GSO.4.64.0804161524410.27404@westnet.com>
	<48079AED.8090906@geizhals.at>
	<Pine.GSO.4.64.0804190507000.5519@westnet.com>
	<480A0A5D.8060604@mansionfamily.plus.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed

On Sat, 19 Apr 2008, James Mansion wrote:

> But isn't it the case that while using background writer might result in 
> *slightly* more data to write (since data that is updated several times 
> might actually be sent several times), the total amount of data in both 
> cases is much the same?

Really depends on your workload, how many wasted writes there are.  It 
might be significant, it might only be slight.

> And if the buffer backed up in the BGW case, wouldn't it also back up 
> (more?) if the writes are deferred?  And in fact by sending earlier, the 
> real bottleneck (the disks) could have been getting on with it and 
> staring their IO earlier?

If you write a giant block of writes, those tend to be sorted by the OS 
and possibly the controller to reduce total seeks.  That's a pretty 
efficient write and it can clear relatively fast.

But if you're been trickling writes in an unstructured form and in low 
volume, there can be a stack of them that aren't sorted well blocking the 
queue from clearing.  With a series of small writes, it's not that 
difficult to end up in a situation where a controller cache is filled with 
writes containing a larger seek component than you'd have gotten had you 
written in larger blocks that took advantage of more OS-level elevator 
sorting.  There's actually a pending patch to try and improve this 
situation in regards to checkpoint writes in the queue.

Seeks are so slow compared to more sequential writes that you really can 
end up in the counterintuitive situation that you finish faster by 
avoiding early writes, even in cases when the disk is the bottleneck.

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