Re: [GENERAL] synchronous replication + fsync=off?
Tomas Vondra wrote: On 17 Listopad 2011, 17:07, Jaime Casanova wrote: On Thu, Nov 17, 2011 at 7:52 AM, Schubert, Joerg jschub...@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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] synchronous replication + fsync=off?
On Tue, Nov 22, 2011 at 12:16 PM, Bruce Momjian br...@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 jschub...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] synchronous replication + fsync=off?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] synchronous replication + fsync=off?
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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] synchronous replication + fsync=off?
What if power supply goes ? What if someone trips on the cable, and both servers go ? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] synchronous replication + fsync=off?
On Thu, Nov 17, 2011 at 7:52 AM, Schubert, Joerg jschub...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] synchronous replication + fsync=off?
On Thu, Nov 17, 2011 at 9:07 AM, Jaime Casanova ja...@2ndquadrant.com wrote: On Thu, Nov 17, 2011 at 7:52 AM, Schubert, Joerg jschub...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] synchronous replication + fsync=off?
On 17 Listopad 2011, 17:07, Jaime Casanova wrote: On Thu, Nov 17, 2011 at 7:52 AM, Schubert, Joerg jschub...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
