Re: Spread checkpoint sync

Heikki Linnakangas wrote:
> If you fsync() a file with one dirty page in it, it's going to return
> very quickly, but a 1GB file will take a while. That could be
> problematic if you have a thousand small files and a couple of big
> ones, as you would want to reserve more time for the big ones. I'm not
> sure what to do about it, maybe it's not a problem in practice.

It's a problem in practice allright, with the bulk-loading situation
being the main one you'll hit it. If somebody is running a giant COPY
to populate a table at the time the checkpoint starts, there's probably
a 1GB file of dirty data that's unsynced around there somewhere. I
think doing anything about that situation requires an additional leap in
thinking about buffer cache evicition and fsync absorption though.
Ultimately I think we'll end up doing sync calls for relations that have
gone "cold" for a while all the time as part of BGW activity, not just
at checkpoint time, to try and avoid this whole area better. That's a
lot more than I'm trying to do in my first pass of improvements though.

In the interest of cutting the number of messy items left in the
official CommitFest, I'm going to mark my patch here "Returned with
Feedback" and continue working in the general direction I was already
going. Concept shared, underlying patches continue to advance, good
discussion around it; those were my goals for this CF and I think we're
there.

I have a good idea how to autotune the sync spread that's hardcoded in
the current patch. I'll work on finishing that up and organizing some
more extensive performance tests. Right now I'm more concerned about
finishing the tests around the wal_sync_method issues, which are related
to this and need to get sorted out a bit more urgently.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services and Support www.2ndQuadrant.us