Dolt is a MySQL-compatible SQL database that has first-class Git-like version-control features. We’ve been building Dolt for eight years and we are constantly making performance improvements. Recently my colleague reached out to me with a PR that showed a marked improvement on Dolt’s sysbench run.

The PR bore the following diff stats, +204 of which is new tests and copyright headers:

A 10–40% improvement on Dolt’s write performance from a single simple change is basically unheard of. Engineers across the organization are generally in the trenches fighting for the likes of 2%, which is considered a big win.

After vetting the PR for design and correctness, we ended up landing it in Dolt. But software engineering is often about tradeoffs. I wanted to better understand both why the solution needed to be structured the way it was and what the underlying performance implications and tradeoffs of the change actually were. This blog post is a short exploration of what the change was, why it worked so well for the benchmark, and when it would be expected to work similarly in an actual Dolt deployment. Strap in to learn a bit about filesystem implementations and how they can affect the performance of a journaling database store.

What Changed?#

Dolt’s OLTP write path is governed by serial sequential writes to a journal file, which was originally introduced in 2023. Writes to Dolt always need to be crash resistant, and to achieve that end Dolt has a specific protocol it goes through when it lands and acknowledges a new commit in its journaled store. The PR in question changed that protocol in two ways, both of which were necessary to realize the performance improvement on the benchmark. First, it switched from calling fsync(2) to calling fdatasync(2) to make each commit durable. Second, each time a journal record’s write ends up extending the lengh of the current file, the write process also writes 4 MiB of zeroes immediately after that record. The majority of the record writes into the journal become overwrites of that zero-padded region, instead of appends of the file itself.

Here’s a short comparison of the new protocol and its two modes, compared to the old one:

We’ll look at the two changes in turn.

fsync vs. fdatasync#

fsync(2) and fdatasync(2) are both syscalls that ask the operating system to flush data from memory into persistent storage. In normal operation, a write(2) call which performs a write against a file will only modify in-memory kernel data structures which represent that file. As a gross simplification, the write will hit certain pages in the page cache which are backed by that file, those pages will be modified to reflect the written data and, if they are not already, they will be marked as dirty. A background process will be responsible for actually flushing dirty pages back to persistent storage, so that they can be read with their new contents after a machine reboot, for example. The f{,data}sync(2) syscall requests an immediate flush of the dirty blocks associated with the given file descriptor and it blocks the caller until those blocks are persistently on the storage device and are available to be read in the future.

fdatasync(2) has a slightly restricted contract compared to fsync(2)—fsync(2) guarantees to flush all data and metadata for the file, while fdatasync(2) only guarantees to flush all data and any associated metadata which is necessary for successfully reading that data. The manpage clarifies a common situation which distinguishes them:

For example, changes to st_atime or st_mtime (respectively, time of last access and time of last modification; see stat(2)) do not require flushing because they are not necessary for a subsequent data read to be handled correctly. On the other hand, a change to the file size (st_size, as made by say ftruncate(2)), would require a metadata flush.

The aim of fdatasync() is to reduce disk activity for applications that do not require all metadata to be synchronized with the disk.

The Multitude of Ways to Append#

The manpage blurb mentions ftruncate(2) as a way to update the st_size metadata of a file. ftruncate(2) can be used to both grow a file, by passing a size larger than the existing size, and to shrink a file, by passing a size which is smaller than the existing size. In the case where the file is grown, it behaves as if the extended part was filled with zero bytes. The same kind of size extension happens when an application performs a pwrite(2) at an offset that is not currently within the bounds of the file. fallocate(2) can also be used to extend the size of an existing file and zero fill the extension. So there are at least three ways to zero fill the suffix of a file without going through a write(2) call.

At least on Linux’s most popular filesystems, it turns out that none of those alternative approaches are sufficient to actually bypass a metadata flush with a later fdatasync(2) when later write(2) calls overwrite those zero-byte extents. This is filesystem specific, but in general, just avoiding the st_mtime and st_size update is not enough. If the filesystem needs to allocate new blocks to the file or change existing file blocks or extents to be marked as written, where they might have previously been marked as empty or unwritten, then it may need to flush metadata anyway. This turns out to be the case on the two most popular Linux filesystems, Ext4 and XFS.

By combining these two approaches we have a mechanism that, in some operational contexts, reduces the amount of I/O Dolt needs to do, on average, to durably persist a new write.

The Conditions for Improvement#

It turns out those conditions prevail quite dominantly in Dolt’s own internal sysbench runs. There are two characteristics of the measured workload that have a big impact on its improved write performance.

Filesystems—Not All Created Equal#

Filesystems differ widely in how they persist data, track metadata updates, handle block allocations, etc. I knew the sysbench benchmarks ran against Ext4. I wanted to better understand how the behavior changed on Ext4 and whether that change was the same across other popular filesystems. I benchmarked the underlying mechanism, fsync vs. fdatasync on overwrites in particular, across four popular filesystems: Ext4, XFS, Btrfs and OpenZFS. The benchmarks came out as follows:

You can see that Ext4 and XFS see gains on the median and even average commit latency given the approach, but the same is not true of Btrfs and OpenZFS. Ext4 and XFS represent relatively traditional filesystems. They perform in-place mutations of persisted on-disk data when that data changes. Btrfs and OpenZFS take a different approach, implementing copy-on-write for all data in the filesystem. On an overwrite in particular, Ext4 and XFS get away with a single round-trip I/O flush in the fdatasync(2) case. On the other hand, Btrfs and OpenZFS both need to flush the equivalent of metadata for each write, regardless of whether it’s an overwrite, and so they do not see much change in performance between an fsync(2) and an fdatasync(2).

Another thing lurking in the results is that Ext4 benefits more from this approach than XFS. It turns out that Ext4 has the slower metadata writes compared to XFS on this hardware, because it makes more serial round trips to storage. When you trace the device I/O commands issued by Ext4 when you f{,data}sync(2) a write that includes a metadata change, you find that Ext4 issues three serial I/O commands, while XFS only issues two:

The strategies XFS and Ext4 take are generally the same across append, fallocate, ftruncte and pwrite. The difference in average latency on append between XFS and Ext4 comes down to the round-trip latency associated with the storage device and the fact that Ext4 makes 3 round trips—data, metadata and commit—while XFS only makes two, persisting the data first and then persisting the metadata and the commit in a single round trip.

On a storage device that has a smaller round-trip latency, such as some local SSDs and certainly local NVMEs, the same performance differences would not be as evident, even on Ext4 and XFS.

The Workload—Big Gains for Small Serial Writes#

OLTP workloads also differ and it turns out our internal sysbench workload was particularly well suited to benefit from this change. The write benchmarks in the internal sysbench workload mostly run small serial writes, one after the other, with no concurrency. That means in order to start staging the second write, the first write has to build and commit completely. Reducing the latency of these small writes means the next write starts making progress sooner and is itself ready to take advantage of the new lower-latency commits sooner.

This works well in this case due to two factors. First, the writes are small enough that many of them fit within each 4 MiB prewritten region. As the writes become larger, fewer commits can use that region before Dolt needs to refill it. A write on the order of 2 MiB, for example, would bring the refill cost around much more often.

The second factor is another I/O device performance characteristic—maximum throughput. The write-ahead strategy is making a fundamental trade-off when it chooses to write a bunch of zeroes to the device which it is going to immediately overwrite. It is shipping those zeroes to the device and consuming drive write bandwidth, as well as potentially drive write cycles in the case of something like an SSD. The basic approach causes up to two times write amplification against the device. If Dolt’s write workload during the benchmark were structured in such a way that Dolt was already or was close to saturating the write bandwidth to the device, then causing Dolt to write up to twice as many bytes to the device during the benchmark, half of which were useless and designed to be immediately overwritten, would be a big regression instead of a big improvement.

The Device—High Latency and Not Saturated#

The filesystem and the workload are a major factor in the improvement the benchmark showed, but they’re not sufficient on their own. The result is a confluence of those two characteristics operating against specific hardware. In this case, the benchmarked storage device is network attached storage in the form of an AWS EBS volume. That device has relatively high round-trip latency—just under 1ms, for the most part—and it has plenty of excess write throughput and IOPS available for the given benchmark. In artificial benchmarks, against the same types of devices, increasing the write throughput towards the device’s limit eventually degraded goodput on the written bytes from the application. Saturing the write throughput by writing a bunch of zero bytes is counterproductive if your bottleneck is throughput instead of repeated onerous blocking across a long, serial sequence of f{,data}sync(2) calls.

Next Steps#

The change to the write behavior of Dolt landed in main but has yet to make it into a Dolt release. We feel like the performance win is large enough across a number of common scenarios that it makes sense as the default. We are still investigating whether it’s worth making write ahead behavior tuneable and whether the behavior should change automatically based on, for example, the filesystem Dolt finds the database hosted on or the write patterns Dolt observes while it is running. If you have ideas here or want to talk about storage optimization for an OLTP database, make sure to drop by our Discord. We hope to see you there!