From: Greg Smith <greg@2ndquadrant.com>
To: Scott Mead <scott.lists@enterprisedb.com>
Cc: Nikolas Everett <nik9000@gmail.com>
Cc: jd <jd@commandprompt.com>
Cc: Andres Freund <andres@anarazel.de>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Cc: Michael Clemmons <glassresistor@gmail.com>
Subject: Re: 8.4.1 ubuntu karmic slow createdb
Date: Fri, 11 Dec 2009 17:39:54 -0500
Message-ID: <4B22CA3A.1040909@2ndquadrant.com> (raw)
In-Reply-To: <d3ab2ec80912111359s47925b0dl7ce54b849767bacc@mail.gmail.com>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: greg@2ndquadrant.com, scott.lists@enterprisedb.com, nik9000@gmail.com, jd@commandprompt.com, andres@anarazel.de, pgsql-performance@postgresql.org, glassresistor@gmail.com
Subject: Re: 8.4.1 ubuntu karmic slow createdb
In-Reply-To: <4B22CA3A.1040909@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox