5 ms·

I suspect many apps would benefit from splitting the commit completion notification into two pieces: - commit logically committed (meaning it won't fail to com

I suspect many apps would benefit from splitting the commit completion notification into two pieces: - commit logically committed (meaning it won't fail to complete for any reason other than power or hardware failure, and in particular no trigger or other such logic can keep it from completing) - commit completed durably (i.e., all associated writes have reached stable, persistent storage, and future reads will see them even if there is a power failure in between) A UI could indicate that a transaction is logically complete and have a second way to indicate that the transaction is durably committed.

ddorian43 2y ago
Probably higher cost to send 2 notifications to the user compared to always fsync on all writes in a SSD.
- shayonj 2y ago
  +1 - I have found its better to instead use it on paths that you know are idempotent and recoverable. Liking expiring data on a cron and so on.
- cryptonector 2y ago
  The network is faster then the storage.
  - rubiquity 2y ago
    No it isn't and hasn't been for quite some time and it will probably remain that way for the rest of our lives.
    - cryptonector 2y ago
      It still doesn't matter. All you need is enough CPU cycles to send the additional completion notice.
  - sgarland 2y ago
    With nearly all DBaaS, and _especially_ Aurora, as TFA mentions, the storage is also on the network, so it's moot.
koolba 2y ago
IIRC, that doesn’t work in practice because once you have a synchronous commit requested, all existing async commits that preceded it must be guaranteed to be fsynced for that sync commit to be fsynced. Which kind of makes sense if you think about the later work potentially reading the result of the earlier one. The high throughput gains if async commit shine when the totality of the workload is async.
- rzwitserloot 2y ago
  I think you mean: If anybody anywhere decided to invoke a wait() until the state "my COMMIT; has been durably committed", then that means all previous commits must also be in durably committed state before we can continue. Sure. But this doesn't affect other code that is merely wait()ing for 'logically committed'. Not in the past, and usefully, not in the future either. The 'logical system' can be far ahead of the 'durable' system, it doesn't have to wait. Where it would go wrong is if code A needs to wait around for code B to do a thing (such as provide a value), and B's code for some reason contains a wait() on durable. Possibly because B was written before the split in commit behaviour was around, and for backwards compatibility reasons, for them COMMIT; means: "wait for durable commit". Given that `SET synchronous_commit = off;` works per transaction, I'm kinda inspired here. I can think of a few places where we commit for various logical reasons but I don't need the fsync guarantee.
- x0x0 2y ago
  Right, but there are common use cases (audit, log, analytics tables) where you can tolerate a small risk of loss and get some hefty speedups.
- paulddraper 2y ago
  But.... Only the sync committer needs to wait for those. (Nothing changes.) The async committers can go on their merry way.
ants_a 2y ago
There is a paper exploring this concept: https://cs.uwaterloo.ca/~kdaudjee/ED.pdf https://cs.uwaterloo.ca/~kdaudjee/ED.pdf UI wise it does not make sense to have this distinction, as the window to get durability is a small fraction of a second. But for concurrent modifications the reduction in lock duration can mean an order of magnitude throughput.

ddorian43 2y ago

Probably higher cost to send 2 notifications to the user compared to always fsync on all writes in a SSD.

shayonj 2y ago
+1 - I have found its better to instead use it on paths that you know are idempotent and recoverable. Liking expiring data on a cron and so on.
cryptonector 2y ago
The network is faster then the storage.
- rubiquity 2y ago
  No it isn't and hasn't been for quite some time and it will probably remain that way for the rest of our lives.
  - cryptonector 2y ago
    It still doesn't matter. All you need is enough CPU cycles to send the additional completion notice.
- sgarland 2y ago
  With nearly all DBaaS, and _especially_ Aurora, as TFA mentions, the storage is also on the network, so it's moot.

koolba 2y ago

IIRC, that doesn’t work in practice because once you have a synchronous commit requested, all existing async commits that preceded it must be guaranteed to be fsynced for that sync commit to be fsynced. Which kind of makes sense if you think about the later work potentially reading the result of the earlier one. The high throughput gains if async commit shine when the totality of the workload is async.

rzwitserloot 2y ago
I think you mean: If anybody anywhere decided to invoke a wait() until the state "my COMMIT; has been durably committed", then that means all previous commits must also be in durably committed state before we can continue. Sure. But this doesn't affect other code that is merely wait()ing for 'logically committed'. Not in the past, and usefully, not in the future either. The 'logical system' can be far ahead of the 'durable' system, it doesn't have to wait. Where it would go wrong is if code A needs to wait around for code B to do a thing (such as provide a value), and B's code for some reason contains a wait() on durable. Possibly because B was written before the split in commit behaviour was around, and for backwards compatibility reasons, for them COMMIT; means: "wait for durable commit". Given that `SET synchronous_commit = off;` works per transaction, I'm kinda inspired here. I can think of a few places where we commit for various logical reasons but I don't need the fsync guarantee.
x0x0 2y ago
Right, but there are common use cases (audit, log, analytics tables) where you can tolerate a small risk of loss and get some hefty speedups.
paulddraper 2y ago
But.... Only the sync committer needs to wait for those. (Nothing changes.) The async committers can go on their merry way.

ants_a 2y ago

There is a paper exploring this concept: https://cs.uwaterloo.ca/~kdaudjee/ED.pdf https://cs.uwaterloo.ca/~kdaudjee/ED.pdf UI wise it does not make sense to have this distinction, as the window to get durability is a small fraction of a second. But for concurrent modifications the reduction in lock duration can mean an order of magnitude throughput.