Better guidance for database developers

At the inaugural Databases microconference at the 2019 Linux Plumbers Conference (LPC), two developers who work on rather different database systems had similar complaints about developing for Linux. Richard Hipp, creator of the SQLite database, and Andres Freund from the PostgreSQL project both lamented the lack of definitive documentation on how to best use the kernel's I/O interfaces, especially for corner cases. Both of the sessions, along with others in the microconference, pointed to a strong need for more interaction between user-space and kernel developers.

SQLite

Hipp went first, with a brief introduction to SQLite (which he pronounced "ess-kyew-ell lite"), its ubiquity, and how it is different from other database systems, the gist of which can be seen in his slides. He also created a briefing paper since he had far more material to discuss than there was time in the session. In many ways, his title, "What SQLite Devs Wish Linux Filesystem Devs Knew About SQLite", summed up the presentation nicely.

He noted that SQLite is "in everything and it is everywhere"; he pointed to my Sony camera (see photo) and said that he didn't know if it had SQLite in it, but that it probably did. It is in cars, phones, televisions, and more. There are more than 200 SQLite databases in each of the 2.5 billion Android phones. In aggregate, the phones are doing more than 5GB of SQLite I/O per device per day. "It's a lot of I/O."

Unlike other database systems, SQLite is effectively a library that gets embedded into applications; there is no separate server thread or process. Most databases are designed to run in data centers, but SQLite is designed to run at the edge of the network. It uses a single file to hold the entire database, though there can be journal files to support atomic commits. There is no configuration file for SQLite, so it must discover the capabilities of the underlying system at runtime.

Multiple processes can all be accessing the database at the same time in an uncoordinated fashion, he said. There are three mechanisms to provide atomicity, consistency, isolation, durability (ACID) guarantees for the database. The most universal is a rollback journal, which is also the slowest. A write-ahead log, which is faster, can be used if it is known that the database file does not reside on a network filesystem. There is no good way to determine if that is true, however.

An attendee asked about using statfs() to determine the type of the filesystem. Hipp said that could be done, but then SQLite would have to maintain a list of which types are network filesystems or not. Since SQLite is often statically linked with applications, there would be no way to update that list, he said.

SQLite can also use the atomic write capabilities of F2FS. It is a Linux-only solution, but it is "really fast", he said. He has heard reports that reformatting the filesystem on an old Android phone from ext4 to F2FS will make the handset "seem like a perky new phone". There is a clunky, ioctl()-based interface to detect the feature and to use it; it would be nice to have a more generic way to query for this kind of information.

There were a few specific items he raised in the session, but said there were many more in the paper. The first was a reliable way to query for filesystem attributes. For example, if you create a file, write to it, and then call fsync() on it, do you also have to open its directory and fsync() that in order to be sure that the file is persistent in the directory? Is that even filesystem-specific?

Kernel filesystem developer Jan Kara said that POSIX mandates the directory fsync() for persistence. Generally, filesystem developers are not willing to guarantee anything more relaxed than that because it ties their hands. As it turns out, ext4 effectively does the directory fsync() under the covers, so it is not truly necessary, at least for now. Doing the directory fsync() anyway, as SQLite does, should not be expensive if there is no concurrent activity, Kara said. That is exactly the kind of information he needed, Hipp said: authoritative information from people who know.

He also wondered if it made sense for SQLite to tell the filesystem about unused chunks of the file. At some point in the future, they would be written and then matter, but they are effectively holes that are allocated but whose contents do not matter to SQLite. While there was some thought that filesystems could use that information to send TRIM commands to SSDs, overall the belief was that it probably was not worth it. Kara said that unless the holes were gigabytes in size, it did not make sense to bother with it.

PostgreSQL

Freund launched right into a complaint that durability handling for PostgreSQL is difficult; every Linux filesystem has different behavior. Most system calls do not document what happens when an error is returned. He specifically mentioned error returns from fsync(), which were only reliably reported starting with Linux 4.13; there is still no documentation on what those errors mean and whether the operation can be sensibly retried.

Kara essentially agreed. He noted that the standards define what should happen in the normal case; POSIX does not try to define any durability guarantees. "I share your pain", he said.

Freund continued by describing another documentation flaw: durability operations like sync_file_range() come with big warnings ("This system call is extremely dangerous and should not be used in portable programs ...") that tend to steer application developers away from them. But when the application developers run into performance problems in various cases, they get pointed to sync_file_range(). Kara said the warning is there because there are no durability guarantees provided by sync_file_range(); some filesystems will durably store the range, but others will not. Freund wondered how applications are supposed to actually use the function without having to read kernel code.

In addition, the error behavior is different depending on the filesystem, block device, and kernel version. Depending on the filesystem, you will either get the old or new contents of a page after an I/O error; for NFS you may not see the error at all until the file is closed. There needs to be some documentation of what applications need to check and what they can expect; you can't "complain about people writing crappy code if that's all the guidance that they have".

An attendee asked: "how deep do you want to go?" The filesystem developers are constrained by the block layer developers who are constrained by the devices themselves. The differences between various types of storage devices are going to make guarantees difficult, they said.

Freund said that he was looking for consistency of a different type. For example, right now on a thin-provisioned block device, you can get an ENOSPC error from random system calls that do not document that return. He would be fine with filesystem and block layer errors that were consistent, but does not think Linux needs to hide or try to paper over device errors of various sorts.

In the case of failures, the behavior needs to be documented, he said. If fsync() fails and gets retried, what happens? Does the original sync operation get tried again or does the new data get thrown away? The latter is kind of what happens now in some cases, he said. Application developers have to find and read threads on the Linux kernel mailing list to figure that out.

Beyond just documenting the failure semantics, there is a need for documentation of the right way to do certain things in a safe manner. Right now that is a guessing game or something that requires talking to kernel developers over beer. The latter is nice, he said, but does not work well remotely.

Continuing in that vein, he said that there is no documentation on how to achieve durability for data. Whatever application developers do, though, some kernel developer will complain about it. If there is a performance concern, a kernel developer will say that the application is doing too much, but if the concern is data loss, then someone will complain that it is not doing enough. "Opinions will contradict each other wildly."

Another example is renaming a file atomically; what is required to ensure that it is on disk? According to some filesystem developers, it requires an fsync() of the existing file and the directory containing it, followed by the rename(), and then an fsync() of the new file and of its containing directory. There was some back and forth about whether some of those steps were actually needed, but Tomas Vondra said that PostgreSQL had settled on that sequence after extensive testing; that is what finally made the data-loss problems disappear.

Kara agreed that documentation of the sort Freund is looking for is lacking. He suggested coming up with concrete questions to post to the linux-fsdevel mailing list. The responses can be summarized into a file for the kernel documentation directory. Kara said that the atomic rename() situation is "kind of sad" and suggested that might be a good question to bring to the list.

An attendee asked if Freund was looking for the lowest common denominator because the filesystems are going to have different answers for some things. Freund said that would be fine; if there are major performance implications, it might make sense to have some filesystem-specific code. In answer to another question, Freund said that he was looking for information on what errors it would make sense to retry—which have a chance of actually succeeding if you do so?

From these two sessions and some others in the microconference, it is clear that database developers (and likely other user-space application developers) need to find ways to collaborate more with the kernel developers—and vice versa. The microconference is a great start, but more discussion on the mailing lists and over beer is needed, as is the creation of better documentation. Guidance on how to perform certain operations safely, especially with regard to file data and metadata consistency, seems to be a great starting place.

[I would like to thank LWN's travel sponsor, the Linux Foundation, for travel assistance to Lisbon for LPC.]