public inbox for [email protected]
help / color / mirror / Atom feedno universally correct setting for fsync
35+ messages / 16 participants
[nested] [flat]
* no universally correct setting for fsync
@ 2010-05-07 13:47 Kevin Grittner <[email protected]>
0 siblings, 3 replies; 35+ messages in thread
From: Kevin Grittner @ 2010-05-07 13:47 UTC (permalink / raw)
To: [email protected]
Someone just posted to the -admin list with a database corrupted
while running with fsync=off. I was all set to refer him to the
documentation explaining why he should stop doing this, but to my
surprise the documentation waffles on the issue way past what I
think is reasonable.
http://www.postgresql.org/docs/8.4/interactive/runtime-config-wal.html#GUC-FSYNC
There are dire-sounding statements interspersed with:
| using fsync results in a performance penalty
| Due to the risks involved, there is no universally correct setting
| for fsync.
| If you trust your operating system, your hardware, and your
| utility company (or your battery backup), you can consider
| disabling fsync.
Isn't this a little too rosy a picture to paint?
-Kevin
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: no universally correct setting for fsync
@ 2010-05-07 14:00 Robert Haas <[email protected]>
parent: Kevin Grittner <[email protected]>
2 siblings, 1 reply; 35+ messages in thread
From: Robert Haas @ 2010-05-07 14:00 UTC (permalink / raw)
To: Kevin Grittner <[email protected]>; +Cc: [email protected]
On Fri, May 7, 2010 at 9:47 AM, Kevin Grittner
<[email protected]> wrote:
> Someone just posted to the -admin list with a database corrupted
> while running with fsync=off. I was all set to refer him to the
> documentation explaining why he should stop doing this, but to my
> surprise the documentation waffles on the issue way past what I
> think is reasonable.
>
> http://www.postgresql.org/docs/8.4/interactive/runtime-config-wal.html#GUC-FSYNC
>
> There are dire-sounding statements interspersed with:
>
> | using fsync results in a performance penalty
>
> | Due to the risks involved, there is no universally correct setting
> | for fsync.
>
> | If you trust your operating system, your hardware, and your
> | utility company (or your battery backup), you can consider
> | disabling fsync.
>
> Isn't this a little too rosy a picture to paint?
I agree. I've always thought this part of the documentation made
setting fsync=off much more reasonable than I feel it to be.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: no universally correct setting for fsync
@ 2010-05-07 14:03 Magnus Hagander <[email protected]>
parent: Robert Haas <[email protected]>
0 siblings, 0 replies; 35+ messages in thread
From: Magnus Hagander @ 2010-05-07 14:03 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Kevin Grittner <[email protected]>; pgsql-hackers <[email protected]>
On Fri, May 7, 2010 at 16:00, Robert Haas <[email protected]> wrote:
> On Fri, May 7, 2010 at 9:47 AM, Kevin Grittner
> <[email protected]> wrote:
>> Someone just posted to the -admin list with a database corrupted
>> while running with fsync=off. I was all set to refer him to the
>> documentation explaining why he should stop doing this, but to my
>> surprise the documentation waffles on the issue way past what I
>> think is reasonable.
>>
>> http://www.postgresql.org/docs/8.4/interactive/runtime-config-wal.html#GUC-FSYNC
>>
>> There are dire-sounding statements interspersed with:
>>
>> | using fsync results in a performance penalty
>>
>> | Due to the risks involved, there is no universally correct setting
>> | for fsync.
>>
>> | If you trust your operating system, your hardware, and your
>> | utility company (or your battery backup), you can consider
>> | disabling fsync.
>>
>> Isn't this a little too rosy a picture to paint?
>
> I agree. I've always thought this part of the documentation made
> setting fsync=off much more reasonable than I feel it to be.
+1, definitely. fsync=off should only be done if you *really*
understand what it means, and that requires a lot more explanation
than that...
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: no universally correct setting for fsync
@ 2010-05-07 14:24 Tom Lane <[email protected]>
parent: Kevin Grittner <[email protected]>
2 siblings, 0 replies; 35+ messages in thread
From: Tom Lane @ 2010-05-07 14:24 UTC (permalink / raw)
To: Kevin Grittner <[email protected]>; +Cc: [email protected]
"Kevin Grittner" <[email protected]> writes:
> | If you trust your operating system, your hardware, and your
> | utility company (or your battery backup), you can consider
> | disabling fsync.
> Isn't this a little too rosy a picture to paint?
I think that statement is true as far as it goes, but I agree with
rejiggering the surrounding text. The whole thing was written back
when Postgres was by far the least reliable component of the stack.
It isn't anymore. We should make it clear that fsync=off is not ever
recommended for production.
regards, tom lane
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: no universally correct setting for fsync
@ 2010-05-07 14:38 Andrew Dunstan <[email protected]>
parent: Kevin Grittner <[email protected]>
2 siblings, 1 reply; 35+ messages in thread
From: Andrew Dunstan @ 2010-05-07 14:38 UTC (permalink / raw)
To: Kevin Grittner <[email protected]>; +Cc: [email protected]
Kevin Grittner wrote:
>
> There are dire-sounding statements interspersed with:
>
> | using fsync results in a performance penalty
>
> | Due to the risks involved, there is no universally correct setting
> | for fsync.
>
> | If you trust your operating system, your hardware, and your
> | utility company (or your battery backup), you can consider
> | disabling fsync.
>
> Isn't this a little too rosy a picture to paint?
>
>
>
I think the critical question is really whether you are prepared to lose
your database.
I have a customer who rotates databases in and out of line, and
processes major updates on the out of line database. If they lose the
database occasionally they are prepared to wear that risk for the
performance gain they get from running with fsync off. It just means
that they have to recover and so the inline database will get a bit
staler than usual while they do.
So I think its true that there is no universally right answer. Maybe the
criteria mentioned in the last para need tweaking some, though. It's not
just a matter of trusting hardware etc. I have seen mishaps when idiots
knock out power cords and the like. The unexpected does sometime happen,
despite the best planning.
cheers
andrew
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: no universally correct setting for fsync
@ 2010-05-07 14:48 Kevin Grittner <[email protected]>
parent: Andrew Dunstan <[email protected]>
0 siblings, 2 replies; 35+ messages in thread
From: Kevin Grittner @ 2010-05-07 14:48 UTC (permalink / raw)
To: Andrew Dunstan <[email protected]>; +Cc: [email protected]
Andrew Dunstan <[email protected]> wrote:
> I think the critical question is really whether you are prepared
> to lose your database.
Precisely; and the docs don't make that at all clear. They mention
the possibility of database corruption, but downplay it:
| When fsync is disabled, the operating system is allowed to do its
| best in buffering, ordering, and delaying writes. This can result
| in significantly improved performance. However, if the system
| crashes, the results of the last few committed transactions might
| be lost in part or whole. In the worst case, unrecoverable data
| corruption might occur.
> [valid use case for fsync=off]
>
> So I think its true that there is no universally right answer.
> Maybe the criteria mentioned in the last para need tweaking some,
> though.
I think it goes beyond "tweaking" -- I think we should have a bald
statement like "don't turn this off unless you're OK with losing the
entire contents of the database cluster." A brief listing of some
cases where that is OK might be illustrative.
I never meant to suggest any statement in that section is factually
wrong; it's just all too rosy, leading people to believe it's no big
deal to turn it off.
-Kevin
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: no universally correct setting for fsync
@ 2010-05-07 17:56 Josh Berkus <[email protected]>
parent: Kevin Grittner <[email protected]>
1 sibling, 2 replies; 35+ messages in thread
From: Josh Berkus @ 2010-05-07 17:56 UTC (permalink / raw)
To: [email protected]
> I never meant to suggest any statement in that section is factually
> wrong; it's just all too rosy, leading people to believe it's no big
> deal to turn it off.
Yeah, that section is overdue for an update. I'll write some new text
and post it to pgsql-docs.
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: no universally correct setting for fsync
@ 2010-05-07 23:32 Bernd Helmle <[email protected]>
parent: Kevin Grittner <[email protected]>
1 sibling, 2 replies; 35+ messages in thread
From: Bernd Helmle @ 2010-05-07 23:32 UTC (permalink / raw)
To: Kevin Grittner <[email protected]>; Andrew Dunstan <[email protected]>; +Cc: [email protected]
--On 7. Mai 2010 09:48:53 -0500 Kevin Grittner
<[email protected]> wrote:
> I think it goes beyond "tweaking" -- I think we should have a bald
> statement like "don't turn this off unless you're OK with losing the
> entire contents of the database cluster." A brief listing of some
> cases where that is OK might be illustrative.
>
+1
> I never meant to suggest any statement in that section is factually
> wrong; it's just all too rosy, leading people to believe it's no big
> deal to turn it off.
I think one mistake in this paragraph is the passing mention of
"performance". I've seen installations in the past with fsync=off only
because the admin was pressured to get instantly "more speed" out of the
database (think of "fast_mode=on"). In my opinion, phrases like
"performance penalty" are misleading, if you need that setting in 99% of
all use cases for reliable operation.
I've recently even started to wonder if the performance gain with fsync=off
is still that large on modern hardware. While testing large migration
procedures to a new version some time ago (on an admitedly fast storage) i
forgot here and then to turn it off, without a significant degradation in
performance.
--
Thanks
Bernd
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: no universally correct setting for fsync
@ 2010-05-07 23:49 Tom Lane <[email protected]>
parent: Bernd Helmle <[email protected]>
1 sibling, 1 reply; 35+ messages in thread
From: Tom Lane @ 2010-05-07 23:49 UTC (permalink / raw)
To: Bernd Helmle <[email protected]>; +Cc: Kevin Grittner <[email protected]>; Andrew Dunstan <[email protected]>; [email protected]
Bernd Helmle <[email protected]> writes:
> I've recently even started to wonder if the performance gain with fsync=off
> is still that large on modern hardware. While testing large migration
> procedures to a new version some time ago (on an admitedly fast storage) i
> forgot here and then to turn it off, without a significant degradation in
> performance.
That says to me either that you're using a battery-backed write cache,
or your fsyncs don't really work (no write barriers or something like
that).
regards, tom lane
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: [HACKERS] no universally correct setting for fsync
@ 2010-05-08 00:02 Josh Berkus <[email protected]>
parent: Josh Berkus <[email protected]>
1 sibling, 1 reply; 35+ messages in thread
From: Josh Berkus @ 2010-05-08 00:02 UTC (permalink / raw)
To: pgsql-docs
Folks,
This is what I have to replace the current fsync entry in config.sgml.
I believe that the note about needing fsync for Warm Standby to work
correctly is true, but could someone verify it?
=========================
<varlistentry id="guc-fsync" xreflabel="fsync">
<indexterm>
<primary><varname>fsync</> configuration parameter</primary>
</indexterm>
<term><varname>fsync</varname> (<type>boolean</type>)</term>
<listitem>
<para>
If this parameter is on, the <productname>PostgreSQL</> server
will try to make sure that updates are physically written to
disk, by issuing <function>fsync()</> system calls or various
equivalent methods (see <xref linkend="guc-wal-sync-method">).
This ensures that the database cluster can recover to a
consistent state after an operating system or hardware crash.
</para>
<para>
While turning off <varname>fsync</varname> is often a performance
benefit, this can result in unrecoverable data corruption in the
event
of an unexpected shutdown. Thus it is only advisable to turn off
<varname>fsync</varname> if you can easily recreate
your entire database from external data. <varname>fsync</varname>
must be on for WAL archiving to work correctly
(see <xref linkend="continuous-archiving">).
<para>
<para>
In many situations, turning off <xref
linkend="guc-synchronous-commit">
for noncritical transactions can provide much of the potential
performance benefit of turning off <varname>fsync</varname>, without
the attendant risks of data corruption.
</para>
<para>
<varname>fsync</varname> can only be set in the
<filename>postgresql.conf</>
file or on the server command line.
If you turn this parameter off, also consider turning off
<xref linkend="guc-full-page-writes">.
</para>
</listitem>
</varlistentry>
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: [HACKERS] no universally correct setting for fsync
@ 2010-05-08 00:13 Tom Lane <[email protected]>
parent: Josh Berkus <[email protected]>
0 siblings, 1 reply; 35+ messages in thread
From: Tom Lane @ 2010-05-08 00:13 UTC (permalink / raw)
To: Josh Berkus <[email protected]>; +Cc: pgsql-docs
Josh Berkus <[email protected]> writes:
> This is what I have to replace the current fsync entry in config.sgml.
s/unexpected shutdown/system crash/, perhaps. The wording you have
suggests that a forced Postgres stoppage produces a problem, which it
doesn't. It takes a failure at the OS level or below to cause a
problem.
> I believe that the note about needing fsync for Warm Standby to work
> correctly is true, but could someone verify it?
AFAIK that's nonsense. The filesystem state that pg_standby could see
will be updated in any case; pg_standby has no direct access to the bits
on the platters, any more than Postgres does.
regards, tom lane
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: no universally correct setting for fsync
@ 2010-05-08 00:16 Bernd Helmle <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 0 replies; 35+ messages in thread
From: Bernd Helmle @ 2010-05-08 00:16 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Kevin Grittner <[email protected]>; Andrew Dunstan <[email protected]>; [email protected]
--On 7. Mai 2010 19:49:15 -0400 Tom Lane <[email protected]> wrote:
> Bernd Helmle <[email protected]> writes:
>> I've recently even started to wonder if the performance gain with
>> fsync=off is still that large on modern hardware. While testing large
>> migration procedures to a new version some time ago (on an admitedly
>> fast storage) i forgot here and then to turn it off, without a
>> significant degradation in performance.
>
> That says to me either that you're using a battery-backed write cache,
> or your fsyncs don't really work (no write barriers or something like
> that).
>
Well, yes, BBU present and proven storage. Maybe i'm wrong, but it seems
battery backed write caches aren't that seldom even in low end systems
nowadays.
--
Thanks
Bernd
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: [HACKERS] no universally correct setting for fsync
@ 2010-05-08 00:17 Josh Berkus <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 0 replies; 35+ messages in thread
From: Josh Berkus @ 2010-05-08 00:17 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: pgsql-docs
On 5/7/10 5:13 PM, Tom Lane wrote:
> Josh Berkus <[email protected]> writes:
>> This is what I have to replace the current fsync entry in config.sgml.
>
> s/unexpected shutdown/system crash/, perhaps. The wording you have
> suggests that a forced Postgres stoppage produces a problem, which it
> doesn't. It takes a failure at the OS level or below to cause a
> problem.
I actually meant "unexpected *system* shutdown", i.e. power-out. A lot
of people think "crash" just means kernel dump, whereas a UPS failure or
tripped power cord is a lot more likely (except maybe on Windows).
Revised:
==================
<varlistentry id="guc-fsync" xreflabel="fsync">
<indexterm>
<primary><varname>fsync</> configuration parameter</primary>
</indexterm>
<term><varname>fsync</varname> (<type>boolean</type>)</term>
<listitem>
<para>
If this parameter is on, the <productname>PostgreSQL</> server
will try to make sure that updates are physically written to
disk, by issuing <function>fsync()</> system calls or various
equivalent methods (see <xref linkend="guc-wal-sync-method">).
This ensures that the database cluster can recover to a
consistent state after an operating system or hardware crash.
</para>
<para>
While turning off <varname>fsync</varname> is often a performance
benefit, this can result in unrecoverable data corruption in the
event
of an unexpected system shutdown or crash. Thus it is only
advisable
to turn off <varname>fsync</varname> if you can easily recreate
your entire database from external data.
<para>
<para>
In many situations, turning off <xref
linkend="guc-synchronous-commit">
for noncritical transactions can provide much of the potential
performance benefit of turning off <varname>fsync</varname>, without
the attendant risks of data corruption.
</para>
<para>
<varname>fsync</varname> can only be set in the
<filename>postgresql.conf</>
file or on the server command line.
If you turn this parameter off, also consider turning off
<xref linkend="guc-full-page-writes">.
</para>
</listitem>
</varlistentry>
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: no universally correct setting for fsync
@ 2010-05-08 08:07 Craig Ringer <[email protected]>
parent: Josh Berkus <[email protected]>
1 sibling, 1 reply; 35+ messages in thread
From: Craig Ringer @ 2010-05-08 08:07 UTC (permalink / raw)
To: Josh Berkus <[email protected]>; +Cc: [email protected]
On 8/05/2010 1:56 AM, Josh Berkus wrote:
>
>> I never meant to suggest any statement in that section is factually
>> wrong; it's just all too rosy, leading people to believe it's no big
>> deal to turn it off.
>
> Yeah, that section is overdue for an update. I'll write some new text
> and post it to pgsql-docs.
It's probably worth mentioning that people who want to turn off fsync to
gain a performance boost should instead look at a RAID controller with a
BBU so they can safely enable write-back caching, getting most of the
benefits of fsync=off safely.
--
Craig Ringer
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: [HACKERS] no universally correct setting for fsync
@ 2010-05-08 13:09 Kevin Grittner <[email protected]>
0 siblings, 0 replies; 35+ messages in thread
From: Kevin Grittner @ 2010-05-08 13:09 UTC (permalink / raw)
To: [email protected]; pgsql-docs
Josh Berkus wrote:
> I believe that the note about needing fsync for Warm Standby to
> work correctly is true, but could someone verify it?
It couldn't really affect the archiving of the WAL files, but if your
warm standby is there for recovery purposes, it might not make a lot
of sense to turn off fsync on the standby -- if that is something
which has an effect during the recovery phase. Does it?
Also, perhaps the issue deserves some mention in the PITR recovery
section:
http://www.postgresql.org/docs/9.0/static/continuous-archiving.html#BACKUP-PITR-RECOVERY
Step 6 says:
| If you have unarchived WAL segment files that you saved in step 2,
| copy them into pg_xlog/. (It is best to copy them, not move them,
| so you still have the unmodified files if a problem occurs and you
| have to start over.)
If the recovery is happening because of OS or hardware failure on the
source, and it was running with fsync off, this might be unwise.
-Kevin
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: no universally correct setting for fsync
@ 2010-05-10 14:48 Michael Tharp <[email protected]>
parent: Craig Ringer <[email protected]>
0 siblings, 1 reply; 35+ messages in thread
From: Michael Tharp @ 2010-05-10 14:48 UTC (permalink / raw)
To: Craig Ringer <[email protected]>; +Cc: [email protected]
On 05/08/2010 04:07 AM, Craig Ringer wrote:
> It's probably worth mentioning that people who want to turn off fsync to
> gain a performance boost should instead look at a RAID controller with a
> BBU so they can safely enable write-back caching, getting most of the
> benefits of fsync=off safely.
Which options specifically should be set if a BBU is in use? Obviously
fsync should be on always, but can full_page_writes be disabled? Are
there other tweaks that can be done?
It would be great to see some practical hints in the documentation while
the fsync part is getting changed.
-- m. tharp
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: no universally correct setting for fsync
@ 2010-05-10 15:12 Bruce Momjian <[email protected]>
parent: Michael Tharp <[email protected]>
0 siblings, 1 reply; 35+ messages in thread
From: Bruce Momjian @ 2010-05-10 15:12 UTC (permalink / raw)
To: Michael Tharp <[email protected]>; +Cc: Craig Ringer <[email protected]>; [email protected]
Michael Tharp wrote:
> On 05/08/2010 04:07 AM, Craig Ringer wrote:
> > It's probably worth mentioning that people who want to turn off fsync to
> > gain a performance boost should instead look at a RAID controller with a
> > BBU so they can safely enable write-back caching, getting most of the
> > benefits of fsync=off safely.
>
> Which options specifically should be set if a BBU is in use? Obviously
> fsync should be on always, but can full_page_writes be disabled? Are
> there other tweaks that can be done?
>
> It would be great to see some practical hints in the documentation while
> the fsync part is getting changed.
Uh, our docs have:
Turning this parameter off speeds normal operation, but might
lead to a corrupt database after an operating system crash or
power failure. The risks are similar to turning off
<varname>fsync</>, though smaller. It might be safe to turn
off this parameter if you have hardware (such as a battery-backed
disk controller) or file-system software that reduces the risk
of partial page writes to an acceptably low level (e.g., ZFS).
--
Bruce Momjian <[email protected]> http://momjian.us
EnterpriseDB http://enterprisedb.com
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: no universally correct setting for fsync
@ 2010-05-10 15:41 Robert Haas <[email protected]>
parent: Bruce Momjian <[email protected]>
0 siblings, 2 replies; 35+ messages in thread
From: Robert Haas @ 2010-05-10 15:41 UTC (permalink / raw)
To: Bruce Momjian <[email protected]>; +Cc: Michael Tharp <[email protected]>; Craig Ringer <[email protected]>; [email protected]
On Mon, May 10, 2010 at 11:12 AM, Bruce Momjian <[email protected]> wrote:
> Michael Tharp wrote:
>> On 05/08/2010 04:07 AM, Craig Ringer wrote:
>> > It's probably worth mentioning that people who want to turn off fsync to
>> > gain a performance boost should instead look at a RAID controller with a
>> > BBU so they can safely enable write-back caching, getting most of the
>> > benefits of fsync=off safely.
>>
>> Which options specifically should be set if a BBU is in use? Obviously
>> fsync should be on always, but can full_page_writes be disabled? Are
>> there other tweaks that can be done?
>>
>> It would be great to see some practical hints in the documentation while
>> the fsync part is getting changed.
>
> Uh, our docs have:
>
> Turning this parameter off speeds normal operation, but might
> lead to a corrupt database after an operating system crash or
> power failure. The risks are similar to turning off
> <varname>fsync</>, though smaller. It might be safe to turn
> off this parameter if you have hardware (such as a battery-backed
> disk controller) or file-system software that reduces the risk
> of partial page writes to an acceptably low level (e.g., ZFS).
"It might be safe" is a bit of a waffle. It would be nice if we could
provide some more clear guidance as to whether it is or is not, or how
someone could go about testing their hardware to find out.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: no universally correct setting for fsync
@ 2010-05-10 15:49 Bruce Momjian <[email protected]>
parent: Robert Haas <[email protected]>
1 sibling, 0 replies; 35+ messages in thread
From: Bruce Momjian @ 2010-05-10 15:49 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Michael Tharp <[email protected]>; Craig Ringer <[email protected]>; [email protected]
Robert Haas wrote:
> On Mon, May 10, 2010 at 11:12 AM, Bruce Momjian <[email protected]> wrote:
> > Michael Tharp wrote:
> >> On 05/08/2010 04:07 AM, Craig Ringer wrote:
> >> > It's probably worth mentioning that people who want to turn off fsync to
> >> > gain a performance boost should instead look at a RAID controller with a
> >> > BBU so they can safely enable write-back caching, getting most of the
> >> > benefits of fsync=off safely.
> >>
> >> Which options specifically should be set if a BBU is in use? Obviously
> >> fsync should be on always, but can full_page_writes be disabled? Are
> >> there other tweaks that can be done?
> >>
> >> It would be great to see some practical hints in the documentation while
> >> the fsync part is getting changed.
> >
> > Uh, our docs have:
> >
> > ? ? ? ?Turning this parameter off speeds normal operation, but might
> > ? ? ? ?lead to a corrupt database after an operating system crash or
> > ? ? ? ?power failure. The risks are similar to turning off
> > ? ? ? ?<varname>fsync</>, though smaller. ?It might be safe to turn
> > ? ? ? ?off this parameter if you have hardware (such as a battery-backed
> > ? ? ? ?disk controller) or file-system software that reduces the risk
> > ? ? ? ?of partial page writes to an acceptably low level (e.g., ZFS).
>
> "It might be safe" is a bit of a waffle. It would be nice if we could
> provide some more clear guidance as to whether it is or is not, or how
> someone could go about testing their hardware to find out.
Agreed. It is "safe" for us to be definitive here?
--
Bruce Momjian <[email protected]> http://momjian.us
EnterpriseDB http://enterprisedb.com
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: no universally correct setting for fsync
@ 2010-05-10 15:55 Kevin Grittner <[email protected]>
parent: Robert Haas <[email protected]>
1 sibling, 1 reply; 35+ messages in thread
From: Kevin Grittner @ 2010-05-10 15:55 UTC (permalink / raw)
To: Robert Haas <[email protected]>; Bruce Momjian <[email protected]>; +Cc: Michael Tharp <[email protected]>; [email protected]; Craig Ringer <[email protected]>
Robert Haas <[email protected]> wrote:
> "It might be safe" is a bit of a waffle. It would be nice if we
> could provide some more clear guidance as to whether it is or is
> not, or how someone could go about testing their hardware to find
> out.
I think that the issue is that you could have corruption if some,
but not all, disk sectors from a page were written from OS cache to
controller cache when a failure occurred. The window would be small
for a RAM-to-RAM write, but it wouldn't be entirely *safe* unless
there's some OS/driver environment where you could count on all the
sectors making it or none of them making it for every single page.
Does such an environment exist?
-Kevin
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: no universally correct setting for fsync
@ 2010-05-10 17:46 Greg Stark <[email protected]>
parent: Kevin Grittner <[email protected]>
0 siblings, 1 reply; 35+ messages in thread
From: Greg Stark @ 2010-05-10 17:46 UTC (permalink / raw)
To: Kevin Grittner <[email protected]>; +Cc: Robert Haas <[email protected]>; Bruce Momjian <[email protected]>; Michael Tharp <[email protected]>; [email protected]; Craig Ringer <[email protected]>
On Mon, May 10, 2010 at 4:55 PM, Kevin Grittner
<[email protected]> wrote:
> Robert Haas <[email protected]> wrote:
>
>> "It might be safe" is a bit of a waffle. It would be nice if we
>> could provide some more clear guidance as to whether it is or is
>> not, or how someone could go about testing their hardware to find
>> out.
>
> I think that the issue is that you could have corruption if some,
> but not all, disk sectors from a page were written from OS cache to
> controller cache when a failure occurred. The window would be small
> for a RAM-to-RAM write, but it wouldn't be entirely *safe* unless
> there's some OS/driver environment where you could count on all the
> sectors making it or none of them making it for every single page.
> Does such an environment exist?
The reason for the waffle is that the following sentence describes a
whole set of environments based the following description:
> > ? ? ? ?if you have hardware (such as a battery-backed
> > ? ? ? ?disk controller) or file-system software that reduces the risk
> > ? ? ? ?of partial page writes to an acceptably low level
Depending on which set of hardware and how low the risk is it might be safe.
I think with WAFL or ZFS it's entirely safe. There may be other
filesystems with similar guarantees. With a BBU the risk might be very
low -- but it might not, it would be hard to determine without a
detailed analysis of the entire stack from the buffer cache,
filesystem, lvm, hardware drivers, BBU design, etc.
--
greg
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: no universally correct setting for fsync
@ 2010-05-10 18:42 Joshua D. Drake <[email protected]>
parent: Greg Stark <[email protected]>
0 siblings, 1 reply; 35+ messages in thread
From: Joshua D. Drake @ 2010-05-10 18:42 UTC (permalink / raw)
To: Greg Stark <[email protected]>; +Cc: Kevin Grittner <[email protected]>; Robert Haas <[email protected]>; Bruce Momjian <[email protected]>; Michael Tharp <[email protected]>; [email protected]; Craig Ringer <[email protected]>
On Mon, 2010-05-10 at 18:46 +0100, Greg Stark wrote:
> On Mon, May 10, 2010 at 4:55 PM, Kevin Grittner
> <[email protected]> wrote:
> > Robert Haas <[email protected]> wrote:
> >
> >> "It might be safe" is a bit of a waffle. It would be nice if we
> >> could provide some more clear guidance as to whether it is or is
> >> not, or how someone could go about testing their hardware to find
> >> out.
> >
> > I think that the issue is that you could have corruption if some,
> > but not all, disk sectors from a page were written from OS cache to
> > controller cache when a failure occurred. The window would be small
> > for a RAM-to-RAM write, but it wouldn't be entirely *safe* unless
> > there's some OS/driver environment where you could count on all the
> > sectors making it or none of them making it for every single page.
> > Does such an environment exist?
>
> The reason for the waffle is that the following sentence describes a
> whole set of environments based the following description:
>
> > > ? ? ? ?if you have hardware (such as a battery-backed
> > > ? ? ? ?disk controller) or file-system software that reduces the risk
> > > ? ? ? ?of partial page writes to an acceptably low level
>
> Depending on which set of hardware and how low the risk is it might be safe.
>
> I think with WAFL or ZFS it's entirely safe. There may be other
> filesystems with similar guarantees. With a BBU the risk might be very
> low -- but it might not, it would be hard to determine without a
> detailed analysis of the entire stack from the buffer cache,
> filesystem, lvm, hardware drivers, BBU design, etc.
>
The answer to this is:
PostgreSQL.org recommends that this setting be left on at all times.
Turning it off, may lead to data corruption.
Anything else is circumstantial and based on knowledge and facts we
don't have about environmental factors.
Joshua D. Drake
> --
> greg
>
--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: no universally correct setting for fsync
@ 2010-05-10 19:00 Kevin Grittner <[email protected]>
parent: Joshua D. Drake <[email protected]>
0 siblings, 2 replies; 35+ messages in thread
From: Kevin Grittner @ 2010-05-10 19:00 UTC (permalink / raw)
To: [email protected]; Greg Stark <[email protected]>; +Cc: Robert Haas <[email protected]>; Bruce Momjian <[email protected]>; Michael Tharp <[email protected]>; [email protected]; Craig Ringer <[email protected]>
"Joshua D. Drake" <[email protected]> wrote:
> The answer to this is:
>
> PostgreSQL.org recommends that this setting be left on at all
> times. Turning it off, may lead to data corruption.
>
> Anything else is circumstantial and based on knowledge and facts
> we don't have about environmental factors.
Perhaps Josh's language for fsync could be modified to work here
(we're now talking about full_page_writes, for anyone who's lost
track):
| it is only advisable to turn off fsync if you can easily recreate
| your entire database from external data.
That covers bulk loads to an empty or just-backed-up database and
entirely redundant databases. Saying it should never be turned off
would tend to make one wonder why we have the setting at all.
-Kevin
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: no universally correct setting for fsync
@ 2010-05-10 19:57 Tom Lane <[email protected]>
parent: Kevin Grittner <[email protected]>
1 sibling, 0 replies; 35+ messages in thread
From: Tom Lane @ 2010-05-10 19:57 UTC (permalink / raw)
To: Kevin Grittner <[email protected]>; +Cc: [email protected]; Greg Stark <[email protected]>; Robert Haas <[email protected]>; Bruce Momjian <[email protected]>; Michael Tharp <[email protected]>; [email protected]; Craig Ringer <[email protected]>
"Kevin Grittner" <[email protected]> writes:
> Perhaps Josh's language for fsync could be modified to work here
> (we're now talking about full_page_writes, for anyone who's lost
> track):
> | it is only advisable to turn off fsync if you can easily recreate
> | your entire database from external data.
> That covers bulk loads to an empty or just-backed-up database and
> entirely redundant databases. Saying it should never be turned off
> would tend to make one wonder why we have the setting at all.
+1. Perhaps for both of them, we should specify that the intended
use-case is for improving performance during initial database load
and similar cases.
regards, tom lane
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: no universally correct setting for fsync
@ 2010-05-10 20:22 Cédric Villemain <[email protected]>
parent: Bernd Helmle <[email protected]>
1 sibling, 2 replies; 35+ messages in thread
From: Cédric Villemain @ 2010-05-10 20:22 UTC (permalink / raw)
To: Bernd Helmle <[email protected]>; +Cc: Kevin Grittner <[email protected]>; Andrew Dunstan <[email protected]>; [email protected]
2010/5/8 Bernd Helmle <[email protected]>:
>
>
> --On 7. Mai 2010 09:48:53 -0500 Kevin Grittner <[email protected]>
> wrote:
>
>> I think it goes beyond "tweaking" -- I think we should have a bald
>> statement like "don't turn this off unless you're OK with losing the
>> entire contents of the database cluster." A brief listing of some
>> cases where that is OK might be illustrative.
>>
>
> +1
>
>> I never meant to suggest any statement in that section is factually
>> wrong; it's just all too rosy, leading people to believe it's no big
>> deal to turn it off.
>
> I think one mistake in this paragraph is the passing mention of
> "performance". I've seen installations in the past with fsync=off only
> because the admin was pressured to get instantly "more speed" out of the
> database (think of "fast_mode=on"). In my opinion, phrases like "performance
> penalty" are misleading, if you need that setting in 99% of all use cases
> for reliable operation.
>
> I've recently even started to wonder if the performance gain with fsync=off
> is still that large on modern hardware. While testing large migration
> procedures to a new version some time ago (on an admitedly fast storage) i
> forgot here and then to turn it off, without a significant degradation in
> performance.
On a recent pg_restore -j 32, with perc 6i with BBU, RAID10 8 hd,
results were not so bas with fsync turn on. (XFS with nobarrier su and
sw)
-- deactivate fsync
time pg_restore -U postgres -d foodb -j 32 foo.psql
real 170m0.527s
user 43m12.914s
sys 1m56.499s
-- activate fsync
time pg_restore -U postgres -d foodb -j 32 foo.psql
real 177m0.121s
user 42m54.581s
sys 2m0.452s
>
>
> --
> Thanks
>
> Bernd
>
> --
> Sent via pgsql-hackers mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
--
Cédric Villemain
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: no universally correct setting for fsync
@ 2010-05-10 20:35 Josh Berkus <[email protected]>
parent: Cédric Villemain <[email protected]>
1 sibling, 2 replies; 35+ messages in thread
From: Josh Berkus @ 2010-05-10 20:35 UTC (permalink / raw)
To: Cédric Villemain <[email protected]>; +Cc: Bernd Helmle <[email protected]>; Kevin Grittner <[email protected]>; Andrew Dunstan <[email protected]>; [email protected]
All,
Updated docs based on tracking this discussion. fsync through full page
writes recorded below.
============
<varlistentry id="guc-fsync" xreflabel="fsync">
<indexterm>
<primary><varname>fsync</> configuration parameter</primary>
</indexterm>
<term><varname>fsync</varname> (<type>boolean</type>)</term>
<listitem>
<para>
If this parameter is on, the <productname>PostgreSQL</> server
will try to make sure that updates are physically written to
disk, by issuing <function>fsync()</> system calls or various
equivalent methods (see <xref linkend="guc-wal-sync-method">).
This ensures that the database cluster can recover to a
consistent state after an operating system or hardware crash.
</para>
<para>
While turning off <varname>fsync</varname> is often a performance
benefit, this can result in unrecoverable data corruption in the
event
of an unexpected system shutdown or crash. Thus it is only
advisable
to turn off <varname>fsync</varname> if you can easily recreate
your entire database from external data.
</para>
<para>
Examples of safe times to turn off <varname>fsync</varname> would be
when initially loading a new database from a backup file, on a
database which is
only used for processing statistics on an hourly basis and is then
deleted,
or on a reporting read-only clone of your database which gets
recreated very
night and is not used for failover. High quality hardware alone
is not a
sufficient justification for turning off <varname>fsync</varname>.
</para>
<para>
In many situations, turning off <xref
linkend="guc-synchronous-commit">
for noncritical transactions can provide much of the potential
performance benefit of turning off <varname>fsync</varname>, without
the attendant risks of data corruption.
</para>
<para>
<varname>fsync</varname> can only be set in the
<filename>postgresql.conf</>
file or on the server command line.
If you turn this parameter off, also consider turning off
<xref linkend="guc-full-page-writes">.
</para>
</listitem>
</varlistentry>
<varlistentry id="guc-synchronous-commit"
xreflabel="synchronous_commit">
<term><varname>synchronous_commit</varname>
(<type>boolean</type>)</term>
<indexterm>
<primary><varname>synchronous_commit</> configuration
parameter</primary>
</indexterm>
<listitem>
<para>
Specifies whether transaction commit will wait for WAL records
to be written to disk before the command returns a <quote>success</>
indication to the client. The default, and safe, setting is
<literal>on</>. When <literal>off</>, there can be a delay between
when success is reported to the client and when the transaction is
really guaranteed to be safe against a server crash. (The maximum
delay is three times <xref linkend="guc-wal-writer-delay">.) Unlike
<xref linkend="guc-fsync">, setting this parameter to
<literal>off</>
does not create any risk of database inconsistency: a crash might
result in some recent allegedly-committed transactions being
lost, but
the database state will be just the same as if those
transactions had
been aborted cleanly. So, turning
<varname>synchronous_commit</> off
can be a useful alternative when performance is more important than
exact certainty about the durability of a transaction. For more
discussion see <xref linkend="wal-async-commit">.
</para>
<para>
This parameter can be changed at any time; the behavior for any
one transaction is determined by the setting in effect when it
commits. It is therefore possible, and useful, to have some
transactions commit synchronously and others asynchronously.
For example, to make a single multi-statement transaction commit
asynchronously when the default is the opposite, issue <command>SET
LOCAL synchronous_commit TO OFF</> within the transaction.
</para>
</listitem>
</varlistentry>
<varlistentry id="guc-wal-sync-method" xreflabel="wal_sync_method">
<term><varname>wal_sync_method</varname> (<type>enum</type>)</term>
<indexterm>
<primary><varname>wal_sync_method</> configuration
parameter</primary>
</indexterm>
<listitem>
<para>
Method used for forcing WAL updates out to disk.
If <varname>fsync</varname> is off then this setting is irrelevant,
since WAL file updates will not be forced out at all.
Possible values are:
</para>
<itemizedlist>
<listitem>
<para>
<literal>open_datasync</> (write WAL files with
<function>open()</> option <symbol>O_DSYNC</>)
</para>
</listitem>
<listitem>
<para>
<literal>fdatasync</> (call <function>fdatasync()</> at each
commit)
</para>
</listitem>
<listitem>
<para>
<literal>fsync_writethrough</> (call <function>fsync()</> at
each commit, forcing write-through of any disk write cache)
</para>
</listitem>
<listitem>
<para>
<literal>fsync</> (call <function>fsync()</> at each commit)
</para>
</listitem>
<listitem>
<para>
<literal>open_sync</> (write WAL files with <function>open()</>
option <symbol>O_SYNC</>)
</para>
</listitem>
</itemizedlist>
<para>
Not all of these choices are available on all platforms.
The default is the first method in the above list that is supported
by the platform.
The <literal>open_</>* options also use <literal>O_DIRECT</> if
available.
The utility <filename>src/tools/fsync</> in the PostgreSQL
source tree
can do performance testing of various fsync methods.
This parameter can only be set in the <filename>postgresql.conf</>
file or on the server command line.
</para>
</listitem>
</varlistentry>
<varlistentry id="guc-full-page-writes" xreflabel="full_page_writes">
<indexterm>
<primary><varname>full_page_writes</> configuration
parameter</primary>
</indexterm>
<term><varname>full_page_writes</varname>
(<type>boolean</type>)</term>
<listitem>
<para>
When this parameter is on, the <productname>PostgreSQL</> server
writes the entire content of each disk page to WAL during the
first modification of that page after a checkpoint.
This is needed because
a page write that is in process during an operating system crash
might
be only partially completed, leading to an on-disk page
that contains a mix of old and new data. The row-level change data
normally stored in WAL will not be enough to completely restore
such a page during post-crash recovery. Storing the full page image
guarantees that the page can be correctly restored, but at the price
of increasing the amount of data that must be written to WAL.
(Because WAL replay always starts from a checkpoint, it is
sufficient
to do this during the first change of each page after a checkpoint.
Therefore, one way to reduce the cost of full-page writes is to
increase the checkpoint interval parameters.)
</para>
<para>
Turning this parameter off speeds normal operation, but
might lead to either unrecoverable data corruption, or silent
data corruption, after a system failure. The risks are similar
to turning off
<varname>fsync</varname>, though smaller, and it should be
turned off
only based on the same circumstances recommended for that parameter.
</para>
<para>
Turning off this parameter does not affect use of
WAL archiving for point-in-time recovery (PITR)
(see <xref linkend="continuous-archiving">).
</para>
<para>
This parameter can only be set in the <filename>postgresql.conf</>
file or on the server command line.
The default is <literal>on</>.
</para>
</listitem>
</varlistentry>
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: no universally correct setting for fsync
@ 2010-05-10 20:59 Kevin Grittner <[email protected]>
parent: Cédric Villemain <[email protected]>
1 sibling, 1 reply; 35+ messages in thread
From: Kevin Grittner @ 2010-05-10 20:59 UTC (permalink / raw)
To: =?ISO-8859-15?Q?C=E9dric=20Villemain?= <[email protected]>; Bernd Helmle <[email protected]>; +Cc: Andrew Dunstan <[email protected]>; [email protected]
Cédric Villemain<[email protected]> wrote:
> On a recent pg_restore -j 32, with perc 6i with BBU, RAID10 8 hd,
> results were not so bas with fsync turn on. (XFS with nobarrier su
> and sw)
> -- deactivate fsync
> time pg_restore -U postgres -d foodb -j 32 foo.psql
> real 170m0.527s
> user 43m12.914s
> sys 1m56.499s
> -- activate fsync
> time pg_restore -U postgres -d foodb -j 32 foo.psql
> real 177m0.121s
> user 42m54.581s
> sys 2m0.452s
Wow. In a situation where you save seven minutes (4%), it's hardly
worth turning off.
-Kevin
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: no universally correct setting for fsync
@ 2010-05-10 21:20 Josh Berkus <[email protected]>
parent: Kevin Grittner <[email protected]>
0 siblings, 1 reply; 35+ messages in thread
From: Josh Berkus @ 2010-05-10 21:20 UTC (permalink / raw)
To: Kevin Grittner <[email protected]>; +Cc: Cédric Villemain <[email protected]>; Bernd Helmle <[email protected]>; Andrew Dunstan <[email protected]>; [email protected]
> 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.
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: no universally correct setting for fsync
@ 2010-05-10 21:21 Ross J. Reedstrom <[email protected]>
parent: Josh Berkus <[email protected]>
1 sibling, 1 reply; 35+ messages in thread
From: Ross J. Reedstrom @ 2010-05-10 21:21 UTC (permalink / raw)
To: [email protected]
On Mon, May 10, 2010 at 01:35:32PM -0700, Josh Berkus wrote:
> deleted,
> or on a reporting read-only clone of your database which gets
> recreated very
> night and is not used for failover. High quality hardware alone
s/very/every/
or
s/very night/periodically/
Ross
--
Ross Reedstrom, Ph.D. [email protected]
Systems Engineer & Admin, Research Scientist phone: 713-348-6166
The Connexions Project http://cnx.org fax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: no universally correct setting for fsync
@ 2010-05-10 23:03 Greg Smith <[email protected]>
parent: Josh Berkus <[email protected]>
0 siblings, 1 reply; 35+ messages in thread
From: Greg Smith @ 2010-05-10 23:03 UTC (permalink / raw)
To: Josh Berkus <[email protected]>; +Cc: Kevin Grittner <[email protected]>; Cédric Villemain <[email protected]>; Bernd Helmle <[email protected]>; Andrew Dunstan <[email protected]>; [email protected]
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
[email protected] www.2ndQuadrant.us
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: no universally correct setting for fsync
@ 2010-05-11 00:23 Josh Berkus <[email protected]>
parent: Greg Smith <[email protected]>
0 siblings, 1 reply; 35+ messages in thread
From: Josh Berkus @ 2010-05-11 00:23 UTC (permalink / raw)
To: [email protected]
> 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.
I can't say I've tested this. Most of my head-to-heads on fsync were
before asych existed.
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: no universally correct setting for fsync
@ 2010-05-11 00:26 Josh Berkus <[email protected]>
parent: Ross J. Reedstrom <[email protected]>
0 siblings, 0 replies; 35+ messages in thread
From: Josh Berkus @ 2010-05-11 00:26 UTC (permalink / raw)
To: PostgreSQL-development <[email protected]>
On 5/10/10 2:21 PM, Ross J. Reedstrom wrote:
> On Mon, May 10, 2010 at 01:35:32PM -0700, Josh Berkus wrote:
>> deleted,
>> or on a reporting read-only clone of your database which gets
>> recreated very
>> night and is not used for failover. High quality hardware alone
>
> s/very/every/
> or
> s/very night/periodically/
"frequently" I think. Periodically could mean once a year.
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: no universally correct setting for fsync
@ 2010-05-11 01:04 Greg Smith <[email protected]>
parent: Josh Berkus <[email protected]>
0 siblings, 0 replies; 35+ messages in thread
From: Greg Smith @ 2010-05-11 01:04 UTC (permalink / raw)
To: Josh Berkus <[email protected]>; +Cc: [email protected]
Josh Berkus wrote:
>> 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.
>>
> I can't say I've tested this. Most of my head-to-heads on fsync were
> before asych existed.
>
Ditto for me. Curious about that, and I'd like to help work on
improving this chunk of the docs too. I don't know about you guys, but
I'm swamped until after PGCon though.
I have some hardware testing stuff planned anyway later this month, can
check exactly where this situation truly stands on a couple of common
pieces of hardware (next system has one of the LSI controllers Dell
rebrands too). I'll have the systems setup for something similar
anyway--can certainly see fsync differences with pgbench--easy to throw
this test into the mix too.
With that report, we should have the info needed to really nail this
down accurately. I can make my own proofreading pass of what Josh has
already been doing that also reflects the new data, and then we can
commit something that's good and well reviewed for 9.0 here.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
[email protected] www.2ndQuadrant.us
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: no universally correct setting for fsync
@ 2010-05-11 08:00 Yeb Havinga <[email protected]>
parent: Kevin Grittner <[email protected]>
1 sibling, 0 replies; 35+ messages in thread
From: Yeb Havinga @ 2010-05-11 08:00 UTC (permalink / raw)
To: Kevin Grittner <[email protected]>; +Cc: [email protected]; Greg Stark <[email protected]>; Robert Haas <[email protected]>; Bruce Momjian <[email protected]>; Michael Tharp <[email protected]>; [email protected]; Craig Ringer <[email protected]>
Kevin Grittner wrote:
> "Joshua D. Drake" <[email protected]> wrote:
>
>
>> The answer to this is:
>>
>> PostgreSQL.org recommends that this setting be left on at all
>> times. Turning it off, may lead to data corruption.
>>
>> Anything else is circumstantial and based on knowledge and facts
>> we don't have about environmental factors.
>>
>
> Perhaps Josh's language for fsync could be modified to work here
> (we're now talking about full_page_writes, for anyone who's lost
> track):
>
> | it is only advisable to turn off fsync if you can easily recreate
> | your entire database from external data.
>
> That covers bulk loads to an empty or just-backed-up database and
> entirely redundant databases. Saying it should never be turned off
> would tend to make one wonder why we have the setting at all.
>
Would the term "entirely redundant databases" include (synchronously)
replicated databases? (ps: I did indeed lose track about whether this is
about fsync or full_page_writes and did not get on the track again)
regards,
Yeb Havinga
^ permalink raw reply [nested|flat] 35+ messages in thread
* Re: no universally correct setting for fsync
@ 2010-05-31 15:52 Bruce Momjian <[email protected]>
parent: Josh Berkus <[email protected]>
1 sibling, 0 replies; 35+ messages in thread
From: Bruce Momjian @ 2010-05-31 15:52 UTC (permalink / raw)
To: Josh Berkus <[email protected]>; +Cc: Cédric Villemain <[email protected]>; Bernd Helmle <[email protected]>; Kevin Grittner <[email protected]>; Andrew Dunstan <[email protected]>; [email protected]
Josh Berkus wrote:
> All,
>
> Updated docs based on tracking this discussion. fsync through full page
> writes recorded below.
I have applied this doc update with the attached patch.
I added the change from "every night" to "frequently", and reworded it
slightly so it was clear it affects the entire cluster, not just a
single database.
--
Bruce Momjian <[email protected]> http://momjian.us
EnterpriseDB http://enterprisedb.com
Attachments:
[text/x-diff] /rtmp/diff (4.1K, 2-%2Frtmp%2Fdiff)
download | inline diff:
Index: doc/src/sgml/config.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/config.sgml,v
retrieving revision 1.279
diff -c -c -r1.279 config.sgml
*** doc/src/sgml/config.sgml 26 May 2010 23:49:18 -0000 1.279
--- doc/src/sgml/config.sgml 31 May 2010 15:44:36 -0000
***************
*** 1413,1446 ****
</para>
<para>
! However, using <varname>fsync</varname> results in a
! performance penalty: when a transaction is committed,
! <productname>PostgreSQL</productname> must wait for the
! operating system to flush the write-ahead log to disk. When
! <varname>fsync</varname> is disabled, the operating system is
! allowed to do its best in buffering, ordering, and delaying
! writes. This can result in significantly improved performance.
! However, if the system crashes, the results of the last few
! committed transactions might be completely lost, or worse,
! might appear partially committed, leaving the database in an
! inconsistent state. In the
! worst case, unrecoverable data corruption might occur.
! (Crashes of the database software itself are <emphasis>not</>
! a risk factor here. Only an operating-system-level crash
! creates a risk of corruption.)
</para>
<para>
! Due to the risks involved, there is no universally correct
! setting for <varname>fsync</varname>. Some administrators
! always disable <varname>fsync</varname>, while others only
! turn it off during initial bulk data loads, where there is a clear
! restart point if something goes wrong. Others
! always leave <varname>fsync</varname> enabled. The default is
! to enable <varname>fsync</varname>, for maximum reliability.
! If you trust your operating system, your hardware, and your
! utility company (or your battery backup), you can consider
! disabling <varname>fsync</varname>.
</para>
<para>
--- 1413,1435 ----
</para>
<para>
! While turning off <varname>fsync</varname> is often a performance
! benefit, this can result in unrecoverable data corruption in
! the event of an unexpected system shutdown or crash. Thus it
! is only advisable to turn off <varname>fsync</varname> if
! you can easily recreate your entire database from external
! data.
</para>
<para>
! Examples of safe circumstances for turning off
! <varname>fsync</varname> include the initial loading a new
! database cluster from a backup file, using a database cluster
! for processing statistics on an hourly basis which is then
! recreated, or for a reporting read-only database clone which
! gets recreated frequently and is not used for failover. High
! quality hardware alone is not a sufficient justification for
! turning off <varname>fsync</varname>.
</para>
<para>
***************
*** 1572,1583 ****
<para>
Turning this parameter off speeds normal operation, but
! might lead to a corrupt database after an operating system crash
! or power failure. The risks are similar to turning off
! <varname>fsync</>, though smaller. It might be safe to turn off
! this parameter if you have hardware (such as a battery-backed disk
! controller) or file-system software that reduces
! the risk of partial page writes to an acceptably low level (e.g., ZFS).
</para>
<para>
--- 1561,1570 ----
<para>
Turning this parameter off speeds normal operation, but
! might lead to either unrecoverable data corruption, or silent
! data corruption, after a system failure. The risks are similar to turning off
! <varname>fsync</varname>, though smaller, and it should be turned off
! only based on the same circumstances recommended for that parameter.
</para>
<para>
^ permalink raw reply [nested|flat] 35+ messages in thread
end of thread, other threads:[~2010-05-31 15:52 UTC | newest]
Thread overview: 35+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-05-07 13:47 no universally correct setting for fsync Kevin Grittner <[email protected]>
2010-05-07 14:00 ` Robert Haas <[email protected]>
2010-05-07 14:03 ` Magnus Hagander <[email protected]>
2010-05-07 14:24 ` Tom Lane <[email protected]>
2010-05-07 14:38 ` Andrew Dunstan <[email protected]>
2010-05-07 14:48 ` Kevin Grittner <[email protected]>
2010-05-07 17:56 ` Josh Berkus <[email protected]>
2010-05-08 00:02 ` Josh Berkus <[email protected]>
2010-05-08 00:13 ` Tom Lane <[email protected]>
2010-05-08 00:17 ` Josh Berkus <[email protected]>
2010-05-08 08:07 ` Craig Ringer <[email protected]>
2010-05-10 14:48 ` Michael Tharp <[email protected]>
2010-05-10 15:12 ` Bruce Momjian <[email protected]>
2010-05-10 15:41 ` Robert Haas <[email protected]>
2010-05-10 15:49 ` Bruce Momjian <[email protected]>
2010-05-10 15:55 ` Kevin Grittner <[email protected]>
2010-05-10 17:46 ` Greg Stark <[email protected]>
2010-05-10 18:42 ` Joshua D. Drake <[email protected]>
2010-05-10 19:00 ` Kevin Grittner <[email protected]>
2010-05-10 19:57 ` Tom Lane <[email protected]>
2010-05-11 08:00 ` Yeb Havinga <[email protected]>
2010-05-07 23:32 ` Bernd Helmle <[email protected]>
2010-05-07 23:49 ` Tom Lane <[email protected]>
2010-05-08 00:16 ` Bernd Helmle <[email protected]>
2010-05-10 20:22 ` Cédric Villemain <[email protected]>
2010-05-10 20:35 ` Josh Berkus <[email protected]>
2010-05-10 21:21 ` Ross J. Reedstrom <[email protected]>
2010-05-11 00:26 ` Josh Berkus <[email protected]>
2010-05-31 15:52 ` Bruce Momjian <[email protected]>
2010-05-10 20:59 ` Kevin Grittner <[email protected]>
2010-05-10 21:20 ` Josh Berkus <[email protected]>
2010-05-10 23:03 ` Greg Smith <[email protected]>
2010-05-11 00:23 ` Josh Berkus <[email protected]>
2010-05-11 01:04 ` Greg Smith <[email protected]>
2010-05-08 13:09 Re: [HACKERS] no universally correct setting for fsync Kevin Grittner <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox