Re: [HACKERS] Synchronization levels in SR
On Wed, Sep 8, 2010 at 10:22 AM, Simon Riggs wrote: > On Wed, 2010-09-08 at 09:50 -0400, Robert Haas wrote: > >> So that means we have to make sure that none of the effects of a >> transaction can be seen until WAL is fsync'd on the master AND the >> slave has acked. > > Yes, that's right. And I like your example; one for the docs. > > There is a slight complexity there: An application might connect to the > standby and see the changes made by the transaction, even though the > master has not yet been notified, but will be in a moment. I don't see > that as an issue though, but worth mentioning cos its just the > "Byzantine Generals" problem. I think that's OK too, because there's no way we can guarantee that the transaction becomes visible exactly simultaneously on both nodes. What we do need to guarantee is that it is known committed on both nodes before it becomes visible on either, so that even if there is a crash or failover it can't uncommit itself. So the order of events must be: - fsync WAL on master - send WAL to slave - wait for ack from slave - allow transaction's effects to become visible on master If the slave is only guaranteeing *receipt* of the WAL rather than fsync or replay of the WAL, then there is still a possibility of a disappearing transaction if the master and standby fail simultaneously AND a failover then occurs. So don't pick that mode if a disappearing transaction will result in someone dying or your $20B company going bankrupt or ... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Wed, Sep 08, 2010 at 03:22:46PM +0100, Simon Riggs wrote: > On Wed, 2010-09-08 at 09:50 -0400, Robert Haas wrote: > > > So that means we have to make sure that none of the effects of a > > transaction can be seen until WAL is fsync'd on the master AND the > > slave has acked. > > Yes, that's right. And I like your example; one for the docs. > > There is a slight complexity there: An application might connect to > the standby and see the changes made by the transaction, even though > the master has not yet been notified, but will be in a moment. I > don't see that as an issue though, but worth mentioning cos its just > the "Byzantine Generals" problem. For completeness, a reference to the aforementioned Byzantine Generals: http://en.wikipedia.org/wiki/Byzantine_fault_tolerance Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Wed, 2010-09-08 at 09:50 -0400, Robert Haas wrote: > So that means we have to make sure that none of the effects of a > transaction can be seen until WAL is fsync'd on the master AND the > slave has acked. Yes, that's right. And I like your example; one for the docs. There is a slight complexity there: An application might connect to the standby and see the changes made by the transaction, even though the master has not yet been notified, but will be in a moment. I don't see that as an issue though, but worth mentioning cos its just the "Byzantine Generals" problem. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Wed, Sep 8, 2010 at 9:32 AM, Fujii Masao wrote: > On Wed, Sep 8, 2010 at 10:07 PM, Robert Haas wrote: >> On Wed, Sep 8, 2010 at 8:30 AM, Fujii Masao wrote: And in any event, there is ALWAYS a window of time during which the client doesn't know the transaction has committed but other transactions can potentially see its effects. >>> >>> Yep. The problem here is that synchronous replication is likely to >>> make the window very big. >> >> So what? If the correctness of your application depends on the >> *amount of time* this window lasts, it's already broken. It seems >> like you're arguing that we should artificially increase lock >> contention to guard against possible race conditions in user >> applications. That doesn't make any sense to me, so one of us is >> confused. > > Yep ;) On second thought, the problem here is that the effects of > the transaction marked as committed but still waiting for replication > can disappear after failover. Ah! I think that's right. So the scenario we're trying to guard against something like this. A customer makes a withdrawal of money from an ATM; their bank balance is debited. The transaction tries to commit. After the transaction becomes visible to other backends but before WAL is reaches the standby, another transaction begins and reads the customer's balance. Naturally, they get the new, lower balance. Crash, master dead. Failover. If another transcation begins and reads the customer's balance again, it's back to the old value. So we have a phantom transaction: it appeared as committed and then vanished again. So that means we have to make sure that none of the effects of a transaction can be seen until WAL is fsync'd on the master AND the slave has acked. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Wed, Sep 8, 2010 at 10:07 PM, Robert Haas wrote: > On Wed, Sep 8, 2010 at 8:30 AM, Fujii Masao wrote: >>> And in any event, there is ALWAYS a window of >>> time during which the client doesn't know the transaction has >>> committed but other transactions can potentially see its effects. >> >> Yep. The problem here is that synchronous replication is likely to >> make the window very big. > > So what? If the correctness of your application depends on the > *amount of time* this window lasts, it's already broken. It seems > like you're arguing that we should artificially increase lock > contention to guard against possible race conditions in user > applications. That doesn't make any sense to me, so one of us is > confused. Yep ;) On second thought, the problem here is that the effects of the transaction marked as committed but still waiting for replication can disappear after failover. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Wed, Sep 8, 2010 at 8:30 AM, Fujii Masao wrote: >> And in any event, there is ALWAYS a window of >> time during which the client doesn't know the transaction has >> committed but other transactions can potentially see its effects. > > Yep. The problem here is that synchronous replication is likely to > make the window very big. So what? If the correctness of your application depends on the *amount of time* this window lasts, it's already broken. It seems like you're arguing that we should artificially increase lock contention to guard against possible race conditions in user applications. That doesn't make any sense to me, so one of us is confused. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Wed, 2010-09-08 at 12:04 +0200, Boszormenyi Zoltan wrote: > >> > >> I know why you want that, but I don't think its right. > >> > > > > Agreed. That's why I put the wait before ProcArrayEndTransaction() > > is called. > > > > Then there is no use to implement individual sync/async > replicated transactions, period. An async replicated transaction > that waits for a sync replicated transaction because of locks > will become implicitely sync. It just waits for another transactions' > sync ack. You aren't making any sense. You have made a general observation and deduced something specific about replication from it. Most transactions are not blocked by locks, especially in well designed applications, so the argument is not relevant to replication. If *any* two transactions wait upon each other then t2 will always wait until t1 has completed. If t1 is slow then any tuning you do on t2 will likely be wasted. If you are concerned about performance you should first remove the dependency between t1 and t2. The above observation isn't sufficient to conclude that "tuning of t2 should not happen via the tuning feature Simon has suggested". It's not sufficient to conclude much, if anything. As it turns out, in the scenario you outline t2 *would* return faster because you had marked it as "async". But it would wait behind t1, as you say. So the performance gain will be clear and measurable. Even so, it would be best to tune the problem (lock contention) not moan that the tool you're choosing to use using (tuning replication) is at fault for being inappropriate to the problem. Mixing sync and async transactions is useful and it's a simple matter to come up with real examples where it would benefit, as well as easily testable workloads using pgbench. For example, customer table updates (sync) alongside chat messages (async). -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On 09/08/2010 12:04 PM, Boszormenyi Zoltan wrote: Then there is no use to implement individual sync/async replicated transactions, period. I disagree. Different transactions have different priorities for latency vs. failure-resistance. An async replicated transaction that waits for a sync replicated transaction because of locks will become implicitely sync. Sure. But how often do your transactions wait for another one because of locks? What do we have MVCC for? Regards Markus Wanner -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Wed, Sep 8, 2010 at 8:43 PM, Robert Haas wrote: > I still don't see why it matters whether you wait before or after > releasing locks. As soon as the transaction is marked committed in > CLOG, other transactions can potentially see its effects. AFAIR, even if CLOG has been updated, until the transaction is marked as no longer running in PGPROC, probably other transactions cannot see its effects. But, if it's not true, I'd make the transaction wait for replication before CLOG update. > And in any event, there is ALWAYS a window of > time during which the client doesn't know the transaction has > committed but other transactions can potentially see its effects. Yep. The problem here is that synchronous replication is likely to make the window very big. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Wed, Sep 8, 2010 at 6:52 AM, Boszormenyi Zoltan wrote: > Fujii Masao írta: >> On Wed, Sep 8, 2010 at 7:04 PM, Boszormenyi Zoltan wrote: >> >>> Then there is no use to implement individual sync/async >>> replicated transactions, period. An async replicated transaction >>> that waits for a sync replicated transaction because of locks >>> will become implicitely sync. It just waits for another transactions' >>> sync ack. >>> >> >> Hmm.. it's the same with async transaction (i.e., synchronous_commit = false) >> and sync one (synchronous_commit = true). Async transaction cannot take the >> lock held by sync one until the sync has flushed the WAL. >> > > You are right. I still don't see why it matters whether you wait before or after releasing locks. As soon as the transaction is marked committed in CLOG, other transactions can potentially see its effects. Holding on to all the locks might mitigate that somewhat, but it's not going to eliminate the problem. And in any event, there is ALWAYS a window of time during which the client doesn't know the transaction has committed but other transactions can potentially see its effects. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
Fujii Masao írta: > On Wed, Sep 8, 2010 at 7:04 PM, Boszormenyi Zoltan wrote: > >> Then there is no use to implement individual sync/async >> replicated transactions, period. An async replicated transaction >> that waits for a sync replicated transaction because of locks >> will become implicitely sync. It just waits for another transactions' >> sync ack. >> > > Hmm.. it's the same with async transaction (i.e., synchronous_commit = false) > and sync one (synchronous_commit = true). Async transaction cannot take the > lock held by sync one until the sync has flushed the WAL. > You are right. -- -- Zoltán Böszörményi Cybertec Schönig & Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de http://www.postgresql.at/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Wed, Sep 8, 2010 at 7:04 PM, Boszormenyi Zoltan wrote: > Then there is no use to implement individual sync/async > replicated transactions, period. An async replicated transaction > that waits for a sync replicated transaction because of locks > will become implicitely sync. It just waits for another transactions' > sync ack. Hmm.. it's the same with async transaction (i.e., synchronous_commit = false) and sync one (synchronous_commit = true). Async transaction cannot take the lock held by sync one until the sync has flushed the WAL. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
Fujii Masao írta: > On Tue, Sep 7, 2010 at 6:02 AM, Simon Riggs wrote: > >> On Mon, 2010-09-06 at 22:32 +0200, Boszormenyi Zoltan wrote: >> >>> (in commit) >>> write wal record >>> release locks/etc >> wait for sync ack >>> >>> In the first case, the contention is obviously increased. >>> With this, we are creating more idle time in the server >>> instead of letting other transactions do their jobs as soon >>> as possible. The second method was implemented in my >>> patch. Are there any drawbacks with this? >>> >> Then I respectfully suggest that you're releasing locks too early. >> >> Your proposal would allow a 2nd user to see the results of the 1st >> user's transaction before the 1st user knew about whether it had >> committed or not. >> >> I know why you want that, but I don't think its right. >> > > Agreed. That's why I put the wait before ProcArrayEndTransaction() > is called. > Then there is no use to implement individual sync/async replicated transactions, period. An async replicated transaction that waits for a sync replicated transaction because of locks will become implicitely sync. It just waits for another transactions' sync ack. Best regards, Zoltán Böszörményi > Regards, > > -- -- Zoltán Böszörményi Cybertec Schönig & Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de http://www.postgresql.at/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Tue, Sep 7, 2010 at 6:02 AM, Simon Riggs wrote: > On Mon, 2010-09-06 at 22:32 +0200, Boszormenyi Zoltan wrote: >> (in commit) >> write wal record >> release locks/etc > wait for sync ack >> >> In the first case, the contention is obviously increased. >> With this, we are creating more idle time in the server >> instead of letting other transactions do their jobs as soon >> as possible. The second method was implemented in my >> patch. Are there any drawbacks with this? > > Then I respectfully suggest that you're releasing locks too early. > > Your proposal would allow a 2nd user to see the results of the 1st > user's transaction before the 1st user knew about whether it had > committed or not. > > I know why you want that, but I don't think its right. Agreed. That's why I put the wait before ProcArrayEndTransaction() is called. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Tue, Sep 7, 2010 at 4:06 PM, marcin mank wrote: > On Tue, Sep 7, 2010 at 5:17 PM, Tom Lane wrote: >> We can *not* allow the slave to replay WAL ahead of what is known >> committed to disk on the master. The only way to make that safe >> is the compare-notes-and-ship-WAL-back approach that Robert mentioned. >> >> If you feel that decoupling WAL application is absolutely essential >> to have a credible feature, then you'd better bite the bullet and >> start working on the ship-WAL-back code. >> > > In the mode where it is not required that the WAL is applied (only > sent to the slave / synced to slave disk) one alternative is to have a > separate pointer to the last WAL record that can be safely applied on > the slave. Then You can send the un-synced WAL to the slave (while > concurrently syncing it on the master). When both the slave an the > master sync complete, one can give the client a commit notification, > increase the pointer, and send it to the slave (it would be a separate > WAL record type I guess). > > In case of master failure, the slave can discard the un-applied WAL > after the pointer. But the pointer on the slave has to be fsync'd to make it persistent, which likely takes roughly the same amount of time as fsync-ing the WAL itself. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Tue, Sep 7, 2010 at 5:17 PM, Tom Lane wrote: > We can *not* allow the slave to replay WAL ahead of what is known > committed to disk on the master. The only way to make that safe > is the compare-notes-and-ship-WAL-back approach that Robert mentioned. > > If you feel that decoupling WAL application is absolutely essential > to have a credible feature, then you'd better bite the bullet and > start working on the ship-WAL-back code. > In the mode where it is not required that the WAL is applied (only sent to the slave / synced to slave disk) one alternative is to have a separate pointer to the last WAL record that can be safely applied on the slave. Then You can send the un-synced WAL to the slave (while concurrently syncing it on the master). When both the slave an the master sync complete, one can give the client a commit notification, increase the pointer, and send it to the slave (it would be a separate WAL record type I guess). In case of master failure, the slave can discard the un-applied WAL after the pointer. Greetings marcin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On 09/07/2010 05:55 PM, Markus Wanner wrote: Robert's argument Sorry, I meant Ron. Regards Markus Wanner -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
Hi, On 09/07/2010 06:00 PM, Robert Haas wrote: People who are more concerned about performance than robustness aren't going to use sync rep in the first place. I'm advocating sync (or eager, FWIW) replication for years, now. One of the hardest preconception I'm always confronted with is: this must perform poorly! Whether or not that's true depends, but my point is: people who need that level of robustness certainly care about performance as well. Telling them to use async replication instead is not an option. (The ability to mix sync and async replication per transaction is one, BTW). They're going to run it in async, which will improve performance by FAR more than you'll ever be able to manage by deciding that you don't care about handling some of the failure cases correctly. Running in async and then trying to achieve the required level of robustness in the application layer pretty certainly performs worse than a good sync replication implementation. Async only wins if you really don't care about the loss of transactions in the case of a failure. In every other case, robustness is better taken care of by the database system itself, IMO. That being said, I certainly agree to do things step by step. And the ability to write to WAL and wait for ack from a standby concurrently can (and probably should) be considered an optimization, yes. Regards Markus Wanner -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Tue, Sep 7, 2010 at 11:06 AM, Simon Riggs wrote: > On Tue, 2010-09-07 at 16:31 +0200, Markus Wanner wrote: >> On 09/07/2010 04:15 PM, Robert Haas wrote: >> > In theory, that's true, but if we do that, then there's an even bigger >> > problem: the slave might have replayed WAL ahead of the master >> > location; therefore the slave is now corrupt and a new base backup >> > must be taken. >> >> The slave isn't corrupt. It would suffice to "late abort" committed >> transactions the master doesn't know about. > > The slave *might* be ahead of the master. And if it is, the case we're > discussing is where the master just crashed and *might* not even be > coming back at all, at least for a while. The standby does differ from > master, but with the master down I don't regard that as a useful > statement. > > If we wait for fsync on master and then transfer to standby the times > are additive. If we do them concurrently the response times will be the > maximum response time of fsync/transfer, as Markus observes. > > ISTM that most people would be more interested in reducing response > times by ~50% rather than in being exactly correct in an edge case. So > we should be planning that as a robustness option, not "it cannot be > done", which seems to be echoing around to much for my liking. People who are more concerned about performance than robustness aren't going to use sync rep in the first place. They're going to run it in async, which will improve performance by FAR more than you'll ever be able to manage by deciding that you don't care about handling some of the failure cases correctly. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
Hi, On 09/07/2010 05:17 PM, Tom Lane wrote: Oh yes it is. If the slave replays WAL that didn't happen on the master, it might for instance have heap tuples in TID slots that are empty on the master, or index pages laid out differently from the master. Trying to apply additional WAL from the master will fail badly. Sure. Reverting to the master's state would be required to be able to safely proceed. Granted, that's far from simple. Robert's argument about read queries on the standby convinced me, that you always need to recover to the node with the newest transactions applied (i.e. better advance rather than revert). Making sure the standby can't ever be ahead of the master node certainly is the simplest way to guarantee that. At its cost for normal operation, though. How about a master failure which leads to a fail-over, immediately followed by a failure of that former standby (and now a master)? The old master might then be in the very same situation: having WAL applied that the new master doesn't. Do we require former masters to fetch a base backup? How does it know the difference, once it gets back up? We can *not* allow the slave to replay WAL ahead of what is known committed to disk on the master. The only way to make that safe is the compare-notes-and-ship-WAL-back approach that Robert mentioned. Agreed. (And it's worth pointing out that this approach has a pretty nasty requirement for a full-cluster crash: all nodes that were synchronously replicated to need to come back up after such a crash, so as to be able to reliably determine which has the newest transaction). If you feel that decoupling WAL application is absolutely essential to have a credible feature, then you'd better bite the bullet and start working on the ship-WAL-back code. My feeling is that WAL is the wrong format to do replication. But that's a another story. Regards Markus Wanner -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
Simon Riggs writes: > On Tue, 2010-09-07 at 11:17 -0400, Tom Lane wrote: >> We can *not* allow the slave to replay WAL ahead of what is known >> committed to disk on the master. The only way to make that safe >> is the compare-notes-and-ship-WAL-back approach that Robert mentioned. >> >> If you feel that decoupling WAL application is absolutely essential >> to have a credible feature, then you'd better bite the bullet and >> start working on the ship-WAL-back code. > Why not just failover? Guaranteed failover is another large piece we don't have. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On 09/07/2010 04:47 PM, Ron Mayer wrote: In that situation, wouldn't it be possible that a different client queried the slave and already saw the result of that transaction which would later be rolled back? Good point, yes. Regards Markus Wanner -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Tue, 2010-09-07 at 11:17 -0400, Tom Lane wrote: > Markus Wanner writes: > > On 09/07/2010 04:15 PM, Robert Haas wrote: > >> In theory, that's true, but if we do that, then there's an even bigger > >> problem: the slave might have replayed WAL ahead of the master > >> location; therefore the slave is now corrupt and a new base backup > >> must be taken. > > > The slave isn't corrupt. It would suffice to "late abort" committed > > transactions the master doesn't know about. > > Oh yes it is. If the slave replays WAL that didn't happen on the > master, it might for instance have heap tuples in TID slots that are > empty on the master, or index pages laid out differently from the > master. Trying to apply additional WAL from the master will fail badly. > > We can *not* allow the slave to replay WAL ahead of what is known > committed to disk on the master. The only way to make that safe > is the compare-notes-and-ship-WAL-back approach that Robert mentioned. > > If you feel that decoupling WAL application is absolutely essential > to have a credible feature, then you'd better bite the bullet and > start working on the ship-WAL-back code. Why not just failover? -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
Markus Wanner writes: > On 09/07/2010 04:15 PM, Robert Haas wrote: >> In theory, that's true, but if we do that, then there's an even bigger >> problem: the slave might have replayed WAL ahead of the master >> location; therefore the slave is now corrupt and a new base backup >> must be taken. > The slave isn't corrupt. It would suffice to "late abort" committed > transactions the master doesn't know about. Oh yes it is. If the slave replays WAL that didn't happen on the master, it might for instance have heap tuples in TID slots that are empty on the master, or index pages laid out differently from the master. Trying to apply additional WAL from the master will fail badly. We can *not* allow the slave to replay WAL ahead of what is known committed to disk on the master. The only way to make that safe is the compare-notes-and-ship-WAL-back approach that Robert mentioned. If you feel that decoupling WAL application is absolutely essential to have a credible feature, then you'd better bite the bullet and start working on the ship-WAL-back code. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Tue, 2010-09-07 at 16:31 +0200, Markus Wanner wrote: > On 09/07/2010 04:15 PM, Robert Haas wrote: > > In theory, that's true, but if we do that, then there's an even bigger > > problem: the slave might have replayed WAL ahead of the master > > location; therefore the slave is now corrupt and a new base backup > > must be taken. > > The slave isn't corrupt. It would suffice to "late abort" committed > transactions the master doesn't know about. The slave *might* be ahead of the master. And if it is, the case we're discussing is where the master just crashed and *might* not even be coming back at all, at least for a while. The standby does differ from master, but with the master down I don't regard that as a useful statement. If we wait for fsync on master and then transfer to standby the times are additive. If we do them concurrently the response times will be the maximum response time of fsync/transfer, as Markus observes. ISTM that most people would be more interested in reducing response times by ~50% rather than in being exactly correct in an edge case. So we should be planning that as a robustness option, not "it cannot be done", which seems to be echoing around to much for my liking. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
Markus Wanner wrote: > On 09/07/2010 02:16 PM, Robert Haas wrote: >> practice, this means that the master and standby need to compare notes >> on the ending WAL location and whichever one is further advanced needs >> to stream the intervening records to the other. > > Not necessarily, no. Remember that the client didn't get a commit > confirmation. So reverting might also be a correct solution (i.e. not > violating the durability constraint). In that situation, wouldn't it be possible that a different client queried the slave and already saw the result of that transaction which would later be rolled back? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On 09/07/2010 04:15 PM, Robert Haas wrote: In theory, that's true, but if we do that, then there's an even bigger problem: the slave might have replayed WAL ahead of the master location; therefore the slave is now corrupt and a new base backup must be taken. The slave isn't corrupt. It would suffice to "late abort" committed transactions the master doesn't know about. However, I realize that undoing of WAL isn't something that's implemented (nor planned). So it's probably easier to forward the master in such a case. Yeah, I hope we'll get there eventually. Understood. Thanks. Markus Wanner -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Tue, Sep 7, 2010 at 9:45 AM, Markus Wanner wrote: > On 09/07/2010 02:16 PM, Robert Haas wrote: >> >> Right, definitely. The trouble is that if they happen concurrently, >> and there's a crash, you have to be prepared for the possibility that >> either one of the two has completed and the other is not. > > Understood. > >> In >> practice, this means that the master and standby need to compare notes >> on the ending WAL location and whichever one is further advanced needs >> to stream the intervening records to the other. > > Not necessarily, no. Remember that the client didn't get a commit > confirmation. So reverting might also be a correct solution (i.e. not > violating the durability constraint). In theory, that's true, but if we do that, then there's an even bigger problem: the slave might have replayed WAL ahead of the master location; therefore the slave is now corrupt and a new base backup must be taken. >> This would be an >> awesome feature, but it's hard, so for a first version, it makes sense >> to commit on the master first and then on the standby after the master >> is known done. > > The obvious downside of that is that latency adds up, instead of just being > the max of the two operations. And that for normal operation. While at best > it saves an un-confirmed transaction in the failure case. > > It might be harder to implement, yes. Yeah, I hope we'll get there eventually. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On 09/07/2010 02:16 PM, Robert Haas wrote: Right, definitely. The trouble is that if they happen concurrently, and there's a crash, you have to be prepared for the possibility that either one of the two has completed and the other is not. Understood. In practice, this means that the master and standby need to compare notes on the ending WAL location and whichever one is further advanced needs to stream the intervening records to the other. Not necessarily, no. Remember that the client didn't get a commit confirmation. So reverting might also be a correct solution (i.e. not violating the durability constraint). This would be an awesome feature, but it's hard, so for a first version, it makes sense to commit on the master first and then on the standby after the master is known done. The obvious downside of that is that latency adds up, instead of just being the max of the two operations. And that for normal operation. While at best it saves an un-confirmed transaction in the failure case. It might be harder to implement, yes. Regards Markus Wanner -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Tue, Sep 7, 2010 at 4:01 AM, Markus Wanner wrote: > In any case, a server failure in between the commit request of the client > and the commit confirmation for the client results in a client that can't > tell if its transaction committed or not. > > So why do we care what to do first internally? Ideally, these two tasks > should happen concurrently, IMO. Right, definitely. The trouble is that if they happen concurrently, and there's a crash, you have to be prepared for the possibility that either one of the two has completed and the other is not. In practice, this means that the master and standby need to compare notes on the ending WAL location and whichever one is further advanced needs to stream the intervening records to the other. This would be an awesome feature, but it's hard, so for a first version, it makes sense to commit on the master first and then on the standby after the master is known done. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
Hi, On 05/27/2010 01:28 PM, Robert Haas wrote: How do you propose to guarantee that? ISTM that you have to either commit locally first, or send the commit to the remote first. Either way, the two events won't occur exactly simultaneously. I'm not getting the point of this discussion. As long as the database confirms the commit to the client only *after* having an ack from the standby and *after* committing locally, there's no problem. In any case, a server failure in between the commit request of the client and the commit confirmation for the client results in a client that can't tell if its transaction committed or not. So why do we care what to do first internally? Ideally, these two tasks should happen concurrently, IMO. Regards Markus Wanner -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Mon, Sep 6, 2010 at 5:02 PM, Simon Riggs wrote: > Then I respectfully suggest that you're releasing locks too early. > > Your proposal would allow a 2nd user to see the results of the 1st > user's transaction before the 1st user knew about whether it had > committed or not. Marking the transaction committed in CLOG will have that effect anyway. We are not doing strict two-phase locking. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Mon, 2010-09-06 at 16:11 -0400, Tom Lane wrote: > Simon Riggs writes: > > On Mon, 2010-09-06 at 21:45 +0200, Boszormenyi Zoltan wrote: > >> The locks held by a transaction are released after > >> RecordTransactionCommit(), and waiting for the sync ack > >> happens in this function. Now what happens when a sync > >> transaction hold a lock that an async one is waiting for? > > > It seems your glass in half-empty. Mine is half-full. > > Simon, you really are failing to advance the conversation. You claim > that we can have sync plus async transactions without a performance hit, > but you have failed to explain how, at least in any way that anyone > else understands. Pontificating about how that will be so much better > than not having it doesn't address the problem that others are having > with seeing how to implement it. A performance hit from mixing sync and async is unlikely. The overhead of deciding whether to wait after commit is trivial. At worst, the async transactions would go at the same speed as the sync transactions, especially if the application contends with itself, which is by no means a certainty. If acting independently, the async transactions would clearly go much faster. So the right question for discussion is "how much will we gain by mixing async/sync?". Since we had exactly this situation for synchronous_commit and a similar discussion, I expect a similar eventual outcome. The discussion would go better if we had clear performance results published from existing work and we did not dissuade people from objective testing. Then you'd probably understand why I think this is so important to me. I've explained more than once how my proposal can work and Dimitri at least appears to have understood with zero off-list conversation. So far the discussion has been mostly negative and the reasons given haven't scored high on logic, I'm sorry to say. I will present a code-based proposal rather than just huge piles of words, to make this a more concrete discussion. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Mon, 2010-09-06 at 23:07 +0100, Greg Stark wrote: > On Mon, Sep 6, 2010 at 10:02 PM, Simon Riggs wrote: > > Then I respectfully suggest that you're releasing locks too early. > > > > Your proposal would allow a 2nd user to see the results of the 1st > > user's transaction before the 1st user knew about whether it had > > committed or not. > > > > I know why you want that, but I don't think its right. > > Well that's always possible. The 1st user might just not wake up > before the 2nd user gets the response back. > > The question is what happens if the server crashes and is failed over > to the slave. The 2nd user with the async transaction might have seen > data commited by the 1st user with his sync transaction but was > subsequently lost. Is the user expecting that making his transaction > synchronously replicated guarantees that *nobody* can see this data > unless the transaction is guaranteed to have been replicated or is he > only expecting it to guarantee that *he* can't see the commit until it > can be trusted to be replicated? > > For that matter I'm not entirely clear I understand how the timing > here works at all. If transactions can't be considered to be committed > before they're acknowledged by the replica what happens if the master > crashes after the WAL is written and then comes back without a > failover. Then the transaction would be immediately visible even if it > still hasn't been replicated yet. > > I think there's no way with our current infrastructure to guarantee > that other transactions can't see your data before it's been > replicated, So making any promise otherwise for some cases is only > going to be a lie. > > To guarantee synchronous replication doesn't show data until it's been > replicated we would have to some kind of 2-phase commit where we send > the commit record to the slave and wait until the slave has received > it and confirmed it has written it (but it doesn't replay it unless > there's a failover) then write the master's commit record and send the > message to the slave that it's safe to replay those records. Just to add that this part of the discussion has nothing at all to do with my proposal for master controlled replication. Zoltan is simply discussing when the wait should occur with sync replication. I have no proposal to vary that myself, wherever we eventually decide the wait should occur. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Mon, Sep 6, 2010 at 10:02 PM, Simon Riggs wrote: > Then I respectfully suggest that you're releasing locks too early. > > Your proposal would allow a 2nd user to see the results of the 1st > user's transaction before the 1st user knew about whether it had > committed or not. > > I know why you want that, but I don't think its right. Well that's always possible. The 1st user might just not wake up before the 2nd user gets the response back. The question is what happens if the server crashes and is failed over to the slave. The 2nd user with the async transaction might have seen data commited by the 1st user with his sync transaction but was subsequently lost. Is the user expecting that making his transaction synchronously replicated guarantees that *nobody* can see this data unless the transaction is guaranteed to have been replicated or is he only expecting it to guarantee that *he* can't see the commit until it can be trusted to be replicated? For that matter I'm not entirely clear I understand how the timing here works at all. If transactions can't be considered to be committed before they're acknowledged by the replica what happens if the master crashes after the WAL is written and then comes back without a failover. Then the transaction would be immediately visible even if it still hasn't been replicated yet. I think there's no way with our current infrastructure to guarantee that other transactions can't see your data before it's been replicated, So making any promise otherwise for some cases is only going to be a lie. To guarantee synchronous replication doesn't show data until it's been replicated we would have to some kind of 2-phase commit where we send the commit record to the slave and wait until the slave has received it and confirmed it has written it (but it doesn't replay it unless there's a failover) then write the master's commit record and send the message to the slave that it's safe to replay those records. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Mon, 2010-09-06 at 22:32 +0200, Boszormenyi Zoltan wrote: > (in commit) > write wal record > release locks/etcwait for sync ack > > In the first case, the contention is obviously increased. > With this, we are creating more idle time in the server > instead of letting other transactions do their jobs as soon > as possible. The second method was implemented in my > patch. Are there any drawbacks with this? Then I respectfully suggest that you're releasing locks too early. Your proposal would allow a 2nd user to see the results of the 1st user's transaction before the 1st user knew about whether it had committed or not. I know why you want that, but I don't think its right. This has very little, if anything, to do with mixing async/sync connections. You make it sound like all transactions always wait for other transactions, which they definitely don't, especially in reasonably well designed applications. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
Simon Riggs írta: > On Mon, 2010-09-06 at 21:45 +0200, Boszormenyi Zoltan wrote: > >> Dimitri Fontaine írta: >> >>> Boszormenyi Zoltan writes: >>> >>> Sorry for answering such an old mail, but what is the purpose of a transaction level synchronous behaviour if async transactions can be held back by a sync transaction? >>> I don't understand why it would be the case (sync holding back async >>> transactions) — it's been proposed that walsender could periodically >>> feed back to the master the current WAL position received, synced and >>> applied. >>> >>> So you can register your sync transaction to wait (and block) until >>> walsender sees a synced WAL position after your own (including it) and >>> another transaction can wait until walsender sees a received WAL >>> position after its own, for example. Of course, meanwhile, any async >>> transaction would just commit without caring about slaves. >>> >>> >> The locks held by a transaction are released after >> RecordTransactionCommit(), and waiting for the sync ack >> happens in this function. Now what happens when a sync >> transaction hold a lock that an async one is waiting for? >> > > It seems your glass in half-empty. Mine is half-full. This is good, we can meet halfway. :-) > My perspective > would be that if there is contention between async and sync transactions > then we will get better throughout than if all transactions were sync. > Though perhaps the main issue in that case would be application lock > contention, not the speed of synchronous replication. > The difference we are talking about is: xact1xact2 begin begin lock something lock same (in commit) write wal record wait for sync ack release locks/etcThe highest level issue is that the system only has so much physical > resources. If we are unable to focus our resources onto the things that > matter most then we end up wasting resources. Mixing async and sync > transactions on the same server allows a single application to carefully > balance performance and durability. Exactly as we do with > synchronous_commit. > I don't think this is the same situation. With synchronous_commit, you have an auxiliary process that's handed the job of doing the syncing. But there's nowhere to hand out the waiting for sync ack from the standby. > By now, people are beginning to see that synchronous replication is > important but has poor performance. Fine grained control is essential to > using it effectively in areas that matter most. > -- -- Zoltán Böszörményi Cybertec Schönig & Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de http://www.postgresql.at/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
Simon Riggs writes: > On Mon, 2010-09-06 at 21:45 +0200, Boszormenyi Zoltan wrote: >> The locks held by a transaction are released after >> RecordTransactionCommit(), and waiting for the sync ack >> happens in this function. Now what happens when a sync >> transaction hold a lock that an async one is waiting for? > It seems your glass in half-empty. Mine is half-full. Simon, you really are failing to advance the conversation. You claim that we can have sync plus async transactions without a performance hit, but you have failed to explain how, at least in any way that anyone else understands. Pontificating about how that will be so much better than not having it doesn't address the problem that others are having with seeing how to implement it. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Mon, 2010-09-06 at 21:45 +0200, Boszormenyi Zoltan wrote: > Dimitri Fontaine írta: > > Boszormenyi Zoltan writes: > > > >> Sorry for answering such an old mail, but what is the purpose of > >> a transaction level synchronous behaviour if async transactions > >> can be held back by a sync transaction? > >> > > > > I don't understand why it would be the case (sync holding back async > > transactions) — it's been proposed that walsender could periodically > > feed back to the master the current WAL position received, synced and > > applied. > > > > So you can register your sync transaction to wait (and block) until > > walsender sees a synced WAL position after your own (including it) and > > another transaction can wait until walsender sees a received WAL > > position after its own, for example. Of course, meanwhile, any async > > transaction would just commit without caring about slaves. > > > > The locks held by a transaction are released after > RecordTransactionCommit(), and waiting for the sync ack > happens in this function. Now what happens when a sync > transaction hold a lock that an async one is waiting for? It seems your glass in half-empty. Mine is half-full. My perspective would be that if there is contention between async and sync transactions then we will get better throughout than if all transactions were sync. Though perhaps the main issue in that case would be application lock contention, not the speed of synchronous replication. The highest level issue is that the system only has so much physical resources. If we are unable to focus our resources onto the things that matter most then we end up wasting resources. Mixing async and sync transactions on the same server allows a single application to carefully balance performance and durability. Exactly as we do with synchronous_commit. By now, people are beginning to see that synchronous replication is important but has poor performance. Fine grained control is essential to using it effectively in areas that matter most. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
Dimitri Fontaine írta: > Boszormenyi Zoltan writes: > >> Sorry for answering such an old mail, but what is the purpose of >> a transaction level synchronous behaviour if async transactions >> can be held back by a sync transaction? >> > > I don't understand why it would be the case (sync holding back async > transactions) — it's been proposed that walsender could periodically > feed back to the master the current WAL position received, synced and > applied. > > So you can register your sync transaction to wait (and block) until > walsender sees a synced WAL position after your own (including it) and > another transaction can wait until walsender sees a received WAL > position after its own, for example. Of course, meanwhile, any async > transaction would just commit without caring about slaves. > The locks held by a transaction are released after RecordTransactionCommit(), and waiting for the sync ack happens in this function. Now what happens when a sync transaction hold a lock that an async one is waiting for? > Not implementing it nor thinking about how to implement it, it seems > simple enough :) > > Regards, > -- -- Zoltán Böszörményi Cybertec Schönig & Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de http://www.postgresql.at/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
Boszormenyi Zoltan writes: > Sorry for answering such an old mail, but what is the purpose of > a transaction level synchronous behaviour if async transactions > can be held back by a sync transaction? I don't understand why it would be the case (sync holding back async transactions) — it's been proposed that walsender could periodically feed back to the master the current WAL position received, synced and applied. So you can register your sync transaction to wait (and block) until walsender sees a synced WAL position after your own (including it) and another transaction can wait until walsender sees a received WAL position after its own, for example. Of course, meanwhile, any async transaction would just commit without caring about slaves. Not implementing it nor thinking about how to implement it, it seems simple enough :) Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
Fujii Masao írta: > On Fri, Sep 3, 2010 at 6:43 PM, Boszormenyi Zoltan wrote: > >> In my patch, when the transactions were waiting for ack from >> the standby, they have already released all their locks, the wait >> happened at the latest possible point in CommitTransaction(). >> >> In Fujii's patch (I am looking at synch_rep_0722.patch, is there >> a newer one?) >> > > No ;) > > We'll have to create the patch based on the result of the recent > discussion held on other thread. > > >> the wait happens in RecordTransactionCommit() >> so other transactions still see the sync transaction and most >> importantly, the locks held by the sync transaction will make >> the async transactions waiting for the same lock wait too. >> > > The transaction should be invisible to other transactions until > its replication has been completed. Invisible? How can it be invisible? You are in RecordTransactionCommit(), even before calling ProcArrayEndTransaction(MyProc, latestXid) and releasing the locks the transaction holds. > So I put the wait before > CommitTransaction() calls ProcArrayEndTransaction(). Is this unsafe? > I don't know whether it's unsafe. In my patch, I only registered the Xid at the point where you do WaitXLogSend(), this was the safe point to setup the waiting for sync ack. Otherwise, when the Xid registration for the sync ack was done in CommitTransaction() later than RecordTransactionCommit(), there was a race between the primary and the standby. The scenario was that the standby received and processed the COMMIT of certain Xids even before the backend on the primary properly registered its Xid, so the backend has set up the waiting for sync ack after this Xid was acked by the standby. The result was stuck backends. My idea to split up the registration for wait and the waiting itself would allow for transaction-level synchronous setting, i.e. in my patch the transaction released the locks and did all the post-commit cleanups *then* it waited for sync ack if needed. In the meantime, because locks were already released, other transactions could progress with their job, allowing that e.g. async transactions to progress and theoretically finish faster than the sync transaction that was waiting for the ack. The solution in my patch was not racy, registration of the Xid was done before XLogInsert() in RecordTransactionCommit(). If the standby acked the Xid to the primary before reaching the end of CommitTransaction() then this backend didn't even needed to wait because the Xid was found in its PGPROC structure and the waiting for sync ack was torn down. But with the LSNs, as you are waiting for XactLastRecEnd which is set by XLogInsert(). I don't know if it's safe to WaitXLogSend() after XLogFlush() in RecordTransactionCommit(). I remember that in previous instances of my patch even if I put the waiting for sync ack directly after latestXid = RecordTransactionCommit(); in CommitTransaction(), there were cases when I got stuck backends after a pgbench run. I had the primary and standbys on the same machine on different ports, so the ack was almost instant, which wouldn't be the case with a real network. But the race condition was still there it just doesn't show up with networks being slower than memory. In your patch, the waiting happens almost at the end of RecordTransactionCommit(), so theoretically it has the same race condition. Am I missing something? Best regards, Zoltán Böszörményi > Regards, > > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Fri, Sep 3, 2010 at 6:43 PM, Boszormenyi Zoltan wrote: > In my patch, when the transactions were waiting for ack from > the standby, they have already released all their locks, the wait > happened at the latest possible point in CommitTransaction(). > > In Fujii's patch (I am looking at synch_rep_0722.patch, is there > a newer one?) No ;) We'll have to create the patch based on the result of the recent discussion held on other thread. > the wait happens in RecordTransactionCommit() > so other transactions still see the sync transaction and most > importantly, the locks held by the sync transaction will make > the async transactions waiting for the same lock wait too. The transaction should be invisible to other transactions until its replication has been completed. So I put the wait before CommitTransaction() calls ProcArrayEndTransaction(). Is this unsafe? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
Hi, Dimitri Fontaine írta: > Simon Riggs writes: > >> Seems strange. If you have 2 standbys and you say you would like node1 >> to be the preferred candidate, then you load it so heavily that a remote >> server with by-definition much larger network delay responds first, then >> I say your preference was wrong. >> > > There's a communication mismatch here I think. The problem with the > dynamic aspect of the system is that the admin wants to plan ahead and > choose in advance the failover server. > > Other than that I much prefer the automatic and dynamic quorum idea. > > >> If you, Jan and Yeb wish to completely exclude standbys from being part >> of any quorum, then I guess we need to have per-standby settings to >> allow that to be defined. I'm in favour of giving people options. That >> needn't be a mandatory per-standby setting, just a non-default option, >> so that we can reduce the complexity of configuration for common >> cases. >> > > +1 > > >> Maximum Performance => quorum = 0 >> Maximum Availability => quorum = 1, timeout_action = commit >> Maximum Protection => quorum = 1, timeout_action = shutdown >> > > +1 > > Being able to say that a given server has not been granted to > participate into the vote allowing to reach the global durability quorum > will allow for choosing the failover candidates. > > Now you're able to have this reporting server and know for sure that > your sync replicated transactions are not waiting for it. > > To summarize, the current "per-transaction approach" would be : > > - transaction level replication synchronous behaviour > Sorry for answering such an old mail, but what is the purpose of a transaction level synchronous behaviour if async transactions can be held back by a sync transaction? In my patch, when the transactions were waiting for ack from the standby, they have already released all their locks, the wait happened at the latest possible point in CommitTransaction(). In Fujii's patch (I am looking at synch_rep_0722.patch, is there a newer one?) the wait happens in RecordTransactionCommit() so other transactions still see the sync transaction and most importantly, the locks held by the sync transaction will make the async transactions waiting for the same lock wait too. > - proxy/cascading in core > - quorum setup for deciding any commit is safe > - any server can be excluded from the sync quorum > - timeout can still raises exception or ignore (commit)? > > This last point seems to need some more discussion, or I didn't > understand well the current positions and proposals. > > Regards, > Best regards, Zoltán Böszörményi -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On 6/4/2010 4:22 PM, Robert Haas wrote: On Fri, Jun 4, 2010 at 3:35 PM, Jan Wieck wrote: So that justifies adding code, that the community needs to maintain and document, to the core system. If only I could find some monitoring case for transaction commit orders ... sigh! Dude, I'm not the one arguing with you... actually I don't think anyone really is, any more, except about details. I know. You actually pretty much defend my case. Sorry for lacking smiley's. This is an old habit I have. A good friend from Germany once suspected one of my emails to be a spoof because I actually used a smiley. Jan -- Anyone who trades liberty for security deserves neither liberty nor security. -- Benjamin Franklin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Fri, Jun 4, 2010 at 3:35 PM, Jan Wieck wrote: > On 6/3/2010 10:57 PM, Robert Haas wrote: >> >> On Thu, Jun 3, 2010 at 8:47 PM, Jan Wieck wrote: >>> >>> On 5/27/2010 4:31 PM, Bruce Momjian wrote: Also, what would be cool would be if you could run a query on the master to view the SR commit mode of each slave. >>> >>> What would be the use case for such a query? >> >> Monitoring? > > So that justifies adding code, that the community needs to maintain and > document, to the core system. If only I could find some monitoring case for > transaction commit orders ... sigh! Dude, I'm not the one arguing with you... actually I don't think anyone really is, any more, except about details. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On 6/3/2010 10:57 PM, Robert Haas wrote: On Thu, Jun 3, 2010 at 8:47 PM, Jan Wieck wrote: On 5/27/2010 4:31 PM, Bruce Momjian wrote: Also, what would be cool would be if you could run a query on the master to view the SR commit mode of each slave. What would be the use case for such a query? Monitoring? So that justifies adding code, that the community needs to maintain and document, to the core system. If only I could find some monitoring case for transaction commit orders ... sigh! Jan -- Anyone who trades liberty for security deserves neither liberty nor security. -- Benjamin Franklin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Thu, Jun 03, 2010 at 10:57:05PM -0400, Robert Haas wrote: > On Thu, Jun 3, 2010 at 8:47 PM, Jan Wieck wrote: > > What would be the use case for such a query? > > Monitoring? s/\?/!/; Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Thu, Jun 3, 2010 at 8:47 PM, Jan Wieck wrote: > On 5/27/2010 4:31 PM, Bruce Momjian wrote: >> >> Heikki Linnakangas wrote: >>> >>> BTW, I think we're going to need a separate config file for listing the >>> standbys anyway. There you can write per-server rules and options, but >>> explicitly knowing about all the standbys also allows the master to recycle >>> WAL as soon as it has been streamed to all the registered standbys. >>> Currently we just keep wal_keep_segments files around, just in case there's >>> a standby out there that needs them. >> >> Ideally we could set 'slave_sync_count' and 'slave_commit_continue_mode' >> on the master, and allow the sync/async mode to be set on each slave, >> e.g. if slave_sync_count = 2 and slave_commit_continue_mode = #2, then >> two slaves with sync mode of #2 or stricter have to complete before the >> master can continue. >> >> Naming the slaves on the master seems very confusing because I am >> unclear how we would identify named slaves, and the names have to match, >> etc. >> Also, what would be cool would be if you could run a query on the master >> to view the SR commit mode of each slave. > > What would be the use case for such a query? Monitoring? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On 5/27/2010 4:31 PM, Bruce Momjian wrote: Heikki Linnakangas wrote: BTW, I think we're going to need a separate config file for listing the standbys anyway. There you can write per-server rules and options, but explicitly knowing about all the standbys also allows the master to recycle WAL as soon as it has been streamed to all the registered standbys. Currently we just keep wal_keep_segments files around, just in case there's a standby out there that needs them. Ideally we could set 'slave_sync_count' and 'slave_commit_continue_mode' on the master, and allow the sync/async mode to be set on each slave, e.g. if slave_sync_count = 2 and slave_commit_continue_mode = #2, then two slaves with sync mode of #2 or stricter have to complete before the master can continue. Naming the slaves on the master seems very confusing because I am unclear how we would identify named slaves, and the names have to match, etc. Also, what would be cool would be if you could run a query on the master to view the SR commit mode of each slave. What would be the use case for such a query? Jan -- Anyone who trades liberty for security deserves neither liberty nor security. -- Benjamin Franklin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
Tom Lane wrote: Heikki Linnakangas writes: It's pretty scary to call a user-defined function at that point in transaction. Not so much "pretty scary" as "zero chance of being accepted". And I do mean zero. I swear, you guys are such buzzkills some days. I was suggesting a model for building easy prototypes, and advocating a more formal way to explain, in what could be code form, what someone means when they suggest a particular quorum model or the like. Maybe all that will ever be exposed into a production server are the best of the hand-written implementations, and the scary "try your prototype here" hook only shows up in debug builds, or never gets written at all. I did comment that I expected faster built-in implementations to be the primary way these would be handled. From what Heikki said, it sounds like the main thing I was didn't remember is to include some timestamp information to allow rules based on that information too. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
Heikki Linnakangas writes: > It's pretty scary to call a user-defined function at that point in > transaction. Not so much "pretty scary" as "zero chance of being accepted". And I do mean zero. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Wed, 2010-06-02 at 03:22 -0400, Greg Smith wrote: > Heikki Linnakangas wrote: > > The possibilities are endless... Your proposal above covers a pretty > > good set of scenarios, but it's by no means complete. If we try to > > solve everything the configuration will need to be written in a > > Turing-complete Replication Description Language. We'll have to pick a > > useful, easy-to-understand subset that covers the common scenarios. To > > handle the more exotic scenarios, you can write a proxy that sits in > > front of the master, and implements whatever rules you wish, with the > > rules written in C. > > I was thinking about this a bit recently. As I see it, there are three > fundamental parts of this: > > 1) We have a transaction that is being committed. The rest of the > computations here are all relative to it. > > 2) There is an (internal?) table that lists the state of each > replication target relative to that transaction. It would include the > node name, perhaps some metadata ('location' seems the one that's most > likely to help with the remote data center issue), and a state code. > The codes from http://wiki.postgresql.org/wiki/Streaming_Replication > work fine for the last part (which is the only dynamic one--everything > else is static data being joined against): > > async=hasn't received yet > recv=been received but just in RAM > fsync=received and synced to disk > apply=applied to the database > > These would need to be enums so they can be ordered from lesser to > greater consistency. > > So in a 3 node case, the internal state table might look like this after > a bit of data had been committed: > > node | location | state > -- > a | local| fsync > b | remote | recv > c | remote | async > > This means that the local node has a fully persistent copy, but the best > either remote one has done is received the data, it's not on disk at all > yet at the remote data center. Still working its way through. > > 3) The decision about whether the data has been committed to enough > places to be considered safe by the master is computed by a function > that is passed this internal table as something like a SRF, and it > returns a boolean. Once that returns true, saying it's satisfied, the > transaction closes on the master and continues to percolate out from > there. If it's false, we wait for another state change to come in and > return to (2). > > I would propose that most behaviors someone has expressed as being their > desired implementation is possible to implement using this scheme. > > -Semi-sync commit: proceed as soon somebody else has a copy and hope > the copies all become consistent: EXISTS WHERE state>=recv > -Don't proceed until there's a fsync'd commit on at least one of the > remote nodes: EXISTS WHERE location='remote' AND state>=fsync > -Look for a quorum of n commits of fsync quality: CASE WHEN (SELECT > COUNT(*) WHERE state>=fsync)>n THEN true else FALSE end; > > Syntax is obviously rough but I think you can get the drift of what I'm > suggesting. > > While exposing the local state and running this computation isn't free, > in situations where there truly are remote nodes in here being > communicated with the network overhead is going to dwarf that. If there > were a fast path for the simplest cases and this complicated one for the > rest, I think you could get the fully programmable behavior some people > want using simple SQL, rather than having to write a new "Replication > Description Language" or something so ambitious. This data about what's > been replicated to where looks an awful lot like a set of rows you can > operate on using features already in the database to me. I think we're all agreed on the 4 levels: async, recv, fsync, apply. I also like the concept of a synchronisation/wakeup rule as an abstract concept. Certainly makes things easier to discuss. The inputs to the wakeup rule can be defined in different ways. Holding per-node state at local level looks too complex to me. I'm not suggesting that we need both per-node AND per-transaction options interacting at the same time. (That would be a clear argument against per-transaction options, if that was a requirement - its not, for me). There seems to be a simpler way: a service oriented model. The transaction requests a minimum level of synchronisation, the standbys together service that request. A simple, clear process: 1. If transaction requests recv, fsync or apply, backend sleeps in the appropriate queue 2. An agent on behalf of the remote standby provides feedback according to the levels of service defined for that standby. 3. The agent calls a wakeup-rule to see if the backend can be woken yet The most basic rule is "first-standby-wakes" meaning that the first standby to provide feedback that the required synchronisation level has been met by at least one standby will cause the rule to fire. The n
Re: [HACKERS] Synchronization levels in SR
On 02/06/10 10:22, Greg Smith wrote: Heikki Linnakangas wrote: The possibilities are endless... Your proposal above covers a pretty good set of scenarios, but it's by no means complete. If we try to solve everything the configuration will need to be written in a Turing-complete Replication Description Language. We'll have to pick a useful, easy-to-understand subset that covers the common scenarios. To handle the more exotic scenarios, you can write a proxy that sits in front of the master, and implements whatever rules you wish, with the rules written in C. I was thinking about this a bit recently. As I see it, there are three fundamental parts of this: 1) We have a transaction that is being committed. The rest of the computations here are all relative to it. Agreed. So in a 3 node case, the internal state table might look like this after a bit of data had been committed: node | location | state -- a | local | fsync b | remote | recv c | remote | async This means that the local node has a fully persistent copy, but the best either remote one has done is received the data, it's not on disk at all yet at the remote data center. Still working its way through. 3) The decision about whether the data has been committed to enough places to be considered safe by the master is computed by a function that is passed this internal table as something like a SRF, and it returns a boolean. Once that returns true, saying it's satisfied, the transaction closes on the master and continues to percolate out from there. If it's false, we wait for another state change to come in and return to (2). You can't implement "wait for X to ack the commit, but if that doesn't happen in Y seconds, time out and return true anyway" with that. While exposing the local state and running this computation isn't free, in situations where there truly are remote nodes in here being communicated with the network overhead is going to dwarf that. If there were a fast path for the simplest cases and this complicated one for the rest, I think you could get the fully programmable behavior some people want using simple SQL, rather than having to write a new "Replication Description Language" or something so ambitious. This data about what's been replicated to where looks an awful lot like a set of rows you can operate on using features already in the database to me. Yeah, if we want to provide full control over when a commit is acknowledged to the client, there's certainly no reason we can't expose that using a hook or something. It's pretty scary to call a user-defined function at that point in transaction. Even if we document that you must refrain from doing nasty stuff like modifying tables in that function, it's still scary. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
Heikki Linnakangas wrote: The possibilities are endless... Your proposal above covers a pretty good set of scenarios, but it's by no means complete. If we try to solve everything the configuration will need to be written in a Turing-complete Replication Description Language. We'll have to pick a useful, easy-to-understand subset that covers the common scenarios. To handle the more exotic scenarios, you can write a proxy that sits in front of the master, and implements whatever rules you wish, with the rules written in C. I was thinking about this a bit recently. As I see it, there are three fundamental parts of this: 1) We have a transaction that is being committed. The rest of the computations here are all relative to it. 2) There is an (internal?) table that lists the state of each replication target relative to that transaction. It would include the node name, perhaps some metadata ('location' seems the one that's most likely to help with the remote data center issue), and a state code. The codes from http://wiki.postgresql.org/wiki/Streaming_Replication work fine for the last part (which is the only dynamic one--everything else is static data being joined against): async=hasn't received yet recv=been received but just in RAM fsync=received and synced to disk apply=applied to the database These would need to be enums so they can be ordered from lesser to greater consistency. So in a 3 node case, the internal state table might look like this after a bit of data had been committed: node | location | state -- a | local| fsync b | remote | recv c | remote | async This means that the local node has a fully persistent copy, but the best either remote one has done is received the data, it's not on disk at all yet at the remote data center. Still working its way through. 3) The decision about whether the data has been committed to enough places to be considered safe by the master is computed by a function that is passed this internal table as something like a SRF, and it returns a boolean. Once that returns true, saying it's satisfied, the transaction closes on the master and continues to percolate out from there. If it's false, we wait for another state change to come in and return to (2). I would propose that most behaviors someone has expressed as being their desired implementation is possible to implement using this scheme. -Semi-sync commit: proceed as soon somebody else has a copy and hope the copies all become consistent: EXISTS WHERE state>=recv -Don't proceed until there's a fsync'd commit on at least one of the remote nodes: EXISTS WHERE location='remote' AND state>=fsync -Look for a quorum of n commits of fsync quality: CASE WHEN (SELECT COUNT(*) WHERE state>=fsync)>n THEN true else FALSE end; Syntax is obviously rough but I think you can get the drift of what I'm suggesting. While exposing the local state and running this computation isn't free, in situations where there truly are remote nodes in here being communicated with the network overhead is going to dwarf that. If there were a fast path for the simplest cases and this complicated one for the rest, I think you could get the fully programmable behavior some people want using simple SQL, rather than having to write a new "Replication Description Language" or something so ambitious. This data about what's been replicated to where looks an awful lot like a set of rows you can operate on using features already in the database to me. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On May 27, 2010, at 4:31 PM, Bruce Momjian wrote: Heikki Linnakangas wrote: BTW, I think we're going to need a separate config file for listing the standbys anyway. There you can write per-server rules and options, but explicitly knowing about all the standbys also allows the master to recycle WAL as soon as it has been streamed to all the registered standbys. Currently we just keep wal_keep_segments files around, just in case there's a standby out there that needs them. Ideally we could set 'slave_sync_count' and 'slave_commit_continue_mode' on the master, and allow the sync/async mode to be set on each slave, e.g. if slave_sync_count = 2 and slave_commit_continue_mode = #2, then two slaves with sync mode of #2 or stricter have to complete before the master can continue. Naming the slaves on the master seems very confusing because I am unclear how we would identify named slaves, and the names have to match, etc. The names could be configured with a GUC on the slaves, or we could base it on the login role. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
Heikki Linnakangas wrote: > BTW, I think we're going to need a separate config file for listing the > standbys anyway. There you can write per-server rules and options, but > explicitly knowing about all the standbys also allows the master to > recycle WAL as soon as it has been streamed to all the registered > standbys. Currently we just keep wal_keep_segments files around, just in > case there's a standby out there that needs them. Ideally we could set 'slave_sync_count' and 'slave_commit_continue_mode' on the master, and allow the sync/async mode to be set on each slave, e.g. if slave_sync_count = 2 and slave_commit_continue_mode = #2, then two slaves with sync mode of #2 or stricter have to complete before the master can continue. Naming the slaves on the master seems very confusing because I am unclear how we would identify named slaves, and the names have to match, etc. Also, what would be cool would be if you could run a query on the master to view the SR commit mode of each slave. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
Simon Riggs wrote: > On Wed, 2010-05-26 at 18:52 +0900, Fujii Masao wrote: > > > I guess that dropping the support of #3 doesn't reduce complexity > > since the code of #3 is almost the same as that of #2. Like > > walreceiver sends the ACK after receiving the WAL in #2 case, it has > > only to do the same thing after the WAL flush. > > Hmm, well the code for #3 is similar also to the code for #4. So if you > do #2, its easy to do #2, #3 and #4 together. > > The comment is about whether having #3 makes sense from a user interface > perspective. It's easy to add options, but they must have useful > meaning. If the slave is runing read-only queries, #3 is the most reliable option withouth delaying the slave, so there is a usecase for #3. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Thu, May 27, 2010 at 9:09 AM, Fujii Masao wrote: > On Thu, May 27, 2010 at 9:48 PM, Robert Haas wrote: >> There could still be additional transactions that the original master >> has committed locally but were not acked to the client. I guess you'd >> just work around that by taking a new base backup from the new master. > > Right. > > Unfortunately the transaction aborted for a client might have already > been committed in the standby. In this case, we might need to eliminate > the mismatch of transaction status between a client and new master > after failover. > > BTW, the similar situation might happen even when only one server is > running. If the server goes down before returning a "success" to a > client after flushing the commit record, the mismatch would happen > after restart of the server. True. But that's a slightly different case. Clients could fail to receive commit ACKs for a variety of reasons, like losing network connectivity momentarily. They had better be prepared for that no matter whether replication is in use or not. The new issue that replication adds is that you've got to make sure that the two (or n) nodes don't disagree with each other. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Thu, May 27, 2010 at 9:48 PM, Robert Haas wrote: > There could still be additional transactions that the original master > has committed locally but were not acked to the client. I guess you'd > just work around that by taking a new base backup from the new master. Right. Unfortunately the transaction aborted for a client might have already been committed in the standby. In this case, we might need to eliminate the mismatch of transaction status between a client and new master after failover. BTW, the similar situation might happen even when only one server is running. If the server goes down before returning a "success" to a client after flushing the commit record, the mismatch would happen after restart of the server. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Thu, May 27, 2010 at 8:02 AM, Fujii Masao wrote: > On Thu, May 27, 2010 at 8:28 PM, Robert Haas wrote: >> On Thu, May 27, 2010 at 3:13 AM, Fujii Masao wrote: >>> (1) most standard case: 1 master + 1 "sync" standby (near) >>> When the master goes down, something like a clusterware detects that >>> failure, and brings the standby online. Since we can ensure that the >>> standby has all the committed transactions, failover doesn't cause >>> any data loss. >> >> How do you propose to guarantee that? ISTM that you have to either >> commit locally first, or send the commit to the remote first. Either >> way, the two events won't occur exactly simultaneously. > > Letting the transaction wait until the standby has received / flushed / > replayed the WAL before it returns a "success" indicator to a client > would guarantee that. This ensures that all transactions which a client > knows as committed exist in the memory or disk of the standby. So we > would be able to see those transactions from new master after failover. There could still be additional transactions that the original master has committed locally but were not acked to the client. I guess you'd just work around that by taking a new base backup from the new master. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Thu, May 27, 2010 at 8:30 PM, Simon Riggs wrote: > There already is a first patch to the community that implements quorum > commit, just not by you. Yeah, AFAIK, that patch includes also per-standby setting. > If you have a better way, describe it in detail and in full now, with > reference to each of the use cases you mentioned, so that people get a > chance to give their opinions on your design. Then we can let the > community decide whether or not that second way is actually better. We > may not need a second patch. See http://archives.postgresql.org/pgsql-hackers/2010-05/msg01407.php But I think that we should focus on "per-standby" setting at first. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Thu, May 27, 2010 at 8:30 PM, Simon Riggs wrote: > Why does it? I just explained a design where that wasn't required. Hmm.. my expression might have been ambiguous. Walreceiver still needs to wait for WAL flush by walwriter *before* sending the ACK to the master, in #3 case. Because, in #3, the master has to wait until the standby has flushed the WAL. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Thu, May 27, 2010 at 8:28 PM, Robert Haas wrote: > On Thu, May 27, 2010 at 3:13 AM, Fujii Masao wrote: >> (1) most standard case: 1 master + 1 "sync" standby (near) >> When the master goes down, something like a clusterware detects that >> failure, and brings the standby online. Since we can ensure that the >> standby has all the committed transactions, failover doesn't cause >> any data loss. > > How do you propose to guarantee that? ISTM that you have to either > commit locally first, or send the commit to the remote first. Either > way, the two events won't occur exactly simultaneously. Letting the transaction wait until the standby has received / flushed / replayed the WAL before it returns a "success" indicator to a client would guarantee that. This ensures that all transactions which a client knows as committed exist in the memory or disk of the standby. So we would be able to see those transactions from new master after failover. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Thu, 2010-05-27 at 19:50 +0900, Fujii Masao wrote: > For now, I agree that we support a quorum commit feature for 9.1 or later. > But I don't think that it's simpler, more intuitive and easier-to-understand > than per-standby setting. So I think that we should include the per-standby > setting in the first patch. There already is a first patch to the community that implements quorum commit, just not by you. If you have a better way, describe it in detail and in full now, with reference to each of the use cases you mentioned, so that people get a chance to give their opinions on your design. Then we can let the community decide whether or not that second way is actually better. We may not need a second patch. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Thu, 2010-05-27 at 20:13 +0900, Fujii Masao wrote: > On Thu, May 27, 2010 at 7:33 PM, Simon Riggs wrote: > > On Thu, 2010-05-27 at 19:21 +0900, Fujii Masao wrote: > >> On Thu, May 27, 2010 at 6:30 PM, Simon Riggs wrote: > > > >> > Would it be easier to have > >> > this happen in a second pair of processes WALSynchroniser (on primary) > >> > and WAL Acknowledger (on standby). WALAcknowledger would send back a > >> > stream of ack messages with latest xlog positions. WALSynchroniser would > >> > receive these messages and wake up sleeping backends. If we did that > >> > then there'd be almost no change at all to existing code, just > >> > additional code and processes for the sync case. Code would be separate > >> > and there would be no performance concerns either. > >> > >> No, this seems to be bad idea. We should not establish extra connection > >> between servers. That would be a source of trouble. > > > > What kind of trouble? You think using an extra connection would cause > > problems; why? > > Because the number of connection failure cases doubles. Likewise, the number > of process failure cases would double. Not really. The users wait for just the synchroniser to return not for two things. Looks to me that other processes are independent of each other. Very simple. > >> > If you do choose to make #3 important, then I'd say you need to work out > >> > how to make WALWriter active as well, so it can perform regular fsyncs, > >> > rather than having WALReceiver wait across that I/O. > >> > >> Yeah, this might be an option for optimization though I'm not sure how > >> it has good effect. > > > > As I said, WALreceiver would not need to wait across fsync... > > Right, but walreceiver still needs to wait for WAL flush by walwriter. Why does it? I just explained a design where that wasn't required. > If currently WAL flush is the dominant workload for walreceiver, > only leaving it to walwriter might not have so good effect. I'm not sure > whether. If we're not sure, we could check before agreeing a design. WAL flush will be costly unless you have huge disk cache. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Thu, May 27, 2010 at 3:13 AM, Fujii Masao wrote: > (1) most standard case: 1 master + 1 "sync" standby (near) > When the master goes down, something like a clusterware detects that > failure, and brings the standby online. Since we can ensure that the > standby has all the committed transactions, failover doesn't cause > any data loss. How do you propose to guarantee that? ISTM that you have to either commit locally first, or send the commit to the remote first. Either way, the two events won't occur exactly simultaneously. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
Simon Riggs writes: > Seems strange. If you have 2 standbys and you say you would like node1 > to be the preferred candidate, then you load it so heavily that a remote > server with by-definition much larger network delay responds first, then > I say your preference was wrong. There's a communication mismatch here I think. The problem with the dynamic aspect of the system is that the admin wants to plan ahead and choose in advance the failover server. Other than that I much prefer the automatic and dynamic quorum idea. > If you, Jan and Yeb wish to completely exclude standbys from being part > of any quorum, then I guess we need to have per-standby settings to > allow that to be defined. I'm in favour of giving people options. That > needn't be a mandatory per-standby setting, just a non-default option, > so that we can reduce the complexity of configuration for common > cases. +1 > Maximum Performance => quorum = 0 > Maximum Availability => quorum = 1, timeout_action = commit > Maximum Protection => quorum = 1, timeout_action = shutdown +1 Being able to say that a given server has not been granted to participate into the vote allowing to reach the global durability quorum will allow for choosing the failover candidates. Now you're able to have this reporting server and know for sure that your sync replicated transactions are not waiting for it. To summarize, the current "per-transaction approach" would be : - transaction level replication synchronous behaviour - proxy/cascading in core - quorum setup for deciding any commit is safe - any server can be excluded from the sync quorum - timeout can still raises exception or ignore (commit)? This last point seems to need some more discussion, or I didn't understand well the current positions and proposals. Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Thu, May 27, 2010 at 7:33 PM, Simon Riggs wrote: > On Thu, 2010-05-27 at 19:21 +0900, Fujii Masao wrote: >> On Thu, May 27, 2010 at 6:30 PM, Simon Riggs wrote: > >> > Would it be easier to have >> > this happen in a second pair of processes WALSynchroniser (on primary) >> > and WAL Acknowledger (on standby). WALAcknowledger would send back a >> > stream of ack messages with latest xlog positions. WALSynchroniser would >> > receive these messages and wake up sleeping backends. If we did that >> > then there'd be almost no change at all to existing code, just >> > additional code and processes for the sync case. Code would be separate >> > and there would be no performance concerns either. >> >> No, this seems to be bad idea. We should not establish extra connection >> between servers. That would be a source of trouble. > > What kind of trouble? You think using an extra connection would cause > problems; why? Because the number of connection failure cases doubles. Likewise, the number of process failure cases would double. >> > If you do choose to make #3 important, then I'd say you need to work out >> > how to make WALWriter active as well, so it can perform regular fsyncs, >> > rather than having WALReceiver wait across that I/O. >> >> Yeah, this might be an option for optimization though I'm not sure how >> it has good effect. > > As I said, WALreceiver would not need to wait across fsync... Right, but walreceiver still needs to wait for WAL flush by walwriter. If currently WAL flush is the dominant workload for walreceiver, only leaving it to walwriter might not have so good effect. I'm not sure whether. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Thu, May 27, 2010 at 7:21 PM, Simon Riggs wrote: > Seems strange. If you have 2 standbys and you say you would like node1 > to be the preferred candidate, then you load it so heavily that a remote > server with by-definition much larger network delay responds first, then > I say your preference was wrong. The above situation is caused by the > DBA and the DBA can solve it also - if the preference is to keep a > "preferred" server then that server would need to be lightly loaded so > it could respond sensibly. No. Even if the load is very low in the "preferred" server, there is *no* guarantee that it responds first. Per-standby setting can give such a guarantee, i.e., we can specify #2, #3 or #4 in the "preferred" server and #1 in the other. > This is the same thing as having an optimizer pick the best path and > then the user saying "no dumb-ass, use the index I tell you" even though > it is slower. If you really don't want to know the fastest way, then I > personally will agree you can have that, as is my view (now) on the > optimizer issue also - sometimes the admin does know best. I think that choice of wrong master causes more serious situation than that of wrong plan. >> Also the administrator generally doesn't >> put the remote standby under the control of a clusterware like heartbeat. >> In this case, the remote standby will never be the candidate for failover. >> But quorum commit cannot cover this simple case. > > If you, Jan and Yeb wish to completely exclude standbys from being part > of any quorum, then I guess we need to have per-standby settings to > allow that to be defined. I'm in favour of giving people options. That > needn't be a mandatory per-standby setting, just a non-default option, > so that we can reduce the complexity of configuration for common cases. > If we're looking for simplest-implementation-first that isn't it. For now, I agree that we support a quorum commit feature for 9.1 or later. But I don't think that it's simpler, more intuitive and easier-to-understand than per-standby setting. So I think that we should include the per-standby setting in the first patch. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Thu, 2010-05-27 at 19:21 +0900, Fujii Masao wrote: > On Thu, May 27, 2010 at 6:30 PM, Simon Riggs wrote: > > Would it be easier to have > > this happen in a second pair of processes WALSynchroniser (on primary) > > and WAL Acknowledger (on standby). WALAcknowledger would send back a > > stream of ack messages with latest xlog positions. WALSynchroniser would > > receive these messages and wake up sleeping backends. If we did that > > then there'd be almost no change at all to existing code, just > > additional code and processes for the sync case. Code would be separate > > and there would be no performance concerns either. > > No, this seems to be bad idea. We should not establish extra connection > between servers. That would be a source of trouble. What kind of trouble? You think using an extra connection would cause problems; why? I've explained it would greatly simplify the code to do it that way and improve performance. Those sound like good things, not problems. > > If you do choose to make #3 important, then I'd say you need to work out > > how to make WALWriter active as well, so it can perform regular fsyncs, > > rather than having WALReceiver wait across that I/O. > > Yeah, this might be an option for optimization though I'm not sure how > it has good effect. As I said, WALreceiver would not need to wait across fsync... -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Thu, May 27, 2010 at 6:30 PM, Simon Riggs wrote: > Who sends the ack message? walreceiver > Who receives it? walsender > Would it be easier to have > this happen in a second pair of processes WALSynchroniser (on primary) > and WAL Acknowledger (on standby). WALAcknowledger would send back a > stream of ack messages with latest xlog positions. WALSynchroniser would > receive these messages and wake up sleeping backends. If we did that > then there'd be almost no change at all to existing code, just > additional code and processes for the sync case. Code would be separate > and there would be no performance concerns either. No, this seems to be bad idea. We should not establish extra connection between servers. That would be a source of trouble. > If you do choose to make #3 important, then I'd say you need to work out > how to make WALWriter active as well, so it can perform regular fsyncs, > rather than having WALReceiver wait across that I/O. Yeah, this might be an option for optimization though I'm not sure how it has good effect. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Thu, 2010-05-27 at 16:13 +0900, Fujii Masao wrote: > On Wed, May 26, 2010 at 10:37 PM, Simon Riggs wrote: > > If the remote server responded first, then that proves it is a better > > candidate for failover than the one you think of as near. If the two > > standbys vary over time then you have network problems that will > > directly affect the performance on the master; synch_rep = N would > > respond better to any such problems. > > No. The remote standby might respond first temporarily though it's almost > behind the near one. The read-only queries or incrementally updated > backup operation might cause a bursty disk write, and delay the ACK from > the standby. The lock contention between read-only queries and recovery > would delay the ACK. So the standby which responds first is not always > the best candidate for failover. Seems strange. If you have 2 standbys and you say you would like node1 to be the preferred candidate, then you load it so heavily that a remote server with by-definition much larger network delay responds first, then I say your preference was wrong. The above situation is caused by the DBA and the DBA can solve it also - if the preference is to keep a "preferred" server then that server would need to be lightly loaded so it could respond sensibly. This is the same thing as having an optimizer pick the best path and then the user saying "no dumb-ass, use the index I tell you" even though it is slower. If you really don't want to know the fastest way, then I personally will agree you can have that, as is my view (now) on the optimizer issue also - sometimes the admin does know best. > Also the administrator generally doesn't > put the remote standby under the control of a clusterware like heartbeat. > In this case, the remote standby will never be the candidate for failover. > But quorum commit cannot cover this simple case. If you, Jan and Yeb wish to completely exclude standbys from being part of any quorum, then I guess we need to have per-standby settings to allow that to be defined. I'm in favour of giving people options. That needn't be a mandatory per-standby setting, just a non-default option, so that we can reduce the complexity of configuration for common cases. If we're looking for simplest-implementation-first that isn't it. Currently, Oracle provides these settings, which correspond to Maximum Performance => quorum = 0 Maximum Availability => quorum = 1, timeout_action = commit Maximum Protection => quorum = 1, timeout_action = shutdown So Oracle already supports the quorum case... Oracle doesn't provide i) any capability to have quorum > 1 ii) any capability to include an async node as a sync node, if the quorum cannot be reached with servers marked "sync", or in the situation where because of mis-use/mis-configuration the sync servers are actually slower. iii) ability to wait for apply iv) ability to specify wait mode at transaction level all of those are desirable in some cases and easily possible by specifying things in the way I've suggested. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Thu, 2010-05-27 at 16:13 +0900, Fujii Masao wrote: > On Wed, May 26, 2010 at 10:37 PM, Simon Riggs wrote: > > Please explain what will happen when the near server is unavailable, > > with per-standby settings. Please also explain what will happen if we > > choose to have 4 or 5 servers to maintain performance in case of the > > near server going down. How will we specify the failure modes? > > I'll try to explain that. We've been discussing parameters and how we would define what we want to happen in various scenarios. You've not explained what parameters you would use, how and where they would be set, so we aren't yet any closer to understanding what it is your proposing. Please explain how your proposal will work. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Thu, 2010-05-27 at 16:35 +0900, Fujii Masao wrote: > On Thu, May 27, 2010 at 3:21 PM, Simon Riggs wrote: > > On Thu, 2010-05-27 at 11:28 +0900, Fujii Masao wrote: > >> On Wed, May 26, 2010 at 10:20 PM, Simon Riggs > >> wrote: > >> > On Wed, 2010-05-26 at 18:52 +0900, Fujii Masao wrote: > >> > > >> >> I guess that dropping the support of #3 doesn't reduce complexity > >> >> since the code of #3 is almost the same as that of #2. Like > >> >> walreceiver sends the ACK after receiving the WAL in #2 case, it has > >> >> only to do the same thing after the WAL flush. > >> > > >> > Hmm, well the code for #3 is similar also to the code for #4. So if you > >> > do #2, its easy to do #2, #3 and #4 together. > >> > >> No. #4 requires the way of prompt communication between walreceiver and > >> startup process, but #2 and #3 not. That is, in #4, walreceiver has to > >> wake the startup process up as soon as it has flushed WAL. OTOH, the > >> startup process has to wake walreceiver up as soon as it has replayed > >> WAL, to request it to send the ACK to the master. In #2 and #3, the > >> prompt communication from walreceiver to startup process, i.e., changing > >> the poll loop in the startup process would also be useful for the data > >> to be visible immediately on the standby. But it's not required. > > > > You need to pass WAL promptly on primary from backend to WALSender. > > Whatever mechanism you use can also be reused symmetrically on standby > > to provide #4. So not a problem. > > I cannot be so optimistic since the situation differs from one process > to another. This spurs some architectural thinking: I think we need to disconnect the idea of waiting in any of the components. Anytime we ask WALSender or WALReceiver to wait for acknowledgement we will be reducing throughput. So we should assume that they will continue to work as quickly as possible. The acknowledgement from standby can contain the latest xlog location of WAL received, WAL written to disk and WAL applied, all by reading values from shared memory. It's all the same, whether we send back 2 or 3 xlog locations in the ack message. Who sends the ack message? Who receives it? Would it be easier to have this happen in a second pair of processes WALSynchroniser (on primary) and WAL Acknowledger (on standby). WALAcknowledger would send back a stream of ack messages with latest xlog positions. WALSynchroniser would receive these messages and wake up sleeping backends. If we did that then there'd be almost no change at all to existing code, just additional code and processes for the sync case. Code would be separate and there would be no performance concerns either. Backends can then choose to wait until the xlog location they wish has been achieved which might be in the next acknowledgement message or in a subsequent one. That also ensures that the logic for this is completely on the master and the standby doesn't act differently, apart from needing to start a WALAcknowledger process if sync rep is requested. If you do choose to make #3 important, then I'd say you need to work out how to make WALWriter active as well, so it can perform regular fsyncs, rather than having WALReceiver wait across that I/O. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Thu, 2010-05-27 at 10:09 +0300, Heikki Linnakangas wrote: > No, not necessarily. As I said above, you might just want a guarantee > that *if* you query the standby, you get up-to-date results. Of course. COMMIT was already one of the options, so this comment was already understood. What we are discussing is whether additional options exist and/or are desirable. We should not be forcing everybody to COMMIT whether or not it is robust. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
Heikki Linnakangas writes: > On 26/05/10 23:31, Dimitri Fontaine wrote: >> So if you want simplicity to admin, effective data availability and >> precise control over the global setup, I say go for: >> a. transaction level control of the replication level >> b. cascading support >> c. quorum with timeout >> d. choice of commit or rollback at timeout >> >> Then give me a setup example that you can't express fully. > > One master, one synchronous standby on another continent for HA purposes, > and one asynchronous reporting server in the same rack as the master. You > don't want to set up the reporting server as a cascaded slave of the standby > on the other continent, because that would double the bandwidth required, > but you also don't want the master to wait for the reporting server. > > The possibilities are endless... Your proposal above covers a pretty good > set of scenarios, but it's by no means complete. If we try to solve > everything the configuration will need to be written in a Turing-complete > Replication Description Language. We'll have to pick a useful, > easy-to-understand subset that covers the common scenarios. To handle the > more exotic scenarios, you can write a proxy that sits in front of the > master, and implements whatever rules you wish, with the rules written > in C. Agreed on the Turing-completeness side of those things. My current thinking is that the proxy I want might simply be a PostgreSQL instance with cascading support. In your example that would give us: Remote Standby, HA Master -- Proxy -< Local Standby, Reporting So what I think we have here is a pretty good trade-off in terms of what you can do with some simple setup knobs. What's left there is that with the quorum idea, you're not sure if the one server that's synced is the remote or local standby, in this example. Several ideas are floating around (votes, mixed per-standby and per-transaction settings). Maybe we could have the standby be able to say it's not interesting into participating into the quorum, that is, it's an async replica, full stop. In your example we'd set the local reporting standby as a non-voting member of the replication setting, the proxy and the master would have a quorum of 1, and the remote HA standby would vote. I don't think the idea of having any number of voting coupons other than 0 or 1 on any server will help us the least. I do think that your proxy idea is a great one and should be in core. By the way, the cascading/proxy instance could be set without Hot Standby, if you don't like to be able to monitor it via a libpq connection and some queries. > BTW, I think we're going to need a separate config file for listing the > standbys anyway. There you can write per-server rules and options, but > explicitly knowing about all the standbys also allows the master to recycle > WAL as soon as it has been streamed to all the registered > standbys. Currently we just keep wal_keep_segments files around, just in > case there's a standby out there that needs them. I much prefer that each server in the set publish what it wants. It only connects to 1 given provider. Then we've been talking about this exact same retention problem for queueing solutions, with Jan, Marko and Jim. The idea we came up with is a watermarking solution (which already exists in Skytools 3, in its coarse-grain version). The first approach is to have all slave give back to its local master/provider/origin the last replayed WAL/LSN, once in a while. You derive from that a global watermark and drop WAL files depending on it. You now have two problems: no more space and why keeping that many files on the master anyway, maybe some slave could be set up for retention instead? To solve that it's possible for each server to be setup with a restricted set of servers they're deriving their watermark from. That's when you need per-server options and an explicit list of all the standbys whatever their level in the cascading tree. That means explicit maintenance of the entire replication topology. I don't think we need to solve that already. I think we need to provide an option on each member of the replication tree to either PANIC or lose WALs in case they're running out of space when trying to follow the watermark. It's crude but already allows to have a standby set to maintain the common archive and have the master drop the WAL files as soon as possible (respecting wal_keep_segments). In our case, if a WAL file is no more available from any active server we still have the option to fetch it from the archives... Regards, -- Dimitri Fontaine PostgreSQL DBA, Architecte -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Thu, May 27, 2010 at 3:21 PM, Simon Riggs wrote: > On Thu, 2010-05-27 at 11:28 +0900, Fujii Masao wrote: >> On Wed, May 26, 2010 at 10:20 PM, Simon Riggs wrote: >> > On Wed, 2010-05-26 at 18:52 +0900, Fujii Masao wrote: >> > >> >> I guess that dropping the support of #3 doesn't reduce complexity >> >> since the code of #3 is almost the same as that of #2. Like >> >> walreceiver sends the ACK after receiving the WAL in #2 case, it has >> >> only to do the same thing after the WAL flush. >> > >> > Hmm, well the code for #3 is similar also to the code for #4. So if you >> > do #2, its easy to do #2, #3 and #4 together. >> >> No. #4 requires the way of prompt communication between walreceiver and >> startup process, but #2 and #3 not. That is, in #4, walreceiver has to >> wake the startup process up as soon as it has flushed WAL. OTOH, the >> startup process has to wake walreceiver up as soon as it has replayed >> WAL, to request it to send the ACK to the master. In #2 and #3, the >> prompt communication from walreceiver to startup process, i.e., changing >> the poll loop in the startup process would also be useful for the data >> to be visible immediately on the standby. But it's not required. > > You need to pass WAL promptly on primary from backend to WALSender. > Whatever mechanism you use can also be reused symmetrically on standby > to provide #4. So not a problem. I cannot be so optimistic since the situation differs from one process to another. >> > The comment is about whether having #3 makes sense from a user interface >> > perspective. It's easy to add options, but they must have useful >> > meaning. >> >> #3 would be useful for people wanting further robustness. In #2, >> when simultaneous power failure on the master and the standby, >> and concurrent disk crash on the master happen, transaction whose >> "success" indicator has been returned to a client might be lost. >> #3 can avoid such a critical situation. This is one of reasons that >> DRBD supports "Protocol C", I think. > > Which few people use it, or if they do its because DRBD didn't > originally support multiple standbys. Not worth emulating IMHO. If so, #3 would be useful for people who don't afford to buy more than one standby servers, too :) Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Wed, May 26, 2010 at 10:37 PM, Simon Riggs wrote: > If the remote server responded first, then that proves it is a better > candidate for failover than the one you think of as near. If the two > standbys vary over time then you have network problems that will > directly affect the performance on the master; synch_rep = N would > respond better to any such problems. No. The remote standby might respond first temporarily though it's almost behind the near one. The read-only queries or incrementally updated backup operation might cause a bursty disk write, and delay the ACK from the standby. The lock contention between read-only queries and recovery would delay the ACK. So the standby which responds first is not always the best candidate for failover. Also the administrator generally doesn't put the remote standby under the control of a clusterware like heartbeat. In this case, the remote standby will never be the candidate for failover. But quorum commit cannot cover this simple case. >> OTOH, "synchronous_replication=2" degrades the >> performance on the master very much. > > Yes, but only because you have only one near standby. It would clearly > to be foolish to make this setting without 2+ near standbys. We would > then have 4 or more servers; how do we specify everything for that > config?? If you always want to use the near standby as the candidate for failover by using quorum commit in the above simple case, you would need to choose such a foolish setting. Otherwise, unfortunately you might have to failover to the remote standby not under the control of a clusterware. >> "synchronous_replication" approach >> doesn't seem to cover the typical use case. > > You described the failure modes for the quorum proposal, but avoided > describing the failure modes for the "per-standby" proposal. > > Please explain what will happen when the near server is unavailable, > with per-standby settings. Please also explain what will happen if we > choose to have 4 or 5 servers to maintain performance in case of the > near server going down. How will we specify the failure modes? I'll try to explain that. (1) most standard case: 1 master + 1 "sync" standby (near) When the master goes down, something like a clusterware detects that failure, and brings the standby online. Since we can ensure that the standby has all the committed transactions, failover doesn't cause any data loss. When the standby goes down or network outage happens, walsender detects that failure via the replication timeout, keepalive or error return from the system calls. Then walsender does something according to the specified reaction (GUC) to the failure of the standby, e.g., walsender wakes the transaction commit up from the wait-for-ACK, and exits. Then the master runs standalone. (2) 1 master + 1 "sync" standby (near) + 1 "async" standby (remote) When the master goes down, something like a clusterware brings the "sync" standby in the near location online. The administrator would need to take a fresh base backup of the new master, load it on the remote standby, change the primary_conninfo, and restart the remote standby. When one of standbys goes down, walsender does the same thing described in (1). Until the failed standby has restarted, the master runs together with another standby. In (1) and (2), after some failure happens, there would be only one server which is guaranteed to have all the committed transactions. When it also goes down, the database service stops. If you want to avoid this fragile situation, you would need to add one more "sync" standby in the near site. (3) 1 master + 2 "sync" standbys (near) + 1 "async" standby (remote) When the master goes down, something like a clusterware brings the one of "sync" standbys online by using some selection algorithm. The administrator would need to take a fresh base backup of the new master, load it on both remaining standbys, change the primary_conninfo, and restart them. When one of standbys goes down, walsender does the same thing described in (1). Until the failed standby has restarted, the master runs together with two standbys. At least one standby is guaranteed to be sync with the master. Is this explanation enough? >> Also, when "synchronous_replication=1" and one of synchronous standbys >> goes down, how should the surviving standby catch up with the master? >> Such standby might be too far behind the master. The transaction commit >> should wait for the ACK from the lagging standby immediately even if >> there might be large gap? If yes, "synch_rep_timeout" would screw up >> the replication easily. > > That depends upon whether we send the ACK at point #2, #3 or #4. It > would only cause a problem if you waited until #4. Yeah, the problem happens. If we implement quorum commit, we need to design how the surviving standby catches up with the master. Regards,
Re: [HACKERS] Synchronization levels in SR
On 27/05/10 09:51, Simon Riggs wrote: On Thu, 2010-05-27 at 02:18 +0300, Heikki Linnakangas wrote: In practice, hard synchronous "don't return ever until the commit hits the standby" behavior is rarely what admins actually want, because it's disastrous from an availability point of view. More likely, admins want "wait for ack from standby, unless it's not responding, in which case to hell with redundancy and just act like a single server". It makes sense if you just want to make sure that the standby doesn't return stale results when it's working properly, and you're not worried about durability but I'm not sure it's very sound otherwise. Which is also crazy. If you're using synch rep its because you care deeply about durability. No, not necessarily. As I said above, you might just want a guarantee that *if* you query the standby, you get up-to-date results. But if the standby is down for any reason, you don't care about it. That's a very sensible mode of operation, for example if you're offloading reads to the standby with something like pgpool. In fact I have the feeling that that's the most common use case for synchronous replication, not a deep concern of durability. I agree that don't-return-ever isn't something anyone will want. What we need is a "COMMIT with ERROR" message! Hmm, perhaps we could emit a warning with the commit. I'm not sure what an application could do with it, though. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Thu, 2010-05-27 at 02:18 +0300, Heikki Linnakangas wrote: > On 27/05/10 01:23, Simon Riggs wrote: > > On Thu, 2010-05-27 at 00:21 +0300, Heikki Linnakangas wrote: > >> On 26/05/10 23:31, Dimitri Fontaine wrote: > >>>d. choice of commit or rollback at timeout > >> > >> Rollback is not an option. There is no going back after the commit > >> record has been flushed to disk or sent to a standby. > > > > There's definitely no going back after the xid has been removed from > > procarray because other transactions will then depend upon the final > > state. Currently we PANIC if we abort after we've marked clog, though > > that happens after XLogFlush(), which is where we're planning to wait > > for synch rep. If we abort after having written a commit record to disk > > we can still successfully generate an abort record as well. (Luckily, I > > note HS does actually cope with that. Phew!) > > > > So actually, an abort is a reasonable possibility, though I know it > > doesn't sound like it could be at first thought. > Hmm, that's an interesting thought. Interesting, as in crazy ;-). :-) It's a surprising thought for me also. > I don't understand how HS could handle that. As soon as it sees the > commit record, the transaction becomes visible to readers. I meant not-barf completely. > >> The choice is to either commit anyway after the timeout, or wait forever. > > > > Hmm, wait forever. What happens if we try to shutdown fast while there > > is a transaction that is waiting forever? Is that then a commit, even > > though it never made it to the standby? How would we know it was safe to > > switchover or not? Hmm. > > Refuse to shut down until the standby acknowledges the commit. That's > the only way to be sure.. > > In practice, hard synchronous "don't return ever until the commit hits > the standby" behavior is rarely what admins actually want, because it's > disastrous from an availability point of view. More likely, admins want > "wait for ack from standby, unless it's not responding, in which case to > hell with redundancy and just act like a single server". It makes sense > if you just want to make sure that the standby doesn't return stale > results when it's working properly, and you're not worried about > durability but I'm not sure it's very sound otherwise. Which is also crazy. If you're using synch rep its because you care deeply about durability. Some people wish to treat the COMMIT as a guarantee, not just a shrug. I agree that don't-return-ever isn't something anyone will want. What we need is a "COMMIT with ERROR" message! Note that Oracle gives the options of COMMIT | SHUTDOWN at this point. Shutdown is an implicit abort for the writing transaction... At this point the primary thinks standby is no longer available. If we have a split brain situation then we should be assuming we will STONITH and shutdown the primary anyway. If we have more than one standby we can stay up and probably shouldn't be sending an abort after a commit. The trouble is *every* option is crazy from some perspective, so we must consider them all, to see whether they are practical or impractical. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Thu, 2010-05-27 at 11:28 +0900, Fujii Masao wrote: > On Wed, May 26, 2010 at 10:20 PM, Simon Riggs wrote: > > On Wed, 2010-05-26 at 18:52 +0900, Fujii Masao wrote: > > > >> I guess that dropping the support of #3 doesn't reduce complexity > >> since the code of #3 is almost the same as that of #2. Like > >> walreceiver sends the ACK after receiving the WAL in #2 case, it has > >> only to do the same thing after the WAL flush. > > > > Hmm, well the code for #3 is similar also to the code for #4. So if you > > do #2, its easy to do #2, #3 and #4 together. > > No. #4 requires the way of prompt communication between walreceiver and > startup process, but #2 and #3 not. That is, in #4, walreceiver has to > wake the startup process up as soon as it has flushed WAL. OTOH, the > startup process has to wake walreceiver up as soon as it has replayed > WAL, to request it to send the ACK to the master. In #2 and #3, the > prompt communication from walreceiver to startup process, i.e., changing > the poll loop in the startup process would also be useful for the data > to be visible immediately on the standby. But it's not required. You need to pass WAL promptly on primary from backend to WALSender. Whatever mechanism you use can also be reused symmetrically on standby to provide #4. So not a problem. > > The comment is about whether having #3 makes sense from a user interface > > perspective. It's easy to add options, but they must have useful > > meaning. > > #3 would be useful for people wanting further robustness. In #2, > when simultaneous power failure on the master and the standby, > and concurrent disk crash on the master happen, transaction whose > "success" indicator has been returned to a client might be lost. > #3 can avoid such a critical situation. This is one of reasons that > DRBD supports "Protocol C", I think. Which few people use it, or if they do its because DRBD didn't originally support multiple standbys. Not worth emulating IMHO. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Wed, May 26, 2010 at 10:20 PM, Simon Riggs wrote: > On Wed, 2010-05-26 at 18:52 +0900, Fujii Masao wrote: > >> I guess that dropping the support of #3 doesn't reduce complexity >> since the code of #3 is almost the same as that of #2. Like >> walreceiver sends the ACK after receiving the WAL in #2 case, it has >> only to do the same thing after the WAL flush. > > Hmm, well the code for #3 is similar also to the code for #4. So if you > do #2, its easy to do #2, #3 and #4 together. No. #4 requires the way of prompt communication between walreceiver and startup process, but #2 and #3 not. That is, in #4, walreceiver has to wake the startup process up as soon as it has flushed WAL. OTOH, the startup process has to wake walreceiver up as soon as it has replayed WAL, to request it to send the ACK to the master. In #2 and #3, the prompt communication from walreceiver to startup process, i.e., changing the poll loop in the startup process would also be useful for the data to be visible immediately on the standby. But it's not required. > The comment is about whether having #3 makes sense from a user interface > perspective. It's easy to add options, but they must have useful > meaning. #3 would be useful for people wanting further robustness. In #2, when simultaneous power failure on the master and the standby, and concurrent disk crash on the master happen, transaction whose "success" indicator has been returned to a client might be lost. #3 can avoid such a critical situation. This is one of reasons that DRBD supports "Protocol C", I think. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On 27/05/10 01:23, Simon Riggs wrote: On Thu, 2010-05-27 at 00:21 +0300, Heikki Linnakangas wrote: On 26/05/10 23:31, Dimitri Fontaine wrote: d. choice of commit or rollback at timeout Rollback is not an option. There is no going back after the commit record has been flushed to disk or sent to a standby. There's definitely no going back after the xid has been removed from procarray because other transactions will then depend upon the final state. Currently we PANIC if we abort after we've marked clog, though that happens after XLogFlush(), which is where we're planning to wait for synch rep. If we abort after having written a commit record to disk we can still successfully generate an abort record as well. (Luckily, I note HS does actually cope with that. Phew!) So actually, an abort is a reasonable possibility, though I know it doesn't sound like it could be at first thought. Hmm, that's an interesting thought. Interesting, as in crazy ;-). I don't understand how HS could handle that. As soon as it sees the commit record, the transaction becomes visible to readers. The choice is to either commit anyway after the timeout, or wait forever. Hmm, wait forever. What happens if we try to shutdown fast while there is a transaction that is waiting forever? Is that then a commit, even though it never made it to the standby? How would we know it was safe to switchover or not? Hmm. Refuse to shut down until the standby acknowledges the commit. That's the only way to be sure.. In practice, hard synchronous "don't return ever until the commit hits the standby" behavior is rarely what admins actually want, because it's disastrous from an availability point of view. More likely, admins want "wait for ack from standby, unless it's not responding, in which case to hell with redundancy and just act like a single server". It makes sense if you just want to make sure that the standby doesn't return stale results when it's working properly, and you're not worried about durability but I'm not sure it's very sound otherwise. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Thu, 2010-05-27 at 00:21 +0300, Heikki Linnakangas wrote: > On 26/05/10 23:31, Dimitri Fontaine wrote: > > d. choice of commit or rollback at timeout > > Rollback is not an option. There is no going back after the commit > record has been flushed to disk or sent to a standby. There's definitely no going back after the xid has been removed from procarray because other transactions will then depend upon the final state. Currently we PANIC if we abort after we've marked clog, though that happens after XLogFlush(), which is where we're planning to wait for synch rep. If we abort after having written a commit record to disk we can still successfully generate an abort record as well. (Luckily, I note HS does actually cope with that. Phew!) So actually, an abort is a reasonable possibility, though I know it doesn't sound like it could be at first thought. > The choice is to either commit anyway after the timeout, or wait forever. Hmm, wait forever. What happens if we try to shutdown fast while there is a transaction that is waiting forever? Is that then a commit, even though it never made it to the standby? How would we know it was safe to switchover or not? Hmm. Oracle offers options of COMMIT | SHUTDOWN in this case. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Wed, 2010-05-26 at 17:31 -0400, Jan Wieck wrote: > You can do this only with per standby options, by giving each standby a > weight, or a number of votes. Your DEV server would have a weight of > zero, while your production standby's have higher weights, depending on > their importance for your overall infrastructure. As long as majority > means >50% of all votes in the house, you don't have a split brain risk. Yes, you could do that with per-standby options. If you give each standby a weight then the parameter has much less meaning for the user. It doesn't mean number of replicas any more, it means something else with local and changeable meaning. A fractional quorum suffers the same way. What would make some sense would be to have an option for "vote=0|1" so that a standby would never take part in the transaction sync when vote=0. But you still have the problem of specifying rules if insufficient servers with vote=1 are available. The reaction to that is to supply more servers with vote=1, though then you need a way to specify how many servers with vote=1 you care about. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On 26/05/10 23:31, Dimitri Fontaine wrote: So if you want simplicity to admin, effective data availability and precise control over the global setup, I say go for: a. transaction level control of the replication level b. cascading support c. quorum with timeout d. choice of commit or rollback at timeout Then give me a setup example that you can't express fully. One master, one synchronous standby on another continent for HA purposes, and one asynchronous reporting server in the same rack as the master. You don't want to set up the reporting server as a cascaded slave of the standby on the other continent, because that would double the bandwidth required, but you also don't want the master to wait for the reporting server. The possibilities are endless... Your proposal above covers a pretty good set of scenarios, but it's by no means complete. If we try to solve everything the configuration will need to be written in a Turing-complete Replication Description Language. We'll have to pick a useful, easy-to-understand subset that covers the common scenarios. To handle the more exotic scenarios, you can write a proxy that sits in front of the master, and implements whatever rules you wish, with the rules written in C. BTW, I think we're going to need a separate config file for listing the standbys anyway. There you can write per-server rules and options, but explicitly knowing about all the standbys also allows the master to recycle WAL as soon as it has been streamed to all the registered standbys. Currently we just keep wal_keep_segments files around, just in case there's a standby out there that needs them. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On 5/26/2010 12:55 PM, Heikki Linnakangas wrote: On 26/05/10 18:31, Robert Haas wrote: And frankly, I don't think it's possible for quorum commit to reduce the number of parameters. Even if we have that feature available, not everyone will want to use it. And the people who don't will presumably need whatever parameters they would have needed if quorum commit hadn't been available in the first place. Agreed, quorum commit is not a panacea. For example, suppose that you have two servers, master and a standby, and you want transactions to be synchronously committed to both, so that in the event of a meteor striking the master, you don't lose any transactions that have been replied to the client as committed. Now you want to set up a temporary replica of the master at a development server, for testing purposes. If you set quorum to 2, your development server becomes critical infrastructure, which is not what you want. If you set quorum to 1, it also becomes critical infrastructure, because it's possible that a transaction has been replicated to the test server but not the real production standby, and a meteor strikes. Per-standby settings would let you express that, but not OTOH the quorum behavior where you require N out of M to acknowledge the commit before returning to client. You can do this only with per standby options, by giving each standby a weight, or a number of votes. Your DEV server would have a weight of zero, while your production standby's have higher weights, depending on their importance for your overall infrastructure. As long as majority means >50% of all votes in the house, you don't have a split brain risk. Jan -- Anyone who trades liberty for security deserves neither liberty nor security. -- Benjamin Franklin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On 26/05/10 23:31, Dimitri Fontaine wrote: d. choice of commit or rollback at timeout Rollback is not an option. There is no going back after the commit record has been flushed to disk or sent to a standby. The choice is to either commit anyway after the timeout, or wait forever. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
Simon Riggs writes: > On Wed, 2010-05-26 at 19:55 +0300, Heikki Linnakangas wrote: >> Now you want to set up a temporary replica of the master at a >> development server, for testing purposes. If you set quorum to 2, your >> development server becomes critical infrastructure, which is not what >> you want. > > That's a good argument for standby relays. Well it seems to me we can have the best of both worlds as soon as we have cascading support. Even in the test server example, this one would be a slave of the main slave, not counted into the quorum on the master. Now that's the quorum on the slave that would be deciding on the availability of the test server. Set it down to 0 and your test server has no impact on the production environment. In the example of one master and 4 slaves in 2 different locations, you'll have a quorum of 2 on the master, which will know about 2 slaves only. And each of them will have 1 slave, with a quorum to set to 0 or 1 depending on what you want to achieve. So if you want simplicity to admin, effective data availability and precise control over the global setup, I say go for: a. transaction level control of the replication level b. cascading support c. quorum with timeout d. choice of commit or rollback at timeout Then give me a setup example that you can't express fully. As far as the options to control the whole thing are concerned, I think that the cascading support does not add any. So that's 3 GUCs. Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Wed, 2010-05-26 at 15:37 -0400, Robert Haas wrote: > On Wed, May 26, 2010 at 3:13 PM, Simon Riggs wrote: > >> I don't really understand this comment. I have said, and I believe, > >> that a system without quorum commit is simpler than one with quorum > >> commit. I'd debate the point with you but I find the point so > >> self-evident that I don't even know where to begin arguing it. > > > >> It's simply an opinion, which I believe to > >> be based on solid technical reasoning, but which I might change my > >> mind about if someone convinces me that I'm looking at the problem the > >> wrong way. > > > > You're saying you have solid technical reasons, but they are so > > self-evident that you can't even begin to argue them. Why are you so > > sure your reasons are solid?? Regrettably, I say this doesn't make any > > sense, however much you write. > > Yeah, especially when you juxtapose two different parts of my email > that were talking about different things. /me rings bell and orders the fighters to their respective corners. > > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise Postgres Company > -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Wed, May 26, 2010 at 3:13 PM, Simon Riggs wrote: >> I don't really understand this comment. I have said, and I believe, >> that a system without quorum commit is simpler than one with quorum >> commit. I'd debate the point with you but I find the point so >> self-evident that I don't even know where to begin arguing it. > >> It's simply an opinion, which I believe to >> be based on solid technical reasoning, but which I might change my >> mind about if someone convinces me that I'm looking at the problem the >> wrong way. > > You're saying you have solid technical reasons, but they are so > self-evident that you can't even begin to argue them. Why are you so > sure your reasons are solid?? Regrettably, I say this doesn't make any > sense, however much you write. Yeah, especially when you juxtapose two different parts of my email that were talking about different things. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Wed, 2010-05-26 at 14:30 -0400, Robert Haas wrote: > On Wed, May 26, 2010 at 1:26 PM, Simon Riggs wrote: > > On Wed, 2010-05-26 at 11:31 -0400, Robert Haas wrote: > >> > Your reply has again avoided the subject of how we would handle failure > >> > modes with per-standby settings. That is important. > >> > >> I don't think anyone is avoiding that, we just haven't discussed it. > > > > You haven't discussed it, but even before you do, you know its better. > > Not very compelling perspective... > > I don't really understand this comment. I have said, and I believe, > that a system without quorum commit is simpler than one with quorum > commit. I'd debate the point with you but I find the point so > self-evident that I don't even know where to begin arguing it. > It's simply an opinion, which I believe to > be based on solid technical reasoning, but which I might change my > mind about if someone convinces me that I'm looking at the problem the > wrong way. You're saying you have solid technical reasons, but they are so self-evident that you can't even begin to argue them. Why are you so sure your reasons are solid?? Regrettably, I say this doesn't make any sense, however much you write. The decision may already have been made in your eyes, but the community still has options as to how to proceed, whether or not Masao has already written this. Zoltan has already presented a patch that follows my proposal, so there are alternate valid paths which we can decide between. It's not a matter of opinion as to which is easier to code cos its already done; you can run the patch and see. (No comment on other parts of that patch). The alternative is an approach that hasn't even been presented itself fully on list, with many unanswered questions. I've thought about this myself and discussed my reasons on list for the past two years. If you can read all I've presented to the community and come up with a better way, great, we'll all be happy. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Wed, May 26, 2010 at 1:26 PM, Simon Riggs wrote: > On Wed, 2010-05-26 at 11:31 -0400, Robert Haas wrote: >> > Your reply has again avoided the subject of how we would handle failure >> > modes with per-standby settings. That is important. >> >> I don't think anyone is avoiding that, we just haven't discussed it. > > You haven't discussed it, but even before you do, you know its better. > Not very compelling perspective... I don't really understand this comment. I have said, and I believe, that a system without quorum commit is simpler than one with quorum commit. I'd debate the point with you but I find the point so self-evident that I don't even know where to begin arguing it. I am not saying, and I have not said, that we shouldn't have quorum commit. I am saying that it is not something that we need to add as part of an initial sync rep patch, because we can instead add it in a follow-on patch. As far as I can tell, we are not trying to decide between two competing approaches and therefore do not need to decide which one is better. Everything you are proposing sounds useful and valuable. I am not sure whether it handles all of the use cases that folks might have. For example, Heikki mentioned the case upthread of wanting to wait for a commit ACK from one of two servers in the data center A and one of two servers in data center B, rather than just two out of four servers total. So, we might need to think a little bit about whether we want to handle those kinds of cases and what sort of infrastructure we would need to support it. But certainly I think quorum commit sounds like a good feature and I hope it will be included in 9.1, or if not 9.1, then some future version. What I don't agree with is that it needs to be part of the initial implementation of sync rep. If there's a case for doing that, I don't believe it's been made on this thread. At any rate, the fact that I don't see it as a sine qua non for sync rep is neither obstructionism nor an ad hominem attack. It's simply an opinion, which I believe to be based on solid technical reasoning, but which I might change my mind about if someone convinces me that I'm looking at the problem the wrong way. That would an involve someone making an argument of the following form: "If we don't implement quorum commit in the very first implementation of sync rep, then it will be hard to add later because X." So far no one has done that. You have made the similar argument "If we do implement quorum commit in the very first version of sync rep, it will save implementation work elsewhere" - but I don't think that's true and I have explained why. >> The thing is, I don't think quorum commit actually does anything to >> address that problem. If I have a master and a standby configured for >> sync rep and the standby goes down, we have to decide what impact that >> has on the master. If I have a master and two standbys configured for >> sync rep with quorum commit such that I only need an ack from one of >> them, and they both go down, we still have to decide what impact that >> has on the master. > > That's already been discussed, and AFAIK Masao and I already agreed on > how that would be handled in the quorum commit case. I can't find that in the thread. Anyway, again, you're going to probably want the same options there that you will in the "master with one standby" case, which I personally think is going to be a LOT more common than any other configuration. > [configuration example] > The Oracle way doesn't allow you to specify that if near1 and near2 are > down then we should continue to SYNC via remote, nor does it allow you > to specify things from user perspective or at transaction level. > > You don't need to do it that way, for sure. But we do need to say what > way you would pick, rather than just arguing against me before you've > even discussed it here or off-list. Well, again, I am not arguing and have not argued that we shouldn't do it, just that we shouldn't do it UNTIL we get the basic stuff working. On the substance of the design, the Oracle way doesn't look that bad in terms of syntax (I suspect we'll end up with some of the same knobs), but certainly I agree that it would be nice to do some of the things they can't which you have detailed here. I just don't want us to bite off more than we can chew. Then we might end up with nothing, which would suck. >> I agree we need to talk about, but I don't agree >> that putting in quorum commit will remove the need to design that >> case. > > Yes, you need to design for that case. It's not a magic wand. > > All I've said is that covering the common cases is easier and more > flexible by choosing transaction-centric style of parameters, and it > also allows user settable behaviour. One of the ideas you proposed upthread, in terms of transaction-centric behavior, is having an individual transaction be able to ask for a weaker integrity guarantee than whatever the default is. I
Re: [HACKERS] Synchronization levels in SR
On 26/05/10 20:40, Simon Riggs wrote: On Wed, 2010-05-26 at 19:55 +0300, Heikki Linnakangas wrote: If you set quorum to 1, it also becomes critical infrastructure, because it's possible that a transaction has been replicated to the test server but not the real production standby, and a meteor strikes. Why would you not want to use the test server? Because your failover procedures known nothing about the test server. Even if the data is there in theory, it'd be completely impractical to fetch it from there. If its the only thing left protecting you, and you wish to be protected, then it sounds very cool to me. In my proposal this test server only gets data ahead of other things if the "real production standby" responds too slowly. There's many reasons why a test server could respond faster than the production standby. Maybe the standby is on a different continent. Maybe you have fsync=off on the test server because it's just a test server. Either way, you want the master to ignore it for the purpose of determining if a commit is safe. It scares the out of people that a DBA can take down a server and suddenly the sync protection you thought you had is turned off. Yeah, it depends on what you're trying to accomplish. If durability is absolutely critical to you, (vs. availability), you don't want the commit to ever be acknowledged to the client until it's safely flushed to disk in the standby, even if it means refusing any further commits on the master, until the standby reconnects and catches up. OTOH, if you're not that worried about durability, but you're load balancing queries to the standby, you want to ensure that when you run a query against the standby, a transaction that committed on the master is also visible in the standby. In that scenario, if a standby can't be reached, it is simply pronounced dead, and the master can just ignore it until it reconnects. That way of doing things means an application never knows the protection level any piece of data has had. App designers want to be able to marks things "handle with care" or "just do it quick, don't care much". Yeah, that's useful too. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
Heikki Linnakangas wrote: >> Unless we have a transaction manager and do proper distributed >> transactions, how do you avoid edge conditions like that? > > Yeah, I guess you can't. You can guarantee that a commit is > always safely flushed first in the master, or in the standby, but > without two-phase commit you can't guarantee atomicity. It's > useful to know which behavior you get, though, so that you can > take it into account in your failover procedure. It strikes me that if you always write the commit for the master first, there's at least a possibility of developing a heuristic for getting a slave back in sync should the connection break. If you randomly update zero to N slaves and then have a failure, I don't see much hope. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On 26/05/10 20:33, Kevin Grittner wrote: Heikki Linnakangas wrote: Although, if the master crashes at that point, and quickly recovers, you could see the last transactions committed on the master before they're replicated to the standby. Versus having the transaction committed on one or more slaves but not on the master? Unless we have a transaction manager and do proper distributed transactions, how do you avoid edge conditions like that? Yeah, I guess you can't. You can guarantee that a commit is always safely flushed first in the master, or in the standby, but without two-phase commit you can't guarantee atomicity. It's useful to know which behavior you get, though, so that you can take it into account in your failover procedure. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Wed, May 26, 2010 at 1:24 PM, Heikki Linnakangas wrote: > On 26/05/10 20:10, Kevin Grittner wrote: >> >> Heikki Linnakangas wrote: >> >>> One way to do that would be to refrain from flushing the commit >>> record to disk on the master until the standby has acknowledged >>> it. >> >> I'm not clear on the benefit of doing that, versus flushing the >> commit record and then waiting for responses. Either way some >> databases will commit before others -- what is the benefit of having >> the master lag? > > Hmm, I was going to answer that that way no other transactions can see the > transaction as committed before it has been safely replicated, but I now > realize that you could also flush, but refrain from releasing the entry from > procarray until the standby acknowledges the commit, so the transaction > would look like in-progress to other transactions in the master until that. > > Although, if the master crashes at that point, and quickly recovers, you > could see the last transactions committed on the master before they're > replicated to the standby. No matter what you do, there's going to be corner cases where one node thinks the transaction committed and the other node doesn't know. At any given time, we're either in a state where a crash and restart on the master will replay the commit record, or we're not. And also, but somewhat independently, we're in a state where a crash on the standby will replay the commit record, or we're not. Each of these is dependent on a disk write, and there's no way to guarantee that both of those disk writes succeed or both of them fail. Now, in theory, maybe you could have a system where we don't have a fixed definition of who the master is. If either server crashes or if they lose communication, both crash. If they both come back up, they agree on who has the higher LSN on disk and both roll forward to that point, then designate one server to be the master. If one comes back up and can't reach the other, it appeals to the clusterware for help. The clusterware is then responsible for shooting one node in the head and telling the other node to carry on as the sole survivor. When, eventually, the dead node is resurrected, it *discards* any WAL written after the point from which the new master restarted. Short of that, I don't think "abort the transaction" is a recovery mechanism for when we can't get hold of a standby. We're going to have to commit locally first and then we can decide how long to wait for an ACK that a standby has also committed the same transaction remotely. We can wait not at all, forever, or for a while and then declare the other guy dead. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Wed, 2010-05-26 at 19:55 +0300, Heikki Linnakangas wrote: > Now you want to set up a temporary replica of the master at a > development server, for testing purposes. If you set quorum to 2, your > development server becomes critical infrastructure, which is not what > you want. That's a good argument for standby relays. Nobody hooks in a disposable test machine into a critical production config without expecting it to have some effect. > If you set quorum to 1, it also becomes critical > infrastructure, because it's possible that a transaction has been > replicated to the test server but not the real production standby, and > a meteor strikes. Why would you not want to use the test server? If its the only thing left protecting you, and you wish to be protected, then it sounds very cool to me. In my proposal this test server only gets data ahead of other things if the "real production standby" responds too slowly. It scares the out of people that a DBA can take down a server and suddenly the sync protection you thought you had is turned off. That way of doing things means an application never knows the protection level any piece of data has had. App designers want to be able to marks things "handle with care" or "just do it quick, don't care much". It's a real pain to have to handle all your data the same, and for that to be selectable only by administrators, who may or may not have everything configured correctly/available. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
Heikki Linnakangas wrote: > Although, if the master crashes at that point, and quickly > recovers, you could see the last transactions committed on the > master before they're replicated to the standby. Versus having the transaction committed on one or more slaves but not on the master? Unless we have a transaction manager and do proper distributed transactions, how do you avoid edge conditions like that? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers