From: Simon Riggs <simon@2ndquadrant.com>
To: guoping.zhang@nec.com.au
Cc: pgsql-performance@postgresql.org, "Guoping Zhang (E-mail)" <guopingz@nstc.nec.com.au>
Subject: Re: how unsafe (or worst scenarios) when setting fsync
Date: Thu, 27 Apr 2006 08:13:04 +0100
Message-ID: <1146121984.3120.56.camel@localhost.localdomain> (raw)
In-Reply-To: <003b01c669c4$34bbd7b0$74304c93@eddy>
References: <003b01c669c4$34bbd7b0$74304c93@eddy>
On Thu, 2006-04-27 at 16:31 +1000, Guoping Zhang wrote:
> We have to looking at setting fsync OFF option for performance reason,
> our questions are
>
> a) if we set fsync OFF and anything (very low chance though) like OS
> crash, loss of power, or hardware fault happened, can postgresql rolls back
> to the state that the last checkpoint was done ( but all the operations
> after that is lost)
There is no rollback, only a rollforward from the checkpoint.
> b) Does this roll back to last checkpoint can ensure the database back to
> consistent state?
Therefore no consistent state guaranteed if some WAL is missing
> c) What is worst scenarios if setting fsync OFF in term of database
> safety. We try to avoid to restore the database from nightly backup.
Losing some DDL changes, probably. You'd need to be wary of things like
ANALYZE, VACUUM etc, since these make catalog changes also.
> We view our application is not that data loss critical, say loss of five
> minutes of data and operation occasionally, but the database integrity and
> consistency must be kept.
>
> Can we set fsync OFF for the performance benefit, have the risk of only 5
> minutes data loss or much worse?
Thats up to you.
fsync can be turned on and off, so you can make critical changes with
fsync on, then continue with fsync off.
The risk and the decision, are yours. You are warned.
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.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-performance@postgresql.org
Cc: simon@2ndquadrant.com, guoping.zhang@nec.com.au, guopingz@nstc.nec.com.au
Subject: Re: how unsafe (or worst scenarios) when setting fsync
In-Reply-To: <1146121984.3120.56.camel@localhost.localdomain>
* 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