Hacker News Reader

Comment by dooglius | Hacker News Reader

> To understand it better, consider an example of Linux trying to write dirty pages from page cache to a USB stick that was removed during an fsync. Neither the ext4 file system nor the btrfs nor an xfs tries to retry the failed writes. A silently failing fsync may result in data loss, block corruption, table or index out of sync, foreign key or other data integrity issues… and deleted records may reappear.

As opposed to what? If the drive isn't there anymore, there's not a whole lot that can be done.

> With the new minor version for all supported PostgreSQL versions, a PANIC is triggered upon such error. This performs a database crash and initiates recovery from the last CHECKPOINT.

How is a recovery possible if the hard drive is borked? I don't understand the model that leads to this "fix" making any difference.

View on HN

The difference is that with the fix, PostgreSQL should not silently lose data (confirmed transactions).

What was possible before is:

1) transaction committed OK (which involves fsync of WAL, but that's it)

2) during checkpoint (essentially asynchronous writes to data files in the background), something went wrong and OS just discarded the dirty pages on fsync()

3) PostgreSQL assumed it can retry the fsync and the pages will be written again

So in the end, the contents of data files mismatched what's in WAL.

What this change does is it "crashes" the database after step (2), forcing the database into a recovery which re-applies the writes done in (1) from WAL. Of course, if the I/O error is permanent, this this won't change a thing. But PostgreSQL won't lie to you returning stale data etc.

Note: This assumes all the layers (notably kernel + filesystem) do the right thing, i.e. report errors reliably. That may or may not be the case, depending on the kernel version etc.

A better example might be a SAN briefly becoming unavailable due to a transient issue with your ISCSI network?

Yeah, this more or less clears it up, I was assuming an error implied disk failure.

Right, this is about "ephemeral" failures which are becoming more common thanks to accessing storage over network, virtualization, thin provisioning etc.

NFS has been around forever and has always had a bad reputation due to problems like this. It mostly handles transient failures by waiting (indefinitely) for the server to return, but it's unclear what a better option is.

And that likely "hid" this issue for quite a long time, according to Tomas Vondra (https://youtu.be/1VWIGBQLtxo): data loss would just be blamed on NFS being NFS and kinda crappy, and not necessarily properly investigated in full (why waste time on NFS shitting the bed yeah?), but it's likely the incorrect checkpointing / fsync assumptions were the culprit in at least some of the issues.

Though an other factor is that people now run a lot more DBs, on a lot more environments, with a lot less reliability, and concurrently the database improved, so things which were rare and lost in the noise when run on "big iron" with expensive drive controllers become visible signal.

FWIW here is a standalone test that shows Linux NFS exhibiting behaviour that would corrupt a PostgreSQL database:

https://www.postgresql.org/message-id/CAEepm=1FGo=ACPKRmAxvb...

You can also tweak that test so that ENOSPC is discovered at close() time. Now you have a system that has thrown away data that PostgreSQL has already evicted from its own buffers, and there is no way to get it back (other than replaying the WAL, which is what PANIC achieves, as unpleasant a solution as it is, especially if it just happens again, and again, ...).

The recent change in 11.2 adds a PANIC on error there. But I'm not sure it's sufficient in Linux NFS, because even on the tip of the master branch of Linux (by my inexpert drive-by reading, at least), the errseq_t stuff doesn't seem to have made it into the NFS client code, so it's still using the old single AS_EIO flag. That probably exposes at least one race that is discussed in this thread:

https://www.postgresql.org/message-id/flat/CA%2BhUKGKa-HtBHJ...

I think we need to do something to make space allocation eager for NFS clients (a couple of concrete approaches are discussed) so that ENOSPC is excluded as a possibility after we have evicted data from PostgreSQL's buffer, and then I think we need Linux NFS to adopt errseq_t behaviour, and PostgreSQL to adopt the "fd passing" design discussed on the pgsql-hackers mailing list (to make sure the checkpointer's file descriptor is old enough to see all relevant IO errors). Or we need direct IO.

TL;DR We are not out of the woods on NFS.

Why would you run a database on NFS?

Well, I wouldn't. But people do. It makes more sense to use a SAN IMHO. I'm told it's not uncommon to use NFS for Oracle. One interesting thing is that they have their own NFS client implementation instead of trusting the kernel (they also do direct IO by default, though I'm not actually sure whether their NFS or DIO support came first).

> How is a recovery possible if the hard drive is borked? I don't understand the model that leads to this "fix" making any difference.

If hard drive is broken it is broken and it is not really PostgreSQL fault. This is more about losing access to hard drive, for example broken SATA cable etc.

The issue here is to properly handle fsync() . Until recently, PostgreSQL assumed that if fsync() call fails and the call is retired and succeeds, then everything is A-OK. So after that PostgreSQL removed the "successful" transaction from WAL and continue to process next transaction.

Turned out that on some systems such as Linux if a fsync() fails, then subsequent fsync() succeeds doesn't mean that all data from both calls was written successfully, so this change makes PostgreSQL panic on first fsync() failure and PostgreSQL refusing to continue. This means that you still will have uncommitted transactions in WAL log and you can use that to recover data when the disk starts to work again.

> Turned out that on some systems such as Linux

Most systems really, keeping the buffers dirty is the exception (FreeBSD, possibly DragonflyBSD, Illumos, possibly Solaris).

OpenBSD recently changed some of their behaviour, but apparently only so fsync doesn't lose the error: https://marc.info/?l=openbsd-cvs&m=155044187426287&w=2 NetBSD and macOS remain on the old BSD behaviour (discard the buffers, second fsync will come back clean)

This issue is observed during a CHECHKPOINT when a BGWriter writes dirty or modified buffers from shared buffers to Disk. So, upon recovery after crash, the changes since the last checkpoint are applied from WAL, this time. So, we are using the changes that are in a WAL (Write-ahead-log) because the changes are already gone from Disk as you rightly said. A good question in fact.

nitpick: BGWrites is not involved in checkpoint - it's a separate process, doing something like checkpoint (so an alternative).

Reply on news.ycombinator.com