Async I/O on Linux in databases

> > you lose the durability guarantee that makes databases useful. ... the data might still be sitting in kernel buffers, not yet written to stable storage.

> No! That's because you stopped using fsync. It's nothing to do with your code being async.

From that section, it sounds like OP was tossing data into the io_uring submition queue and calling it "done" at that point (ie: not waiting for the io_uring completion queue to have the completion indicated). So yes, fsync is needed, but they weren't even waiting for the kernel to start the write before indicating success.

I think to some extent things have been confused because io_uring has a completion concept, but OP also has a separate completion concept in their dual wal design (where the second WAL they call the "completion" WAL).

But I'm not sure if OP really took away the right understanding from their issues with ignoring io_uring completions, as they then create a 5 step procedure that adds one check for an io_uring completion, but still omits another.

> 1. Write intent record (async)

> 2. Perform operation in memory

> 3. Write completion record (async)

> 4. Wait for the completion record to be written to the WAL

> 5. Return success to client

Note the lack of waiting for the io_uring completion of the intent record (and yes, there's still not any reference to fsync or alternates, which is also wrong). There is no ordering guarantee between independent io_urings (OP states they're using separate io_uring instances for each WAL), and even in the same io_uring there is limited ordering around completions (IOSQE_IO_LINK exists, but doesn't allow traversing submission boundaries, so won't work here because OP submits the work a separate times).

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.

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.

> 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.

> Does this mean that a client could receive a success for a request, which if the system crashed immediately afterwards, when replayed, wouldn’t necessarily have that request recorded?

Yup. OP says "the intent record could just be sitting in a kernel buffer", but then the exact same issue applies to the completion record. So confirmation to the client cannot be issued until the completion record has been written to durable storage. Not really seeing the point of this blogpost.

As best I can tell, the author understands that the async write-ahead fails to be a guarantee where the sync one does… then turns their async write into two async writes… but there’s still no guarantee comparable to the synchronous version.

So I fail to see how the two async writes are any guarantee at all. It sounds like they just happen to provide better consistency than the one async write because it forces an arbitrary amount of time to pass.

I think you missed the part in the middle:

-----------------

So the protocol ends up becoming:

Write intent record (async) Perform operation in memory Write completion record (async) Return success to client

-----------------

In other words, the client only knows its a success when both wal files have been written.

The goal is not to provide faster responses to the client, on the first intent record, but to ensure that the system is not stuck with I/O Waiting on fsync requests.

When you write a ton of data to database, you often see that its not the core writes but the I/O > fsync that eat a ton of your resources. Cutting back on that mess, results that you can push more performance out of a write heavy server.

It's really not clear in the article. But I _think_ the gains are to be had because you can do the in-memory updating during the time that the WAL is being written to disk (rather than waiting for it to flush before proceeding). So I'm guessing the protocol as presented, is actually missing a key step:

    Write intent record (async)
    Perform operation in memory
    Write completion record (async)
    * * Wait for intent and completion to be flushed to disk * *
    Return success to client

Presumably the intent record is large (containing the key-value data) while the completion record is tiny

I don't think this is necessarily the case, because the operations may have completed in a different order to how they are recorded in the intent log.

Speaking from experience, its easy to make Postgres (for example), just trash your system usage on a lot of individual or batch inserts. The NVME drives are often extreme underutilized, and your bottleneck is the whole fsync layer.

Second, the durability is the same as fsync. The client only gets reported a success, if both wall writes have been done.

Its the same guarantee as fsync but you bypass the fsync bottleneck, what in turn allows for actually using the benefits of your NVME drives better (and shifting away the resource from the i/o blocking fsync).

Yes, it involves more management because now you need to maintain two states, instead of one with the synchronous fsync operation. But that is the thing about parallel programming, its more complex but you get a ton of benefits from it by bypassing synchronous bottlenecks.

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 performance gain is not uncommon using a method like this.

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.

It has literally the same durability as a fsync write. You need to take in account, that most databases are written 30, 40 ... years ago. In the time when HDDs ruled and stuff like NVME drives was a pipedream. But most DBs still work the same, and threat NVME drives like they are HDDs.

Doing this above operation on a HDD, will cost you 2x the performance because you barely have like 80 to 120 IOPS/s. But a cheap NVME drive easily does 100.000 like its nothing.

If you even monitored a NVME drive with a database write usage, you will noticed that those NVME drives are just underutilized. This is why you see a lot more work in trying new data storage layers being developed for Databases that better utilize NVME capabilities (and trying to bypass old HDD era bottlenecks).

it's possible to unify the WAL and the tree. There are some append only B-tree implementations. https://github.com/Incubaid/baardskeerder fe.

Hopefully never. It almost seems to have been purpose-built for local privilege escalation exploits.