Re: Insert are going slower ... - Mailing list pgsql-performance

From Josh Berkus
Subject Re: Insert are going slower ...
Date
Msg-id 200407181023.22004.josh@agliodbs.com
Whole thread Raw
In response to Re: Insert are going slower ...  (Hervé Piedvache <herve@elma.fr>)
List pgsql-performance
Herve'

> Hum ... it's only for speed aspect ... I was using postgresql with this
> option since 7.01 ... and for me fsync=on was so slow ...
> Is it really no time consuming for the system to bring it ON now with
> v7.4.3 ??

Well, I wouldn't do it until you've figured out the current performance
problem.

The issue with having fsync=off is that, if someone yanks the power cord on
your server, there is a significant chance that you will have to restore the
database from backup becuase it will be corrupted.   But clearly you've been
living with that risk for some time.

It *is* true that there is significantly less performance difference between
7.4 with fsync off and on than there was between 7.1 with fsync off and on.
But there is still a difference.   In 7.0 and 7.1 (I think), when you turned
fsync off it turned WAL off completely, resulting in a substantial difference
in disk activity.   Now, it just stops checkpointing WAL but WAL is still
recording -- meaning that disk activity decreases some but not a lot.   The
difference is more noticable the more vulnerable to contention your disk
system is.

The other reason not to think of fsync=off as a permanent performance tweak is
that we're likely to remove the option sometime in the next 2 versions, since
an increasing number of features depend on WAL behavior, and the option is
largely a legacy of the 7.0 days, when WAL was sometimes buggy and needed to
be turned off to get the database to start.

--
Josh Berkus
Aglio Database Solutions
San Francisco

pgsql-performance by date:

Previous
From: Gaetano Mendola
Date:
Subject: Re: High load average with PostgreSQL 7.4.2 on debian/ibm eserver.
Next
From: bsimon@loxane.com
Date:
Subject: NAS, SAN or any alternate solution ?