4 ms·

How would that work without risking loss of committed transactions? > Fully durable transaction commits are synchronous and report a commit as successful and r

How would that work without risking loss of committed transactions? > Fully durable transaction commits are synchronous and report a commit as successful and return control to the client only after the log records for the transaction are written to disk. Delayed durable transaction commits are asynchronous and report a commit as successful before the log records for the transaction are written to disk. Writing the transaction log entries to disk is required for a transaction to be durable. Delayed durable transactions become durable when the transaction log entries are flushed to disk. https://learn.microsoft.com/en-us/sql/relational-databases/logs/control-transaction-durability https://learn.microsoft.com/en-us/sql/relational-databases/l...

TwoPhonesOneKid 2y ago
Typically the SQL engine will allow flexibility on this. Not all transactions need to prioritize write-to-disk confirmation over throughput. If you're collecting observability metrics, for instance, these don't have the same data coherency constraints your app model (account etc) demand. In this case you can accept the logical commit and the tiny chance it might not actually hit the disk. Postgres at least allows customizing this per transaction, I believe, although I'm not quite sure how it works if you compose transactions with distinct syncrhonization constraints.
- anonymars 2y ago
  Sure, but the comment I responded to was lamenting that the commits are not asynchronous by default. The documentation I linked to was all about the considerations and behavior for asynchronous commits.
  - TwoPhonesOneKid 2y ago
    I see this understanding that sql databases should do xyz by default as corporate dogmatism, kind of. A database is only as useful as it's used! I realize you haven't argued for this, but if we're collectively claiming postgres can handle 100% of the persistent database needs of an arbitrary app (a very common claim these days), we also need to accept that people will "abuse" sql databases to prioritize accessibility over coherency, which was always a major draw of NoSQL engines. I suspect most consumer apps can scale with some form of inconsistency just fine, even if this creates a PR rats-nest, but consumers are far more forgiving of incompetency than greediness. This is a very much an "understand your market" sort of decision to make. So I see what you're saying, but I'd also like more async bindings that lean into customizing the behavior at query- or execution-time. You can build them today but you have to work around whatever sql-binding framework you use and it will still likely result in leaky abstractions.
    - marsovo 2y ago
      I see what you mean, but ACID is a fairly foundational expectation for SQL transactions (D being the relevant feature here) That being said, my background is primarily Microsoft SQL more than Postgres. As such I'm occasionally bemused at the sort-of monoculture here around Postgres, where if Postgres doesn't have it, it may as well not exist*. And so it is in this case (the DELAYED_DURABILITY documentation I linked above). Alas, this doesn't seem to be something I see in standard SQL, so indeed, as you say, it's too bad that the standard doesn't provide for relaxing the rules. Relatedly, the other interesting thing is the chatter about fsync. I know on Windows that's not the mechanism that's used, and out of curiosity I looked deeper into what MS-SQL does on Linux, and indeed they were able to get significant improvement by leveraging similar mechanisms to ensure the data is hardened to disk without a separate flush (see https://news.ycombinator.com/item?id=43443703 https://news.ycombinator.com/item?id=43443703). They contributed to kernel 4.18 to make it happen. > The repeated use of a write request followed by a flush request may be detrimental to performance and will increase traffic on the I/O bus. > Prior to the Linux Kernel 4.18 updates, Linux could use Fua but only for the file system journaling writes and not data writes. > If your system supports Fua and you have the Linux Kernel 4.18 or newer updates, you can enable SQL Server trace flag -T3979 and use /opt/mssql/bin/mssql-conf set control.alternatewritethrough 0. SQL Server will use Fua write behavior patterns instead of Forced Flush. File systems supporting optimized Fua align SQL Server on Linux with SQL Server on Windows behavior and performance. *I think performance of CTEs/Views is another topic where I noticed it, where it was just taken as given that they can hurt performance, whereas in T-SQL they are simply equivalent to subqueries
      - anarazel 2y ago
        > As such I'm occasionally bemused at the sort-of monoculture here around Postgres, where if Postgres doesn't have it, it may as well not exist. FWIW, I, as a medium-long term PG developer, are also regularly ... bemused by that attitude. We do some stuff well, but we also do a lot of shit not so well, and PG is succeeding despite that, not because of. > Relatedly, the other interesting thing is the chatter about fsync. I know on Windows that's not the mechanism that's used, and out of curiosity I looked deeper into what MS-SQL does on Linux, and indeed they were able to get significant improvement by leveraging similar mechanisms to ensure the data is hardened to disk without a separate flush (see https://news.ycombinator.com/item?id=43443703 https://news.ycombinator.com/item?id=43443703). They contributed to kernel 4.18 to make it happen. Case in point about "we also do a lot of shit not so well" - you can actually get WAL writes utilizing FUA out of postgres, but it's advisable only under somewhat limited circumstances: Most filesystems are only going to use FUA writes with O_DIRECT. The problem is that for streaming replication PG currently reads back the WAL from the filesystem. So from a performance POV it's not great to use FUA writes, because that then triggers read IO. And some filesystems have, uhm, somewhat odd behaviour if you mix buffered and unbuffered IO. Another fun angle around this is that some SSDs have *absurdly* slow FUA writes. Many Samsung SSDs, in particular, have FUA write performance 2-3x slower than their already bad whole-cache-flush performance - and it's not even just client and prosumer drives, it's some of the more lightweight enterprise-y drives too. Edit: fights with HN formatting.
deleted 2y ago
[deleted]
toast0 2y ago
Write data for transaction 1, write data for transaction 2, fsync, signal commit success for transactions 1 and 2. Up to you how many transactions you want in a batch.
- koolba 2y ago
  You missed a number of steps. The transactions are independent so they signal completion (to trigger the commit fsync) independently. You can have the first transaction wait a bit to see if any other commits can be batched in the same fsync. However that’s off by default as the assumption is you want the transaction to complete as fast as possible. At least that’s how PostgreSQL implements it.
  - jiggawatts 2y ago
    The clever way to do this is to immediately commit the first transaction when the storage engine is idle. While it is waiting for the fsync to finish it should batch up any incoming WAL writes and then issue the next fsync immediately after the first one finishes, committing the entire batch at once. Then, and only then, it can reply to clients with “transaction complete”. Some modern database engines now do this, by many older ones don’t because too much of their code assumes one transaction per fsync.
    - koolba 2y ago
      You still have to wait for the final fsync which is only requested after the transaction work has completed. So not sure you’re gaining much if at all from this. There’s also concurrency issues with writing and fsyncing the same fd: http://oldblog.antirez.com/post/fsync-different-thread-useless.html http://oldblog.antirez.com/post/fsync-different-thread-usele...
      - jiggawatts 2y ago
        The key limit is the rate of fsyncs, which is constrained by the user mode to kernel mode transition and physical characteristics of the storage device. In the good old days, it was about a millisecond due to spinning disk latencies, but even on the best SSDs it's about 200 microseconds. This is only about 5K transactions per second, maximum, no matter how trivial the transactions are! With automatic batching, trivial transactions can be grouped together so that the bottleneck becomes bandwidth, not an absolute rate. You get to have your cake and eat it too: There's no additional latency added using automatic batching of transactions because when the I/O queue is empty, the next transaction commits immediately, same as normal. If the disk is already in the middle of an fsync, the next one will have to queue up behind it in the storage subsystem anyway, so the DB engine may as well accumulate more transactions in-memory while it is waiting.

TwoPhonesOneKid 2y ago

Typically the SQL engine will allow flexibility on this. Not all transactions need to prioritize write-to-disk confirmation over throughput. If you're collecting observability metrics, for instance, these don't have the same data coherency constraints your app model (account etc) demand. In this case you can accept the logical commit and the tiny chance it might not actually hit the disk. Postgres at least allows customizing this per transaction, I believe, although I'm not quite sure how it works if you compose transactions with distinct syncrhonization constraints.

anonymars 2y ago
Sure, but the comment I responded to was lamenting that the commits are not asynchronous by default. The documentation I linked to was all about the considerations and behavior for asynchronous commits.
- TwoPhonesOneKid 2y ago
  I see this understanding that sql databases should do xyz by default as corporate dogmatism, kind of. A database is only as useful as it's used! I realize you haven't argued for this, but if we're collectively claiming postgres can handle 100% of the persistent database needs of an arbitrary app (a very common claim these days), we also need to accept that people will "abuse" sql databases to prioritize accessibility over coherency, which was always a major draw of NoSQL engines. I suspect most consumer apps can scale with some form of inconsistency just fine, even if this creates a PR rats-nest, but consumers are far more forgiving of incompetency than greediness. This is a very much an "understand your market" sort of decision to make. So I see what you're saying, but I'd also like more async bindings that lean into customizing the behavior at query- or execution-time. You can build them today but you have to work around whatever sql-binding framework you use and it will still likely result in leaky abstractions.
  - marsovo 2y ago
    I see what you mean, but ACID is a fairly foundational expectation for SQL transactions (D being the relevant feature here) That being said, my background is primarily Microsoft SQL more than Postgres. As such I'm occasionally bemused at the sort-of monoculture here around Postgres, where if Postgres doesn't have it, it may as well not exist*. And so it is in this case (the DELAYED_DURABILITY documentation I linked above). Alas, this doesn't seem to be something I see in standard SQL, so indeed, as you say, it's too bad that the standard doesn't provide for relaxing the rules. Relatedly, the other interesting thing is the chatter about fsync. I know on Windows that's not the mechanism that's used, and out of curiosity I looked deeper into what MS-SQL does on Linux, and indeed they were able to get significant improvement by leveraging similar mechanisms to ensure the data is hardened to disk without a separate flush (see https://news.ycombinator.com/item?id=43443703 https://news.ycombinator.com/item?id=43443703). They contributed to kernel 4.18 to make it happen. > The repeated use of a write request followed by a flush request may be detrimental to performance and will increase traffic on the I/O bus. > Prior to the Linux Kernel 4.18 updates, Linux could use Fua but only for the file system journaling writes and not data writes. > If your system supports Fua and you have the Linux Kernel 4.18 or newer updates, you can enable SQL Server trace flag -T3979 and use /opt/mssql/bin/mssql-conf set control.alternatewritethrough 0. SQL Server will use Fua write behavior patterns instead of Forced Flush. File systems supporting optimized Fua align SQL Server on Linux with SQL Server on Windows behavior and performance. *I think performance of CTEs/Views is another topic where I noticed it, where it was just taken as given that they can hurt performance, whereas in T-SQL they are simply equivalent to subqueries
    - anarazel 2y ago
      > As such I'm occasionally bemused at the sort-of monoculture here around Postgres, where if Postgres doesn't have it, it may as well not exist. FWIW, I, as a medium-long term PG developer, are also regularly ... bemused by that attitude. We do some stuff well, but we also do a lot of shit not so well, and PG is succeeding despite that, not because of. > Relatedly, the other interesting thing is the chatter about fsync. I know on Windows that's not the mechanism that's used, and out of curiosity I looked deeper into what MS-SQL does on Linux, and indeed they were able to get significant improvement by leveraging similar mechanisms to ensure the data is hardened to disk without a separate flush (see https://news.ycombinator.com/item?id=43443703 https://news.ycombinator.com/item?id=43443703). They contributed to kernel 4.18 to make it happen. Case in point about "we also do a lot of shit not so well" - you can actually get WAL writes utilizing FUA out of postgres, but it's advisable only under somewhat limited circumstances: Most filesystems are only going to use FUA writes with O_DIRECT. The problem is that for streaming replication PG currently reads back the WAL from the filesystem. So from a performance POV it's not great to use FUA writes, because that then triggers read IO. And some filesystems have, uhm, somewhat odd behaviour if you mix buffered and unbuffered IO. Another fun angle around this is that some SSDs have *absurdly* slow FUA writes. Many Samsung SSDs, in particular, have FUA write performance 2-3x slower than their already bad whole-cache-flush performance - and it's not even just client and prosumer drives, it's some of the more lightweight enterprise-y drives too. Edit: fights with HN formatting.

deleted 2y ago

[deleted]

toast0 2y ago

Write data for transaction 1, write data for transaction 2, fsync, signal commit success for transactions 1 and 2. Up to you how many transactions you want in a batch.

koolba 2y ago
You missed a number of steps. The transactions are independent so they signal completion (to trigger the commit fsync) independently. You can have the first transaction wait a bit to see if any other commits can be batched in the same fsync. However that’s off by default as the assumption is you want the transaction to complete as fast as possible. At least that’s how PostgreSQL implements it.
- jiggawatts 2y ago
  The clever way to do this is to immediately commit the first transaction when the storage engine is idle. While it is waiting for the fsync to finish it should batch up any incoming WAL writes and then issue the next fsync immediately after the first one finishes, committing the entire batch at once. Then, and only then, it can reply to clients with “transaction complete”. Some modern database engines now do this, by many older ones don’t because too much of their code assumes one transaction per fsync.
  - koolba 2y ago
    You still have to wait for the final fsync which is only requested after the transaction work has completed. So not sure you’re gaining much if at all from this. There’s also concurrency issues with writing and fsyncing the same fd: http://oldblog.antirez.com/post/fsync-different-thread-useless.html http://oldblog.antirez.com/post/fsync-different-thread-usele...
    - jiggawatts 2y ago
      The key limit is the rate of fsyncs, which is constrained by the user mode to kernel mode transition and physical characteristics of the storage device. In the good old days, it was about a millisecond due to spinning disk latencies, but even on the best SSDs it's about 200 microseconds. This is only about 5K transactions per second, maximum, no matter how trivial the transactions are! With automatic batching, trivial transactions can be grouped together so that the bottleneck becomes bandwidth, not an absolute rate. You get to have your cake and eat it too: There's no additional latency added using automatic batching of transactions because when the I/O queue is empty, the next transaction commits immediately, same as normal. If the disk is already in the middle of an fsync, the next one will have to queue up behind it in the storage subsystem anyway, so the DB engine may as well accumulate more transactions in-memory while it is waiting.