Re: [GENERAL] Dangers of fsync = off
Thanks, Bill and Scott, for your responses. To summarize, turning fsync off on the master of a Slony-I cluster is probably safe if you observe the following: 1. When failover occurs, drop all databases on the failed machine and sync it with the new master before re-introducing it into the cluster. Note that the failed machine must not be returned to use until this is done. 2. Be aware that the above implies that you will lose any transactions which did not reach the standby machine prior to failure, violating the Durability component of ACID. This is true of any system which relies on asynchronous replication and automatic failover. - Joel ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Dangers of fsync = off
On 5/8/07, Joel Dice <[EMAIL PROTECTED]> wrote: On Tue, 8 May 2007, Andrew Sullivan wrote: > My real question is why you want to turn it off. If you're using a > battery-backed cache on your disk controller, then fsync ought to be > pretty close to free. Are you sure that turning it off will deliver > the benefit you think it will? You may very well be right. I tend to think in terms of software solutions, but a hardware solution may be most appropriate here. In any case, I'm not at all sure this will bring a significant peformance improvement. I just want to understand the implications before I start fiddling; if fsync=off is dangerous, it doesn't matter what the performance benefits may be. Well, fsync=off makes failures harder to cope with. Normally when your operating system crashes/power fails your master server should start up cleanly. If it doesn't -- you've got slave. Now, with fsync=off you should promote slave to master whenever you experience crash/power failure, just to be safe. Having battery backed unit may be cheaper than cost of failovers (time of DBA costs money, downtime also ;)). Do some testing, do some calculations. >> on Y. Thus, database corruption on X is irrelevant since our first step >> is to drop them. > > Not if the corruption introduces problems for replication, which is > indeed possible. That's exactly what I want to understand. How, exactly, is this possible? If the danger of fsync is that it may leave the on-disk state of the database in an inconsistent state after a crash, it would not seem to have any implications for activity occurring prior to the crash. In particular, a trigger-based replication system would seem to be immune. In other words, while there may be ways the master could cause corruption on the slave, I don't see how they could be related to the fsync setting. OK, let's assume you have machine mdb as a master database, and sdb as slave database. mdb has fsync=off and Slony-I is used as a replication system. You have a power failure/system crash/whatever. mdb goes down. Your sdb is consistent, but it's missing, let's say 15 seconds of last transactions which didn't manage to replicate. You don't do failover yet. Your mdb starts up, PostgreSQL replays its Write Ahead Log. Everything seems fine, mdb is up and running, and these 15 seconds of transactions are replicated to sdb. Oops. PostgreSQL seemd to be fine, but since fsync was off, the rows in Money_Transactions weren't flushed to disk (fsync was off), and PostgreSQL thought they should already be on disk (WAL was replayed since last known CHECKPOINT), you didn't actually replicated these transactions. If you are really unlucky you've replicated some old contents of database, and thus now, both your mdb and sdb contain erraneous data. Of course sdb is consistent in terms of "internal structure" but try explaining it to the poor soul who happened to be doing updates on Money_Transactions table. ;-) Of course likelihood of this happening isn't very big -- PostgreSQL really tries to safeguard your data (elephant never forgets ;)), but only as long as you give him a chance. ;) Regards, Dawid ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Dangers of fsync = off
On Wed, 2007-05-09 at 08:26 -0600, Scott Ribe wrote: > > I still wouldn't trust Slony with fsync off. Another scenario would be > > the Slony trigger writes a change to the Slony DB, the db crashes before > > it gets committed to disk. When the DB is started, no errors prevent > > startup, but that transaction is lost. > > I'm not sure, but I think the questioner was proposing a policy of "if it > crashes, we go to the standby, no attempt at recovery, ever", and I think > that would be safe. Just make sure that there is no way that the database would come back up after the crash. If it did, the slons could pick up and cause you trouble. If you disable all start up scripts, and operate under the assumption that crash=corruption=failover to Slony replica, you should be okay. You will lose whatever transactions were not replicated to the subscriber, but that's inherent to async replication. > And, personally, given my experience with pg, I think that's reasonable. > Because the day I see pg crash I'm going to assume I have a hardware problem > ;-) If you care about your data, leave fsync on. Period. If you can accept the potential for data loss, and you've proven that there is a worthwhile performance benefit from turning it off (which there may not be), and you gotten your boss/clients/stakeholders to sign off (preferably in writing) that data loss is acceptable if the db crashes, then go ahead and turn it off. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Dangers of fsync = off
> I still wouldn't trust Slony with fsync off. Another scenario would be > the Slony trigger writes a change to the Slony DB, the db crashes before > it gets committed to disk. When the DB is started, no errors prevent > startup, but that transaction is lost. I'm not sure, but I think the questioner was proposing a policy of "if it crashes, we go to the standby, no attempt at recovery, ever", and I think that would be safe. And, personally, given my experience with pg, I think that's reasonable. Because the day I see pg crash I'm going to assume I have a hardware problem ;-) -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Dangers of fsync = off
In response to Csaba Nagy <[EMAIL PROTECTED]>: > > [snip] Take the example of a query "UPDATE tablename SET x = x + 1". > > When this query is erroneously issued twice, data corruption will occur. > > Huh ? I thought slony is replicating data, not queries... what on the > master is "UPDATE tablename SET x = x + 1" will translate to "UPDATE > tablename SET x = new_value" on the slave, where new_value equals that x > + 1. That's why slony is working well even if you do "UPDATE tablename > SET x = now()". True. My mistake. I still wouldn't trust Slony with fsync off. Another scenario would be the Slony trigger writes a change to the Slony DB, the db crashes before it gets committed to disk. When the DB is started, no errors prevent startup, but that transaction is lost. I mean, you have to weight all these possibilities against your tolerance for data loss. If you're a bank, none of this is acceptable. If you're MySpace, who f*cking cares if you lose data (I saw an article where the CIO of MySpace admitted that was their policy -- must be nice to have a job where nobody cares if you do it wrong!) -- Bill Moran http://www.potentialtech.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Dangers of fsync = off
> [snip] Take the example of a query "UPDATE tablename SET x = x + 1". > When this query is erroneously issued twice, data corruption will occur. Huh ? I thought slony is replicating data, not queries... what on the master is "UPDATE tablename SET x = x + 1" will translate to "UPDATE tablename SET x = new_value" on the slave, where new_value equals that x + 1. That's why slony is working well even if you do "UPDATE tablename SET x = now()". Cheers, Csaba. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Dangers of fsync = off
In response to Joel Dice <[EMAIL PROTECTED]>: > Thanks for your response, Andrew. > > On Tue, 8 May 2007, Andrew Sullivan wrote: > > > On Fri, May 04, 2007 at 08:54:10AM -0600, Joel Dice wrote: > >> > >> My next question is this: what are the dangers of turning fsync off in the > >> context of a high-availablilty cluster using asynchronous replication? > > > > My real question is why you want to turn it off. If you're using a > > battery-backed cache on your disk controller, then fsync ought to be > > pretty close to free. Are you sure that turning it off will deliver > > the benefit you think it will? > > You may very well be right. I tend to think in terms of software > solutions, but a hardware solution may be most appropriate here. In any > case, I'm not at all sure this will bring a significant peformance > improvement. I just want to understand the implications before I start > fiddling; if fsync=off is dangerous, it doesn't matter what the > performance benefits may be. > > >> on Y. Thus, database corruption on X is irrelevant since our first step > >> is to drop them. > > > > Not if the corruption introduces problems for replication, which is > > indeed possible. > > That's exactly what I want to understand. How, exactly, is this possible? > If the danger of fsync is that it may leave the on-disk state of the > database in an inconsistent state after a crash, it would not seem to have > any implications for activity occurring prior to the crash. In > particular, a trigger-based replication system would seem to be immune. If you mean Slony, no. It's not immune. Slony maintains its state in tables in the database. If fsync is off, there's no guarantee that Slony's state information is sane, which means replication is not guaranteed to be or do anything. > In other words, while there may be ways the master could cause corruption > on the slave, I don't see how they could be related to the fsync setting. Specifically, I can imagine a system crashing, then _seeming_ to restart properly, but Slony starts re-replicating transactions that have already been replicated once because the ACKs were never written to disk on the master. Take the example of a query "UPDATE tablename SET x = x + 1". When this query is erroneously issued twice, data corruption will occur. Other scenarios may be possible. -- Bill Moran http://www.potentialtech.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Dangers of fsync = off
Thanks for your response, Andrew. On Tue, 8 May 2007, Andrew Sullivan wrote: On Fri, May 04, 2007 at 08:54:10AM -0600, Joel Dice wrote: My next question is this: what are the dangers of turning fsync off in the context of a high-availablilty cluster using asynchronous replication? My real question is why you want to turn it off. If you're using a battery-backed cache on your disk controller, then fsync ought to be pretty close to free. Are you sure that turning it off will deliver the benefit you think it will? You may very well be right. I tend to think in terms of software solutions, but a hardware solution may be most appropriate here. In any case, I'm not at all sure this will bring a significant peformance improvement. I just want to understand the implications before I start fiddling; if fsync=off is dangerous, it doesn't matter what the performance benefits may be. on Y. Thus, database corruption on X is irrelevant since our first step is to drop them. Not if the corruption introduces problems for replication, which is indeed possible. That's exactly what I want to understand. How, exactly, is this possible? If the danger of fsync is that it may leave the on-disk state of the database in an inconsistent state after a crash, it would not seem to have any implications for activity occurring prior to the crash. In particular, a trigger-based replication system would seem to be immune. In other words, while there may be ways the master could cause corruption on the slave, I don't see how they could be related to the fsync setting. - Joel ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Dangers of fsync = off
On Fri, May 04, 2007 at 08:54:10AM -0600, Joel Dice wrote: > > My next question is this: what are the dangers of turning fsync off in the > context of a high-availablilty cluster using asynchronous replication? My real question is why you want to turn it off. If you're using a battery-backed cache on your disk controller, then fsync ought to be pretty close to free. Are you sure that turning it off will deliver the benefit you think it will? > on Y. Thus, database corruption on X is irrelevant since our first step > is to drop them. Not if the corruption introduces problems for replication, which is indeed possible. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Dangers of fsync = off
Thanks for the explanation, Tom. I understand the problem now. My next question is this: what are the dangers of turning fsync off in the context of a high-availablilty cluster using asynchronous replication? In particular, we are using Slony-I and linux-ha to provide a two-node, master-slave cluster. As you may know, Slony-I uses triggers to provide asynchronous replication. If the master (X) fails, the slave (Y) becomes active. At this point, the administrator manually performs a recovery by reintroducing X so that Y is the master and X is the slave. This task involves dropping any databases on X and having it sync with the versions on Y. Thus, database corruption on X is irrelevant since our first step is to drop them. It would seem that our only exposure is that both machines fail before the administrator is able to perform the recovery. Even that could be solved by leaving fsync turned on for the slave, so that when failover occurs and the slave becomes active, we only turn fsync off once we've safely reintroduced the other machine (which, in turn will have fsync turned on). There was a discussion about this here: http://gborg.postgresql.org/pipermail/slony1-general/2005-March/001760.html However, that discussion seems to assume that the administrator needs to salvage the databases on the failed machine, which is not necessary in our case. In short, is there any danger (besides losing a few transactions) of turning fsync off on the master of a cluster using asynchronous replication, assuming we don't need to recover the data from the master when it fails? Thanks. - Joel ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Dangers of fsync = off
Joel Dice <[EMAIL PROTECTED]> writes: > It's clear from the documentation for the fsync configuration option that > turning it off may lead to unrecoverable data corruption. I'd like to > learn more about why this is possible and how likely it really is. As you note, WAL is not particularly vulnerable --- the worst likely consequence is not being able to read the last few WAL entries that were made. The real problem with fsync off is that there is essentially no guarantee about the relative write order of WAL and data files. In particular, some data-file updates might hit disk before the corresponding WAL entries. If other data-file updates part of the same transaction did *not* reach disk before a crash, then replay of WAL might not cause those updates to happen (because the relevant WAL records are unreadable), leaving you with inconsistent data. Another scenario is that a checkpoint is shown as completed by WAL but not all of the before-the-checkpoint data-file updates actually reached disk. WAL replay will start from the checkpoint and therefore not fix the missing updates. Either way you have inconsistencies in on-disk data, such as missing tuples, multiple live versions of the same tuple, index contents not consistent with heap, or outright-corrupt index structure. The extent to which these things are visible to applications is hard to predict, but it's frequently ugly :-(. Index problems can always be fixed with REINDEX, but there's no fix for inconsistent heap contents. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
