Re: Spread checkpoint sync

Tom Lane wrote:
> I wonder whether it'd be useful to keep track of the total amount of
> data written-and-not-yet-synced, and to issue fsyncs often enough to
> keep that below some parameter; the idea being that the parameter would
> limit how much dirty kernel disk cache there is. Of course, ideally the
> kernel would have a similar tunable and this would be a waste of effort
> on our part...
>

I wanted to run the tests again before reporting in detail here, because
the results are so bad, but I threw out an initial report about trying
to push this toward this down to be the kernel's job at
http://blog.2ndquadrant.com/en/2011/01/tuning-linux-for-low-postgresq.html

So far it looks like the newish Linux dirty_bytes parameter works well
at reducing latency by limiting how much dirty data can pile up before
it gets nudged heavily toward disk. But the throughput drop you pay on
VACUUM in particular is brutal, I'm seeing over a 50% slowdown in some
cases. I suspect we need to let the regular cleaner and backend writes
queue up in the largest possible cache for VACUUM, so it benefits as
much as possible from elevator sorting of writes. I suspect this being
the worst case now for a tightly controlled write cache is an unintended
side-effect of the ring buffer implementation it uses now.

Right now I'm running the same tests on XFS instead of ext3, and those
are just way more sensible all around; I'll revisit this on that
filesystem and ext4. The scale=500 tests I've running lots of lately
are a full 3X TPS faster on XFS relative to ext3, with about 1/8 as much
worst-case latency.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books