Message-ID: <4B22CA3A.1040909@2ndquadrant.com>
Date: Fri, 11 Dec 2009 17:39:54 -0500
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: Scott Mead <scott.lists@enterprisedb.com>
CC: Nikolas Everett <nik9000@gmail.com>, jd <jd@commandprompt.com>,
	Andres Freund <andres@anarazel.de>,
	pgsql-performance <pgsql-performance@postgresql.org>,
	Michael Clemmons <glassresistor@gmail.com>
Subject: Re: 8.4.1 ubuntu karmic slow createdb
References: <4427a97a0912101235x3ade510blcc00d5ce9883c6b6@mail.gmail.com>	
	<200912102256.59868.andres@anarazel.de>	
	<4427a97a0912101401kb29e1a5ic5424d6d0b7efe62@mail.gmail.com>	
	<200912102309.04057.andres@anarazel.de>	
	<d4e11e980912101738t77b654b2j1db1c023eafd9a58@mail.gmail.com>	
	<1260554319.2611.10.camel@jd-desktop.unknown.charter.com>	
	<d4e11e980912111243s6b0a67b1u2c2c22031fadf756@mail.gmail.com>	
	<1260564610.2611.41.camel@jd-desktop.unknown.charter.com>	
	<d4e11e980912111339q355f9fb2rf444f47a208b5b1e@mail.gmail.com>
	<d3ab2ec80912111359s47925b0dl7ce54b849767bacc@mail.gmail.com>
In-Reply-To: <d3ab2ec80912111359s47925b0dl7ce54b849767bacc@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Scott Mead wrote:
> The other common issue is that developers running with something like 
> 'fsync=off' means that they have completely unrealistic expectations 
> of the performance surrounding something.
Right, but the flip side here is that often the production server will 
have hardware such as a caching RAID card that vastly improves 
performance in this area.  There's some room to cheat in order to 
accelerate the dev systems lack of such things, while still not giving a 
completely unrealistic view of performance.

As far as I'm concerned, using "fsync=off" is almost never excusable if 
you're running 8.3 or later where "synchronous_commit=off" is a 
possibility.  If you use that, it will usually improve the worst part of 
commit issues substantially.  And it happens in a way that's actually 
quite similar to how a caching write production server will run:  small 
writes happen instantly, but eventually bigger ones will end up 
bottlenecked at the disks anyway.

It would improve the average safety of our community members if anytime 
someone suggests "fsync=off", we strongly suggest 
"synchronous_commit=off" and potentially tuning its interval instead as 
a middle ground, while still helping people who need to speed their 
systems up.  Saying "never turn fsync off" without suggesting this 
alternative is counter-productive.  If you're in the sort of position 
where fsync is killing your performance you'll do anything to speed 
things up (I've seen a 100:1 speed improvement) no matter how risky.  
I've ran a production system of 8.2 with fsync off, a TB of data, and no 
safety net if a crash introduced corruption beyond a ZFS snapshot.  It 
wasn't fun, but it was the only possibility to get bulk loading (there 
was an ETL step in the middle after COPY) to happen fast enough.  Using 
async commit instead is a much better approach now that it's available.

-- 
Greg Smith    2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com  www.2ndQuadrant.com