Re: Sorting writes during checkpoint

On Fri, 4 Jul 2008, Simon Riggs wrote:

> No action on this seen since last commitfest, but I think we should do
> something with it, rather than just ignore it.

Just no action worth reporting yet. Over the weekend I finally reached
the point where I've got a system that should be capable of independently
replicating the results improvement setup here, and I've started
performance testing of the patch. Getting useful checkpoint test results
from pgbench is really a pain.

> Sorting by file might have inadvertently shown benefit at the tablespace
> level on a larger server with spread out data whereas on Tom's test
> system I would guess just a single tablespace was used.

I doubt this has anything to do with it, only because the pgbench schema
doesn't split into tablespaces usefully. Almost all of the real action is
on a single table, accounts.

My suspicion is that sorting only benefits in situations where you have a
disk controller with a significant amount of RAM on it, but the server RAM
is much larger. In that case the sorting horizon of the controller itself
is smaller than what the server can do, and the sorting makes it less
likely you'll end up with the controller filled with unsorted stuff that
takes a long time to clear.

In Tom's test, there's probably only 8 or 16MB worth of cache on the disk
itself, so you can't get a large backlog of unsorted writes clogging the
write pipeline. But most server systems have 256MB or more of RAM there,
and if you get that filled with seek-heavy writes (which might only clear
at a couple of MB a second) the delay for that cache to empty can be
considerable.

That said, I've got a 256MB controller here and have a very similar disk
setup to the one postiive results were reported on, but so far I don't see
any significant difference after applying the sorted writes patch.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD