Async I/O on Linux in databases • Hacker News Board

Re: Async I/O on Linux in databases

#51

Post by toast0 » Sun, Jul 20, 2025, 3:01 PM UTC

ori_b wrote: post #34

nephalegm wrote: post #20

Earlier quoted context omitted.

Reading through the article it’s explained in the recovery process. He reads the intent log entries and the completion entries and only applies them if they both exist. So there is no guarantee that operations are committed by virtue of not being acknowledged to the application (asynchronous) the recovery replay will be consistent. I could see it would be problematic for any data where the order of operations is impo…

There's not even a guarantee that the intent log flushes to disk before the completion log. You can get completions entries in the completion log that were lost in the intent log. So, no, there's no guarantee of consistent recovery. You'd be better off with a single log.

There's no guarantee of ordering of writes within the two logs either.

This seems nightmarish to recover from.

Re: Async I/O on Linux in databases

#52

Post by lmeyerov » Sun, Jul 20, 2025, 3:01 PM UTC

ori_b wrote: post #34

nephalegm wrote: post #20

Earlier quoted context omitted.

Reading through the article it’s explained in the recovery process. He reads the intent log entries and the completion entries and only applies them if they both exist. So there is no guarantee that operations are committed by virtue of not being acknowledged to the application (asynchronous) the recovery replay will be consistent. I could see it would be problematic for any data where the order of operations is impo…

There's not even a guarantee that the intent log flushes to disk before the completion log. You can get completions entries in the completion log that were lost in the intent log. So, no, there's no guarantee of consistent recovery. You'd be better off with a single log.

I think he says he checks for both

It's interesting as a weaker safety guarantee. He is guaranteeing write integrity, so valid WAL view on restart by throwing out mismatching writes. But from an outside observation, premature signaling of completion, which would mean data loss as a client may have moved on without retries due to thinking the data was safely saved. (I was a bit confused in the completion meaning around this, so not confident.)

We hit some similar scenarios in Graphistry where we treat recieving server disk/RAM during browser uploads as writethrough caches in front of our cloud storage persistence tiers. The choice of when to signal success to the uploader is funny -- disk/RAM vs cloud storage -- and timing difference is fairly observable to the web user.

Re: Async I/O on Linux in databases

#53

Post by ajross » Sun, Jul 20, 2025, 3:03 PM UTC

quietbritishjim wrote: post #29

The article claims that, when they switched to io_uring, > throughput increased by an order of magnitude almost immediately But right near the start is the real story: the sync version had > the classic fsync() call after every write to the log for durability They are not comparing performance of sync APIs vs io_uring. They're comparing using fsync vs not using fsync! They even go on to say that a problem with async…

> There's no getting around fsync if you want to be sure that your data is really on the storage medium.

That's not correct; io_uring supports O_DIRECT write requests just fine. Obviously bypassing the cache isn't the same as just flushing it (which is what fsync does), so there are design impacts.

But database engines are absolutely the target of io_uring's feature set and they're expected to be managing this complexity.

Re: Async I/O on Linux in databases

#54

Post by azlev » Sun, Jul 20, 2025, 3:14 PM UTC

immibis wrote: post #44

quietbritishjim wrote: post #36

Earlier quoted context omitted.

Well, there already is slightly more fine gained control: in the sync version, you can perhaps call sync write() a few times before calling fsync() once i.e. basically batch up a few writes. That does have the disadvantage that you can't easily queue new writes while waiting for the previous ones. Perhaps you could use calls to write() in another thread while the first one is waiting for fsync() for the previous batc…

Postgres claims to have some kind of commit batching, but I couldn't figure out how to turn it on. I wanted to scrub a table by processing each row, but without holding locks, so I wanted to commit every few hundred rows, but with only ACI and not D, since I could just run the process again. I don't think Postgres supports this feature. It also seemed to be calling fsync much more than once per transaction.

commit_delay

https://www.postgresql.org/docs/current/runtime-config-wal.h...

Re: Async I/O on Linux in databases

#55

Post by zozbot234 » Sun, Jul 20, 2025, 3:15 PM UTC

ajross wrote: post #53

quietbritishjim wrote: post #29

The article claims that, when they switched to io_uring, > throughput increased by an order of magnitude almost immediately But right near the start is the real story: the sync version had > the classic fsync() call after every write to the log for durability They are not comparing performance of sync APIs vs io_uring. They're comparing using fsync vs not using fsync! They even go on to say that a problem with async…

> There's no getting around fsync if you want to be sure that your data is really on the storage medium. That's not correct; io_uring supports O_DIRECT write requests just fine. Obviously bypassing the cache isn't the same as just flushing it (which is what fsync does), so there are design impacts. But database engines are absolutely the target of io_uring's feature set and they're expected to be managing this comple…

That's not what O_DIRECT is for. Did you mean O_SYNC ?

Re: Async I/O on Linux in databases

#56

Post by codys » Sun, Jul 20, 2025, 3:21 PM UTC

benjiro wrote: post #35

tobias3 wrote: post #14

I don't get this. How can two(+) WAL operations be faster than one (double the sync IOPS)? I think this database doesn't have durability at all.

fsync waits for the drive to report back the success write. When you do a ton of small writes, fsync becomes a bottleneck. Its a issue of context switching and pipelining with fsync. When you async write data, you do not need to wait for this confirmation. So by double writing two async requests, you are better using all your system CPU cores as they are not being stalled waiting for that I/O response. Seeing a 10x p…

> Yes, you do need to check if both records are written and then report it back to the client. But that is a non-fsync request and does not tax your system the same as fsync writes.

What mechanism can be used to check that the writes are complete if not fsync (or adjacent fdatasync)? What specific io_uring operation or system call?

Re: Async I/O on Linux in databases

#57

Post by zozbot234 » Sun, Jul 20, 2025, 3:22 PM UTC

stefanha wrote: post #47

zozbot234 wrote: post #30

Earlier quoted context omitted.

So OP's real point is that fsync() sucks in the context of modern hardware where thousands of I/O reqs may be in flight at any given time. We need more fine-grained mechanisms to ensure that writes are committed to permanent storage, without introducing undue serialization.

The Linux RWF_DSYNC flag sets the Full Unit Access (FUA) bit in write requests. This can be used instead of fdatasync(2) in some cases. It only syncs a specific write request instead of the entire disk write cache.

You should prefer RWF_SYNC in case the write involves changes to the file metadata (For example, most append operations will alter the file size).

Re: Async I/O on Linux in databases

#58

Post by eatonphil » Sun, Jul 20, 2025, 3:23 PM UTC

From the title I was hoping this would be a survey of databases using io_uring, since there've been quips on the internet (here, twitter, etc) that no one uses io_uring in production. In my brief search TigerBeetle (and maybe Turso's Limbo) was the only database in production that I remember doing io_uring (by default). Some other databases had it as an option but didn't seem to default to it.

If anyone else feels like doing this survey and publishing the results I'd love to see it.

Re: Async I/O on Linux in databases

#59

Post by codys » Sun, Jul 20, 2025, 3:26 PM UTC

ajross wrote: post #53

quietbritishjim wrote: post #29

The article claims that, when they switched to io_uring, > throughput increased by an order of magnitude almost immediately But right near the start is the real story: the sync version had > the classic fsync() call after every write to the log for durability They are not comparing performance of sync APIs vs io_uring. They're comparing using fsync vs not using fsync! They even go on to say that a problem with async…

> There's no getting around fsync if you want to be sure that your data is really on the storage medium. That's not correct; io_uring supports O_DIRECT write requests just fine. Obviously bypassing the cache isn't the same as just flushing it (which is what fsync does), so there are design impacts. But database engines are absolutely the target of io_uring's feature set and they're expected to be managing this comple…

> But database engines are absolutely the target of io_uring's feature set and they're expected to be managing this complexity.

io_uring includes an fsync opcode (with range support). When folks talk about fsync generally here, they're not saying the io_uring is unusable, they're saying that they'd expect the fsync to be used whether it's via the io_uring opcode, the system call, or some other mechanism yet to be created.

Re: Async I/O on Linux in databases

#60

Post by jorangreef » Sun, Jul 20, 2025, 3:38 PM UTC

osigurdson wrote: post #43

quietbritishjim wrote: post #29

The article claims that, when they switched to io_uring, > throughput increased by an order of magnitude almost immediately But right near the start is the real story: the sync version had > the classic fsync() call after every write to the log for durability They are not comparing performance of sync APIs vs io_uring. They're comparing using fsync vs not using fsync! They even go on to say that a problem with async…

Suggest watching the Tigerbeatle video link in the article. There they discuss bitrot, "fsync gate", how Postgres used fsync wrong for 30 years, etc. It is very interesting even as pure entertainment.

Thanks! Great to hear you enjoyed our talk. Most of it is simply putting the spotlight on UW-Madison’s work on storage faults.

Just to emphasize again that this blog post here is really quite different, since it does not fsync and breaks durability.

Not what we do in TigerBeetle or would recommend or encourage.