Re: no universally correct setting for fsync
Re: no universally correct setting for fsync
От:
"Joshua D. Drake" <jd@commandprompt.com>
Дата:
On Mon, 2010-05-10 at 18:46 +0100, Greg Stark wrote: > On Mon, May 10, 2010 at 4:55 PM, Kevin Grittner > wrote: > > Robert Haas 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
Re: no universally correct setting for fsync
От:
Bruce Momjian <bruce@momjian.us>
Дата:
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 http://momjian.us
EnterpriseDB http://enterprisedb.com
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 ****
! However, using fsync results in a
! performance penalty: when a transaction is committed,
! PostgreSQL must wait for the
! operating system to flush the write-ahead log to disk. 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 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 not</>
! a risk factor here. Only an operating-system-level crash
! creates a risk of corruption.)
! Due to the risks involved, there is no universally correct
! setting for fsync. Some administrators
! always disable fsync, 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 fsync enabled. The default is
! to enable fsync, for maximum reliability.
! If you trust your operating system, your hardware, and your
! utility company (or your battery backup), you can consider
! disabling fsync.
--- 1413,1435 ----
! While turning off fsync 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 fsync if
! you can easily recreate your entire database from external
! data.
! Examples of safe circumstances for turning off
! fsync 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 fsync.
***************
*** 1572,1583 ****
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
! 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).
--- 1561,1570 ----
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
! fsync, though smaller, and it should be turned off
! only based on the same circumstances recommended for that parameter.
Re: no universally correct setting for fsync
От:
Cédric Villemain <cedric.villemain.debian@gmail.com>
Дата:
