Message-ID: <4BE890C6.5010907@2ndquadrant.com>
Date: Mon, 10 May 2010 19:03:34 -0400
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Josh Berkus <josh@agliodbs.com>
CC: Kevin Grittner <Kevin.Grittner@wicourts.gov>, =?UTF-8?B?Q8OpZHJpYyBWaQ==?=
	=?UTF-8?B?bGxlbWFpbg==?= <cedric.villemain.debian@gmail.com>,
	Bernd Helmle <mailings@oopsware.de>,
	Andrew Dunstan <andrew@dunslane.net>, pgsql-hackers@postgresql.org
Subject: Re: no universally correct setting for fsync
References: <4BE3D3930200002500031382@gw.wicourts.gov>
	<4BE425F7.30804@dunslane.net>
	<4BE3E205020000250003138F@gw.wicourts.gov>
	<A1EBC6E5100210B51612365E@amenophis>
	<AANLkTin_XJNc3uxB5730VUbeovj2QtHV3JZc5tJT9Zup@mail.gmail.com>
	<4BE82D6602000025000314D6@gw.wicourts.gov>
	<4BE87892.5060409@agliodbs.com>
In-Reply-To: <4BE87892.5060409@agliodbs.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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@2ndQuadrant.com   www.2ndQuadrant.us