Re: Spread checkpoint sync

On Fri, Feb 4, 2011 at 2:08 PM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:
> -The total number of buffers I'm computing based on the checkpoint writes
> being sorted it not a perfect match to the number reported by the
> "checkpoint complete" status line.  Sometimes they are the same, sometimes
> not.  Not sure why yet.

My first guess would be that in the cases where it's not the same,
some backend evicted the buffer before the background writer got to
it. That's expected under heavy contention for shared_buffers.

> -The estimate for "expected to need sync" computed as a by-product of the
> checkpoint sorting is not completely accurate either.  This particular one
> has a fairly large error in it, percentage-wise, being off by 3 with a total
> of 11.  Presumably these are absorbed fsync requests that were already
> queued up before the checkpoint even started.  So any time estimate I drive
> based off of this count is only going to be approximate.

As previously noted, I wonder if we ought sync queued-up requests that
don't require writes before beginning the write phase.

> -The order in which the sync phase processes files is unrelated to the order
> in which they are written out.  Note that 17216.10 here, the biggest victim
> (cause?) of the I/O spike, isn't even listed among the checkpoint writes!

That's awful. If more than 50% of the I/O is going to happen from one
fsync() call, that seems to put a pretty pessimal bound on how much
improvement we can hope to achieve here. Or am I missing something?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company