5 ms·

Hm... thanks for the head's up. I thought that SQLITE_CONSTRAINT would detect both read and write conflicts, but I'll dig deeper there. As for how much conten

Hm... thanks for the head's up. I thought that SQLITE_CONSTRAINT would detect both read and write conflicts, but I'll dig deeper there. As for how much contention there will be, I'm not sure: that's one reason we haven't taken the plunge yet as we sorta want to determine this first. However, given that we have a huge number of users modifying unshared data (eg, our largest group still makes up a tiny fraction of the userbase), I suspect conflicts will be relatively few. And to be clear: even a little write concurrency goes a long way. If we can even do two writes simultaneously, that doubles our write performance. But I personally suspect the average batch size of non-conflicting transactions will be in the dozens or even hundreds -- meaning I suspect we will achieve so much replication throughput that something else becomes the bottleneck before replication is saturated. Only one way to find out however!

Jweb_Guru 10y ago
Thinking about it, your algorithm also does not sound crash-resilient. Specifically, unless you're doing round-trip confirmations, you can easily have different replicas with different (possibly overlapping, possibly disjoint, in weird patterns) sets of changesets applied within a batch. If you have asynchronous replication at all you probably already have to deal with any issues arising from "gaps" in replication on some of the replicas for a single version, but if you don't, be aware that it can be very tricky to deal with properly on recovery. Specifically, if the master applies different transactions (within a batch) to different replicas, then goes down, and you elect a replica that only saw some of the transactions, and there can't be gaps, it's relatively easy to identify which replica is most up-to-date and make that one the leader. But if there can be gaps, there's the possibility that different nodes have different transactions applied, so you'll have to make sure any gaps are filled in before you elect a new leader. Still, with a total order, it's not that bad, because it's easy to identify gaps; with your algorithm, however, there's no total order between changesets within or across batches (at least, none that I can see in your algorithm), so you won't have any way of knowing whether there are gaps (at least, not without doing something silly like broadcasting every changeset)! Adding a total order seems like the easiest way to resolve that, but like I said even that case can be tricky. Also, your algorithm doesn't specify that replicas which receive changesets with batch ids with gaps in them wait to apply them until the gap is filled in (which means they have to know when the previous batch is done), or alternately that before a batch with a new id can be sent from the master all replicas must have synchronously applied all changes within the old batch. I think something like that is a requirement here in order for there not to be potential conflicts on your replicas during replication even if the master doesn't crash. IMO, doing exactly your algorithm but delaying replication or transaction confirmation until a specified amount of time has passed (say 10 ms), then synchronously sending the entire changeset and incrementing the batch id (aka group commit) is a much better idea. You exchange marginally worse latency (and more bursty network usage) for a far less complex update scenario (yes, you're back to having a total ordering on writes at replicas you need to follow, but you can include multiple changesets in the batch). You can also have your master monitor number of writes and only turn on group commit if there's unusually high write volume. (Also, belatedly reading one of your other comments more carefully, I'm fairly confident commitCount will also not work correctly if changesets can be applied out of order on different replicas, even if there was no data loss, since the same commitCount could include different sets of changesets at any one time).
- quinthar 10y ago
  To be clear, my sense is that every replica would receive every transaction, and would be free to commit the transactions inside each batch in any order. Now I do agree that it's tricky to avoid "gaps" in the failure cases. However, every replica keeps a record of the past several million transactions (we aim for 3 days), and every transaction is assigned a unique ID. When a replica starts up, it "synchronizes" down every missing transaction it has, and at this point would "repair" any gaps it somehow obtained when it went down. Admittedly, the exact details of that part are TBD, but it doesn't strike me as an unresolvable problem on the surface.
  - Jweb_Guru 10y ago
    The question is: (1) In the event of a crash, how does it know which transactions it's missing without a total order on the write transactions? (2) In the non-failure case, how do you know the transactions in one batch are ordered before the transactions in a subsequent batch (which requires the replica to identify any "gaps" within the previous batch so it can wait for them to come in before continuing on)? I don't think any of this is unresolvable (just adding a total order would go a long way). I do think it's very tricky to get right, with lots of edge cases, and that if you can't efficiently identify missing transactions it potentially makes recovery unusably slow. In any case, since I believe you are losing serializability with multiple writers, you should probably weigh that against any performance gains you get from multiple writers (I think even the group commit variant suffers from this problem).
    - Jweb_Guru 10y ago
      Also, as pointed out in https://twitter.com/aphyr/status/788757992829222912 https://twitter.com/aphyr/status/788757992829222912, even your current solution isn't safe if your commit order isn't total across leader changes (you can resolve this by adding an epoch number that increments every time leader election occurs). Strongly recommend you take him up on his offer.
      - quinthar 10y ago

Jweb_Guru 10y ago

Thinking about it, your algorithm also does not sound crash-resilient. Specifically, unless you're doing round-trip confirmations, you can easily have different replicas with different (possibly overlapping, possibly disjoint, in weird patterns) sets of changesets applied within a batch. If you have asynchronous replication at all you probably already have to deal with any issues arising from "gaps" in replication on some of the replicas for a single version, but if you don't, be aware that it can be very tricky to deal with properly on recovery. Specifically, if the master applies different transactions (within a batch) to different replicas, then goes down, and you elect a replica that only saw some of the transactions, and there can't be gaps, it's relatively easy to identify which replica is most up-to-date and make that one the leader. But if there can be gaps, there's the possibility that different nodes have different transactions applied, so you'll have to make sure any gaps are filled in before you elect a new leader. Still, with a total order, it's not that bad, because it's easy to identify gaps; with your algorithm, however, there's no total order between changesets within or across batches (at least, none that I can see in your algorithm), so you won't have any way of knowing whether there are gaps (at least, not without doing something silly like broadcasting every changeset)! Adding a total order seems like the easiest way to resolve that, but like I said even that case can be tricky. Also, your algorithm doesn't specify that replicas which receive changesets with batch ids with gaps in them wait to apply them until the gap is filled in (which means they have to know when the previous batch is done), or alternately that before a batch with a new id can be sent from the master all replicas must have synchronously applied all changes within the old batch. I think something like that is a requirement here in order for there not to be potential conflicts on your replicas during replication even if the master doesn't crash. IMO, doing exactly your algorithm but delaying replication or transaction confirmation until a specified amount of time has passed (say 10 ms), then synchronously sending the entire changeset and incrementing the batch id (aka group commit) is a much better idea. You exchange marginally worse latency (and more bursty network usage) for a far less complex update scenario (yes, you're back to having a total ordering on writes at replicas you need to follow, but you can include multiple changesets in the batch). You can also have your master monitor number of writes and only turn on group commit if there's unusually high write volume. (Also, belatedly reading one of your other comments more carefully, I'm fairly confident commitCount will also not work correctly if changesets can be applied out of order on different replicas, even if there was no data loss, since the same commitCount could include different sets of changesets at any one time).

quinthar 10y ago
To be clear, my sense is that every replica would receive every transaction, and would be free to commit the transactions inside each batch in any order. Now I do agree that it's tricky to avoid "gaps" in the failure cases. However, every replica keeps a record of the past several million transactions (we aim for 3 days), and every transaction is assigned a unique ID. When a replica starts up, it "synchronizes" down every missing transaction it has, and at this point would "repair" any gaps it somehow obtained when it went down. Admittedly, the exact details of that part are TBD, but it doesn't strike me as an unresolvable problem on the surface.
- Jweb_Guru 10y ago
  The question is: (1) In the event of a crash, how does it know which transactions it's missing without a total order on the write transactions? (2) In the non-failure case, how do you know the transactions in one batch are ordered before the transactions in a subsequent batch (which requires the replica to identify any "gaps" within the previous batch so it can wait for them to come in before continuing on)? I don't think any of this is unresolvable (just adding a total order would go a long way). I do think it's very tricky to get right, with lots of edge cases, and that if you can't efficiently identify missing transactions it potentially makes recovery unusably slow. In any case, since I believe you are losing serializability with multiple writers, you should probably weigh that against any performance gains you get from multiple writers (I think even the group commit variant suffers from this problem).
  - Jweb_Guru 10y ago
    Also, as pointed out in https://twitter.com/aphyr/status/788757992829222912 https://twitter.com/aphyr/status/788757992829222912, even your current solution isn't safe if your commit order isn't total across leader changes (you can resolve this by adding an epoch number that increments every time leader election occurs). Strongly recommend you take him up on his offer.
    - quinthar 10y ago