Re: no universally correct setting for fsync

Josh Berkus wrote:
>> Wow. In a situation where you save seven minutes (4%), it's hardly
>> worth turning off.
>>
>
> I've had it be much higher, especially for really large databases.
>

Cedric's system had a non-volatile write cache in it. In that case, a
few percentage points of improvement is normal--the overhead of fsync is
very low. In the case where you don't have one of those, and the write
cache on the drives are turned off for safety too, I've seen turning
fsync off be a 40X speedup--100 inserts/second jumping to 4000TPS.
(This was before synchronous_commit).

The real question is how much of a speed-up fsync provides compared to
the same workload with synchronous_commit disabled. The only case for
fsync=off is one where that number is much faster. That's the case on
some low-level operations (I seem to recall there is no async commit
speedup for CREATE DATABASE for example). But for most of what people
want to speed, just killing sync commit while keeping fsync is on is
good enough. I suspect there are still some bulk-load workloads where
fsync=off helps beyond just going for async commit, but they're tougher
to find and the difference isn't huge relative to total load times.

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