Обсуждение: synchronous replication + fsync=off?
Hello,
I have two servers with battery backed power supply (USV). So it is unlikely, that both will crash at the same time.
Will synchronous replication work with fsync=off?
That means we will commit to system cache, but not to disk. Data will not survive a system crash but the second system should still be consistent.
Or: will it work with
master: fsync=off
and
slave(s): fsync=on?
In this case master is free for read-queries and slave has time to do all the heavy writing. Data on master will not survive a crash but can be restored from slave.
Thanks,
Joerg
I have two servers with battery backed power supply (USV). So it is unlikely, that both will crash at the same time.
Will synchronous replication work with fsync=off?
That means we will commit to system cache, but not to disk. Data will not survive a system crash but the second system should still be consistent.
Or: will it work with
master: fsync=off
and
slave(s): fsync=on?
In this case master is free for read-queries and slave has time to do all the heavy writing. Data on master will not survive a crash but can be restored from slave.
Thanks,
Joerg
What if power supply goes ? What if someone trips on the cable, and both servers go ?
On Thu, Nov 17, 2011 at 7:52 AM, Schubert, Joerg <jschubert@cebacus.de> wrote: > Hello, > > I have two servers with battery backed power supply (USV). So it is > unlikely, that both will crash at the same time. > > Will synchronous replication work with fsync=off? > That means we will commit to system cache, but not to disk. Data will not > survive a system crash but the second system should still be consistent. > you should never use fsync=off (in production at least) the appropiate parameter to use is synchronous_commit which is the one that controls synchronous replication: off = no local nor remote synchronous commit local = local synchronous commit but no remote on = both, local and remote, synchronous commit synchronous commit = flushed to disk once all that said, i guess you can use fsync on any combination (off on master and on on standby, for your case) but i haven't tried. anyway that will guarantee you will lose your master instalation on OS crash and i think to remember that even if the OS doesn't crash there is a risk (altough i can't find the mail saying that) -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación
On Thu, Nov 17, 2011 at 9:07 AM, Jaime Casanova <jaime@2ndquadrant.com> wrote: > On Thu, Nov 17, 2011 at 7:52 AM, Schubert, Joerg <jschubert@cebacus.de> wrote: >> Hello, >> >> I have two servers with battery backed power supply (USV). So it is >> unlikely, that both will crash at the same time. >> >> Will synchronous replication work with fsync=off? >> That means we will commit to system cache, but not to disk. Data will not >> survive a system crash but the second system should still be consistent. >> > > you should never use fsync=off (in production at least) That's not entirely true. for instance, session servers are fine with fsync=off because the data in them is only alive while the session is up. Corrupted database means reinit db, restore schema, put back in loop. But yeh for data that means anything, fsync off is a bad idea.
On 17 Listopad 2011, 17:07, Jaime Casanova wrote: > On Thu, Nov 17, 2011 at 7:52 AM, Schubert, Joerg <jschubert@cebacus.de> > wrote: >> Hello, >> >> I have two servers with battery backed power supply (USV). So it is >> unlikely, that both will crash at the same time. >> >> Will synchronous replication work with fsync=off? >> That means we will commit to system cache, but not to disk. Data will >> not >> survive a system crash but the second system should still be consistent. >> > > you should never use fsync=off (in production at least) > > the appropiate parameter to use is synchronous_commit which is the one > that controls synchronous replication: > off = no local nor remote synchronous commit > local = local synchronous commit but no remote > on = both, local and remote, synchronous commit > > synchronous commit = flushed to disk While I don't recommend it, fsync=off definitely is an option, especially with sync replication. The synchronous_commit is not a 1:1 replacement. Imagine for example a master with lot of I/O, and a sync standby. By setting fsync=off on the master and fsync=on on the slave the master does not need to wait for the fsync (so the I/O is not that stressed and can handle more requests from clients), but the slave actually does fsync. So you don't force local fsync, but you're waiting for fsync from the standby. But standby doesn't need to handle all the I/O the primary has. You can't do this with synchronous_commit - that basically forces you to do local fsync on commit, or not to wait for the commit at all. Tomas Disclaimer: I haven't actually tried this, so maybe I missed something.
Tomas Vondra wrote: > On 17 Listopad 2011, 17:07, Jaime Casanova wrote: > > On Thu, Nov 17, 2011 at 7:52 AM, Schubert, Joerg <jschubert@cebacus.de> > > wrote: > >> Hello, > >> > >> I have two servers with battery backed power supply (USV). So it is > >> unlikely, that both will crash at the same time. > >> > >> Will synchronous replication work with fsync=off? > >> That means we will commit to system cache, but not to disk. Data will > >> not > >> survive a system crash but the second system should still be consistent. > >> > > > > you should never use fsync=off (in production at least) > > > > the appropiate parameter to use is synchronous_commit which is the one > > that controls synchronous replication: > > off = no local nor remote synchronous commit > > local = local synchronous commit but no remote > > on = both, local and remote, synchronous commit > > > > synchronous commit = flushed to disk > > While I don't recommend it, fsync=off definitely is an option, especially > with sync replication. The synchronous_commit is not a 1:1 replacement. > > Imagine for example a master with lot of I/O, and a sync standby. By > setting fsync=off on the master and fsync=on on the slave the master does > not need to wait for the fsync (so the I/O is not that stressed and can > handle more requests from clients), but the slave actually does fsync. > > So you don't force local fsync, but you're waiting for fsync from the > standby. But standby doesn't need to handle all the I/O the primary has. > > You can't do this with synchronous_commit - that basically forces you to > do local fsync on commit, or not to wait for the commit at all. > > Tomas > > Disclaimer: I haven't actually tried this, so maybe I missed something. I think you did. synchronous_commit really means fsync so that the system is alway consistent --- there is no waiting for the fsync to happen on the master (unless I am totally missing something). With fsync off, you can get into cases where the heap/index files are pushed to disk before the wal gets written to disk, causing the system to be inconsistent in case of a crash replay. I think the only use of fsync off is for performance testing so see how expensive fynsc is. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On Tue, Nov 22, 2011 at 12:16 PM, Bruce Momjian <bruce@momjian.us> wrote: > Tomas Vondra wrote: >> On 17 Listopad 2011, 17:07, Jaime Casanova wrote: >> > On Thu, Nov 17, 2011 at 7:52 AM, Schubert, Joerg <jschubert@cebacus.de> >> > wrote: >> >> Hello, >> >> >> >> I have two servers with battery backed power supply (USV). So it is >> >> unlikely, that both will crash at the same time. >> >> >> >> Will synchronous replication work with fsync=off? >> >> That means we will commit to system cache, but not to disk. Data will >> >> not >> >> survive a system crash but the second system should still be consistent. >> >> >> > >> > you should never use fsync=off (in production at least) >> > >> > the appropiate parameter to use is synchronous_commit which is the one >> > that controls synchronous replication: >> > off = no local nor remote synchronous commit >> > local = local synchronous commit but no remote >> > on = both, local and remote, synchronous commit >> > >> > synchronous commit = flushed to disk >> >> While I don't recommend it, fsync=off definitely is an option, especially >> with sync replication. The synchronous_commit is not a 1:1 replacement. >> >> Imagine for example a master with lot of I/O, and a sync standby. By >> setting fsync=off on the master and fsync=on on the slave the master does >> not need to wait for the fsync (so the I/O is not that stressed and can >> handle more requests from clients), but the slave actually does fsync. >> >> So you don't force local fsync, but you're waiting for fsync from the >> standby. But standby doesn't need to handle all the I/O the primary has. >> >> You can't do this with synchronous_commit - that basically forces you to >> do local fsync on commit, or not to wait for the commit at all. >> >> Tomas >> >> Disclaimer: I haven't actually tried this, so maybe I missed something. > > I think you did. synchronous_commit really means fsync so that the > system is alway consistent --- there is no waiting for the fsync to > happen on the master (unless I am totally missing something). +1, synchronous_commit has (pretty much) nothing to do with synchronous replication; it's all about controlling the relationship between local commits and fsync. > With > fsync off, you can get into cases where the heap/index files are pushed > to disk before the wal gets written to disk, causing the system to be > inconsistent in case of a crash replay. > I think it's worth saying that this doesn't "guarantee you will lose your master" as someone claimed upthread; more correctly it just introduces the possibility that your database will be corrupt upon server or OS crash (which is something most people should avoid). > I think the only use of fsync off is for performance testing so see how > expensive fynsc is. > Never speak in absolutes! ;-) It's not unheard of to run with fsync = off when you have asynchronous replicated failover. Given you've already decided that you're ok with data loss, the extra amount that you lose with the fsync off can be trivial compared to the performance boost you get, especially if system crashes in your environment are rare (which hopefully they should be). Robert Treat conjecture: xzilla.net consulting: omniti.com
On 22 Listopad 2011, 18:16, Bruce Momjian wrote: > Tomas Vondra wrote: >> While I don't recommend it, fsync=off definitely is an option, >> especially >> with sync replication. The synchronous_commit is not a 1:1 replacement. >> >> Imagine for example a master with lot of I/O, and a sync standby. By >> setting fsync=off on the master and fsync=on on the slave the master >> does >> not need to wait for the fsync (so the I/O is not that stressed and can >> handle more requests from clients), but the slave actually does fsync. >> >> So you don't force local fsync, but you're waiting for fsync from the >> standby. But standby doesn't need to handle all the I/O the primary has. >> >> You can't do this with synchronous_commit - that basically forces you to >> do local fsync on commit, or not to wait for the commit at all. >> >> Tomas >> >> Disclaimer: I haven't actually tried this, so maybe I missed something. > > I think you did. synchronous_commit really means fsync so that the > system is alway consistent --- there is no waiting for the fsync to > happen on the master (unless I am totally missing something). With > fsync off, you can get into cases where the heap/index files are pushed > to disk before the wal gets written to disk, causing the system to be > inconsistent in case of a crash replay. Well, but this inconsistency can happen only on the master, right? The slave receives on the WAL records in order, and applies them just fine. And it's fsync=on so it is not vulnerable to this kind of data corruption. When the master crashes, the recovery may fail - that's expected. The master would have to be rebuilt from scratch in this scenario. Yes, I know that synchronous_commit actually guarantees data consistency. The point of the suggested scenario was that (a) the master does not wait for the I/O (assuming that it's busy and the latency would be significant) (b) the master waits for the slave (sync replication) so that you know that in case of crash of the master the slave actually contains all the data. That's not what synchronous_commit does - you may loose data if you use it. > I think the only use of fsync off is for performance testing so see how > expensive fynsc is. There's at least one more quite commonly used scenario - restoring a database from backup. It often speeds the process significantly and when something fails, you can simply start again. Tomas
Tomas Vondra wrote: > On 22 Listopad 2011, 18:16, Bruce Momjian wrote: > > Tomas Vondra wrote: > >> While I don't recommend it, fsync=off definitely is an option, > >> especially > >> with sync replication. The synchronous_commit is not a 1:1 replacement. > >> > >> Imagine for example a master with lot of I/O, and a sync standby. By > >> setting fsync=off on the master and fsync=on on the slave the master > >> does > >> not need to wait for the fsync (so the I/O is not that stressed and can > >> handle more requests from clients), but the slave actually does fsync. > >> > >> So you don't force local fsync, but you're waiting for fsync from the > >> standby. But standby doesn't need to handle all the I/O the primary has. > >> > >> You can't do this with synchronous_commit - that basically forces you to > >> do local fsync on commit, or not to wait for the commit at all. > >> > >> Tomas > >> > >> Disclaimer: I haven't actually tried this, so maybe I missed something. > > > > I think you did. synchronous_commit really means fsync so that the > > system is alway consistent --- there is no waiting for the fsync to > > happen on the master (unless I am totally missing something). With > > fsync off, you can get into cases where the heap/index files are pushed > > to disk before the wal gets written to disk, causing the system to be > > inconsistent in case of a crash replay. > > Well, but this inconsistency can happen only on the master, right? The > slave receives on the WAL records in order, and applies them just fine. > And it's fsync=on so it is not vulnerable to this kind of data corruption. True. > When the master crashes, the recovery may fail - that's expected. The > master would have to be rebuilt from scratch in this scenario. Ah, the recovery perhaps doesn't generate an error. It might come up just fine, but be inconsistent. > Yes, I know that synchronous_commit actually guarantees data consistency. > The point of the suggested scenario was that > > (a) the master does not wait for the I/O (assuming that it's busy and the > latency would be significant) > > (b) the master waits for the slave (sync replication) > > so that you know that in case of crash of the master the slave actually > contains all the data. That's not what synchronous_commit does - you may > loose data if you use it. Yes, that would work. > > I think the only use of fsync off is for performance testing so see how > > expensive fynsc is. > > There's at least one more quite commonly used scenario - restoring a > database from backup. It often speeds the process significantly and when > something fails, you can simply start again. True. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +