3 ms·

It hurts my soul that database engines still don’t batch multiple transactions per disk flush as a default setting. It’s such an insane bottleneck on modern sy

It hurts my soul that database engines still don’t batch multiple transactions per disk flush as a default setting. It’s such an insane bottleneck on modern systems.

Animats 2y ago
File system APIs should be able to return events "data is transferred, buffer no longer needed" and "data is safely stored on persistent media". That's what databases really need from the file system. Interestingly, some IBM mainframe hardware makes this distinction, with "channel end" and "device end". But Linux does not.
- colanderman 2y ago
  Linux does make this distinction. Linux has two main disk I/O paths, buffered and direct I/O. Direct I/O indicates "channel end" by the completion of the write operation. By that point, data has been transferred to disk from the buffer and the buffer can be reused. (Notably, direct I/O may be performed asynchronously via the Linux AIO API.) Contrary to popular belief, this does not indicate that the data is safely committed to disk. [1] Buffered I/O does not require the concept of "channel end", since write calls complete immediately after the buffer contents have been copied into kernel space. (This mode is what PostgreSQL uses by default. I don't know MySQL.) In either case, `fsync(2)` (or `fdatasync(2)`) is used to indicate "device end". The disk has indicated that data is safely stored. (This can likewise be monitored asynchronously, either via the AIO API for direct I/O, or `sync_file_range(2)` for buffered I/O. The latter is used by PostgreSQL [2].) Aside – Linux has also recently grown support for this concept in its networking API, via zerocopy `send(2)` functionality in io_uring. [1] https://lwn.net/Articles/457667/ https://lwn.net/Articles/457667/ [2] https://news.ycombinator.com/item?id=11512653 https://news.ycombinator.com/item?id=11512653
  - evanelias 2y ago
    > This mode is what PostgreSQL uses by default. I don't know MySQL. Assuming the InnoDB storage engine, the default recently changed to Direct I/O as of MySQL 8.4, see https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.html#sysvar_innodb_flush_method https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.ht... It was common to set this even before it became the default though, since InnoDB maintains its own buffer pool caching scheme, and there's no sense in duplicating data pages between the InnoDB buffer pool and the OS cache. The general idea is that you should assign the majority of the system's memory to the InnoDB buffer pool, and then use direct I/O to bypass the system cache, since InnoDB can manage memory more intelligently than the OS. This is because InnoDB has a better understanding of your workload and its own data structures, vs the OS cache being more general-purpose. Other MySQL storage engines behave differently, for example MyISAM had its own caching only for indexes but relied on the OS cache for row data. (if I'm remembering correctly, that is; been a long while since I've touched MyISAM. It's a non-ACID engine and generally should not be used.)
anonymars 2y ago
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.
Andys 2y ago
I thought PostgreSQL did, but now that I check the docs, you are correct, the default `commit_delay` is zero. That would be worth increasing a little if you can afford the latency.
- anarazel 2y ago
  It works even with that setting at zero! Just requires a bit more concurrency.
bob1029 2y ago
It is really shocking to me that this isn't more of a thing. If the I/O block size is 4,096 bytes and you are doing things like updating a user's email address or some boolean preference, you could get a LOT more data packed in per unit space & time. Each block is enough to store an entire page of text or a small image. The only caveat is that the system needs to be consistently under heavy load, or you would have to force clients to wait unreasonable amounts of time for a sufficient batch to accumulate. I think the best goldilocks path is to put something like a MPSC ring buffer in front of the command processor and to "take what you can get" in terms of batch size each iteration.
colanderman 2y ago
They do, if you issue the transactions concurrently (i.e., multiple DB connections). This is "group commit" as referenced in the article. You can't batch sequential transactions without violating the semantics of `COMMIT`. (Data is after all not durable if it's not flushed to disk!) Hence such behavior is not the default in quality databases. If you are issuing many sequential writes and you don't care about them being individually committed, you can and should group them.
avinassh 2y ago
where can I read more?

It hurts my soul that database engines still don’t batch multiple transactions per disk flush as a default setting. It’s such an insane bottleneck on modern systems.

Animats 2y ago

File system APIs should be able to return events "data is transferred, buffer no longer needed" and "data is safely stored on persistent media". That's what databases really need from the file system. Interestingly, some IBM mainframe hardware makes this distinction, with "channel end" and "device end". But Linux does not.

colanderman 2y ago
Linux does make this distinction. Linux has two main disk I/O paths, buffered and direct I/O. Direct I/O indicates "channel end" by the completion of the write operation. By that point, data has been transferred to disk from the buffer and the buffer can be reused. (Notably, direct I/O may be performed asynchronously via the Linux AIO API.) Contrary to popular belief, this does not indicate that the data is safely committed to disk. [1] Buffered I/O does not require the concept of "channel end", since write calls complete immediately after the buffer contents have been copied into kernel space. (This mode is what PostgreSQL uses by default. I don't know MySQL.) In either case, `fsync(2)` (or `fdatasync(2)`) is used to indicate "device end". The disk has indicated that data is safely stored. (This can likewise be monitored asynchronously, either via the AIO API for direct I/O, or `sync_file_range(2)` for buffered I/O. The latter is used by PostgreSQL [2].) Aside – Linux has also recently grown support for this concept in its networking API, via zerocopy `send(2)` functionality in io_uring. [1] https://lwn.net/Articles/457667/ https://lwn.net/Articles/457667/ [2] https://news.ycombinator.com/item?id=11512653 https://news.ycombinator.com/item?id=11512653
- evanelias 2y ago
  > This mode is what PostgreSQL uses by default. I don't know MySQL. Assuming the InnoDB storage engine, the default recently changed to Direct I/O as of MySQL 8.4, see https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.html#sysvar_innodb_flush_method https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.ht... It was common to set this even before it became the default though, since InnoDB maintains its own buffer pool caching scheme, and there's no sense in duplicating data pages between the InnoDB buffer pool and the OS cache. The general idea is that you should assign the majority of the system's memory to the InnoDB buffer pool, and then use direct I/O to bypass the system cache, since InnoDB can manage memory more intelligently than the OS. This is because InnoDB has a better understanding of your workload and its own data structures, vs the OS cache being more general-purpose. Other MySQL storage engines behave differently, for example MyISAM had its own caching only for indexes but relied on the OS cache for row data. (if I'm remembering correctly, that is; been a long while since I've touched MyISAM. It's a non-ACID engine and generally should not be used.)

anonymars 2y ago

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.

Andys 2y ago

I thought PostgreSQL did, but now that I check the docs, you are correct, the default `commit_delay` is zero. That would be worth increasing a little if you can afford the latency.

anarazel 2y ago
It works even with that setting at zero! Just requires a bit more concurrency.

bob1029 2y ago

It is really shocking to me that this isn't more of a thing. If the I/O block size is 4,096 bytes and you are doing things like updating a user's email address or some boolean preference, you could get a LOT more data packed in per unit space & time. Each block is enough to store an entire page of text or a small image. The only caveat is that the system needs to be consistently under heavy load, or you would have to force clients to wait unreasonable amounts of time for a sufficient batch to accumulate. I think the best goldilocks path is to put something like a MPSC ring buffer in front of the command processor and to "take what you can get" in terms of batch size each iteration.

colanderman 2y ago

They do, if you issue the transactions concurrently (i.e., multiple DB connections). This is "group commit" as referenced in the article. You can't batch sequential transactions without violating the semantics of `COMMIT`. (Data is after all not durable if it's not flushed to disk!) Hence such behavior is not the default in quality databases. If you are issuing many sequential writes and you don't care about them being individually committed, you can and should group them.

avinassh 2y ago

where can I read more?