Postgres, fsync, and OSs (specifically linux)

Hi,

I thought I'd send this separately from [0] as the issue has become more
general than what was mentioned in that thread, and it went off into
various weeds.

I went to LSF/MM 2018 to discuss [0] and related issues. Overall I'd say
it was a very productive discussion.  I'll first try to recap the
current situation, updated with knowledge I gained. Secondly I'll try to
discuss the kernel changes that seem to have been agreed upon. Thirdly
I'll try to sum up what postgres needs to change.

== Current Situation ==

The fundamental problem is that postgres assumed that any IO error would
be reported at fsync time, and that the error would be reported until
resolved. That's not true in several operating systems, linux included.

There's various judgement calls leading to the current OS (specifically
linux, but the concerns are similar in other OSs) behaviour:

- By the time IO errors are treated as fatal, it's unlikely that plain
  retries attempting to write exactly the same data are going to
  succeed. There are retries on several layers. Some cases would be
  resolved by overwriting a larger amount (so device level remapping
  functionality can mask dead areas), but plain retries aren't going to
  get there if they didn't the first time round.
- Retaining all the data necessary for retries would make it quite
  possible to turn IO errors on some device into out of memory
  errors. This is true to a far lesser degree if only enough information
  were to be retained to (re-)report an error, rather than actually
  retry the write.
- Continuing to re-report an error after one fsync() failed would make
  it hard to recover from that fact. There'd need to be a way to "clear"
  a persistent error bit, and that'd obviously be outside of posix.
- Some other databases use direct-IO and thus these paths haven't been
  exercised under fire that much.
- Actually marking files as persistently failed would require filesystem
  changes, and filesystem metadata IO, far from guaranteed in failure
  scenarios.

Before linux v4.13 errors in kernel writeback would be reported at most
once, without a guarantee that that'd happen (IIUC memory pressure could
lead to the relevant information being evicted) - but it was pretty
likely.  After v4.13 (see https://lwn.net/Articles/724307/) errors are
reported exactly once to all open file descriptors for a file with an
error - but never for files that have been opened after the error
occurred.

It's worth to note that on linux it's not well defined what contents one
would read after a writeback error. IIUC xfs will mark the pagecache
contents that triggered an error as invalid, triggering a re-read from
the underlying storage (thus either failing or returning old but
persistent contents). Whereas some other filesystems (among them ext4 I
believe) retain the modified contents of the page cache, but marking it
as clean (thereby returning new contents until the page cache contents
are evicted).

Some filesystems (prominently NFS in many configurations) perform an
implicit fsync when closing the file. While postgres checks for an error
of close() and reports it, we don't treat it as fatal. It's worth to
note that by my reading this means that an fsync error at close() will
*not* be re-reported by the time an explicit fsync() is issued. It also
means that we'll not react properly to the possible ENOSPC errors that
may be reported at close() for NFS.  At least the latter isn't just the
case in linux.

Proposals for how postgres could deal with this included using syncfs(2)
- but that turns out not to work at all currently, because syncfs()
basically wouldn't return any file-level errors. It'd also imply
superflously flushing temporary files etc.

The second major type of proposal was using direct-IO. That'd generally
be a desirable feature, but a) would require some significant changes to
postgres to be performant, b) isn't really applicable for the large
percentage of installations that aren't tuned reasonably well, because
at the moment the OS page cache functions as a memory-pressure aware
extension of postgres' page cache.


Another topic brought up in this thread was the handling of ENOSPC
errors that aren't triggered on a filesystem level, but rather are
triggered by thin provisioning. On linux that currently apprently lead
to page cache contents being lost (and errors "eaten") in a lot of
places, including just when doing a write(). In a lot of cases it's
pretty much expected that the file system will just hang or react
unpredictably upon space exhaustion.  My reading is that the block-layer
thin provisioning code is still pretty fresh, and should only be used
with great care. The only way to halfway reliably use it appears to
change the configuration so space exhaustion blocks until admin
intervention (at least dm-thinp provides allows that).


There's some clear need to automate some more testing in this area so
that future behaviour changes don't surprise us.


== Proposed Linux Changes ==

- Matthew Wilcox proposed (and posted a patch) that'd partially revert
  behaviour to the pre v4.13 world, by *also* reporting errors to
  "newer" file-descriptors if the error hasn't previously been
  reported. That'd still not guarantee that the error is reported
  (memory pressure could evict information without open fd), but in most
  situations we'll again get the error in the checkpointer.

  This seems largely be agreed upon. It's unclear whether it'll go into
  the stable backports for still-maintained >= v4.13 kernels.

- syncfs() will be fixed so it reports errors properly - that'll likely
  require passing it an O_PATH filedescriptor to have space to store the
  errseq_t value that allows discerning already reported and new errors.

  No patch has appeared yet, but the behaviour seems largely agreed
  upon.

- Make per-filesystem error counts available in a uniform (i.e. same for
  every supporting fs) manner. Right now it's very hard to figure out
  whether errors occurred. There seemed general agreement that exporting
  knowledge about such errors is desirable. Quite possibly the syncfs()
  fix above will provide the necessary infrastructure. It's unclear as
  of yet how the value would be exposed. Per-fs /sys/ entries and an
  ioctl on O_PATH fds have been mentioned.

  These'd error counts would not vanish due to memory pressure, and they
  can be checked even without knowing which files in a specific
  filesystem have been touched (e.g. when just untar-ing something).

  There seemed to be fairly widespread agreement that this'd be a good
  idea. Much less clearer whether somebody would do the work.

- Provide config knobs that allow to define the FS error behaviour in a
  consistent way across supported filesystems. XFS currently has various
  knobs controlling what happens in case of metadata errors [1] (retry
  forever, timeout, return up). It was proposed that this interface be
  extended to also deal with data errors, and moved into generic support
  code.

  While the timeline is unclear, there seemed to be widespread support
  for the idea. I believe Dave Chinner indicated that he at least has
  plans to generalize the code.

- Stop inodes with unreported errors from being evicted. This will
  guarantee that a later fsync (without an open FD) will see the
  error. The memory pressure concerns here are lower than with keeping
  all the failed pages in memory, and it could be optimized further.

  I read some tentative agreement behind this idea, but I think it's the
  by far most controversial one.


== Potential Postgres Changes ==

Several operating systems / file systems behave differently (See
e.g. [2], thanks Thomas) than we expected. Even the discussed changes to
e.g. linux don't get to where we thought we are. There's obviously also
the question of how to deal with kernels / OSs that have not been
updated.

Changes that appear to be necessary, even for kernels with the issues
addressed:

- Clearly we need to treat fsync() EIO, ENOSPC errors as a PANIC and
  retry recovery. While ENODEV (underlying device went away) will be
  persistent, it probably makes sense to treat it the same or even just
  give up and shut down.  One question I see here is whether we just
  want to continue crash-recovery cycles, or whether we want to limit
  that.

- We need more aggressive error checking on close(), for ENOSPC and
  EIO. In both cases afaics we'll have to trigger a crash recovery
  cycle. It's entirely possible to end up in a loop on NFS etc, but I
  don't think there's a way around that.

  Robert, on IM, wondered whether there'd be a race between some backend
  doing a close(), triggering a PANIC, and a checkpoint succeeding.  I
  don't *think* so, because the error will only happen if there's
  outstanding dirty data, and the checkpoint would have flushed that out
  if it belonged to the current checkpointing cycle.

- The outstanding fsync request queue isn't persisted properly [3]. This
  means that even if the kernel behaved the way we'd expected, we'd not
  fail a second checkpoint :(. It's possible that we don't need to deal
  with this because we'll henceforth PANIC, but I'd argue we should fix
  that regardless. Seems like a time-bomb otherwise (e.g. after moving
  to DIO somebody might want to relax the PANIC...).

- It might be a good idea to whitelist expected return codes for write()
  and PANIC one ones that we did not expect. E.g. when hitting an EIO we
  should probably PANIC, to get back to a known good state. Even though
  it's likely that we'd again that error at fsync().

- Docs.

I think we also need to audit a few codepaths. I'd be surprised if we
PANICed appropriately on all fsyncs(), particularly around the SLRUs.  I
think we need to be particularly careful around the WAL handling, I
think it's fairly likely that there's cases where we'd write out WAL in
one backend and then fsync() in another backend with a file descriptor
that has only been opened *after* the write occurred, which means we
might miss the error entirely.


Then there's the question of how we want to deal with kernels that
haven't been updated with the aforementioned changes. We could say that
we expect decent OS support and declare that we just can't handle this -
given that at least various linux versions, netbsd, openbsd, MacOS just
silently drop errors and we'd need different approaches for dealing with
that, that doesn't seem like an insane approach.

What we could do:

- forward file descriptors from backends to checkpointer (using
  SCM_RIGHTS) when marking a segment dirty. That'd require some
  optimizations (see [4]) to avoid doing so repeatedly.  That'd
  guarantee correct behaviour in all linux kernels >= 4.13 (possibly
  backported by distributions?), and I think it'd also make it vastly
  more likely that errors are reported in earlier kernels.

  This should be doable without a noticeable performance impact, I
  believe.  I don't think it'd be that hard either, but it'd be a bit of
  a pain to backport it to all postgres versions, as well as a bit
  invasive for that.

  The infrastructure this'd likely end up building (hashtable of open
  relfilenodes), would likely be useful for further things (like caching
  file size).

- Add a pre-checkpoint hook that checks for filesystem errors *after*
  fsyncing all the files, but *before* logging the checkpoint completion
  record. Operating systems, filesystems, etc. all log the error format
  differently, but for larger installations it'd not be too hard to
  write code that checks their specific configuration.

  While I'm a bit concerned adding user-code before a checkpoint, if
  we'd do it as a shell command it seems pretty reasonable. And useful
  even without concern for the fsync issue itself. Checking for IO
  errors could e.g. also include checking for read errors - it'd not be
  unreasonable to not want to complete a checkpoint if there'd been any
  media errors.

- Use direct IO. Due to architectural performance issues in PG and the
  fact that it'd not be applicable for all installations I don't think
  this is a reasonable fix for the issue presented here. Although it's
  independently something we should work on.  It might be worthwhile to
  provide a configuration that allows to force DIO to be enabled for WAL
  even if replication is turned on.

- magic

Greetings,

Andres Freund

[0] https://archives.postgresql.org/message-id/CAMsr+YHh+5Oq4xziwwoEfhoTZgr07vdGG+hu=1adXx59aTeaoQ@mail.gmail.com

[1]
static const struct xfs_error_init xfs_error_meta_init[XFS_ERR_ERRNO_MAX] = {
    { .name = "default",
      .max_retries = XFS_ERR_RETRY_FOREVER,
      .retry_timeout = XFS_ERR_RETRY_FOREVER,
    },
    { .name = "EIO",
      .max_retries = XFS_ERR_RETRY_FOREVER,
      .retry_timeout = XFS_ERR_RETRY_FOREVER,
    },
    { .name = "ENOSPC",
      .max_retries = XFS_ERR_RETRY_FOREVER,
      .retry_timeout = XFS_ERR_RETRY_FOREVER,
    },
    { .name = "ENODEV",
      .max_retries = 0,    /* We can't recover from devices disappearing */
      .retry_timeout = 0,
    },
};

[2] https://wiki.postgresql.org/wiki/Fsync_Errors
[3] https://archives.postgresql.org/message-id/87y3i1ia4w.fsf%40news-spur.riddles.org.uk
[4] https://archives.postgresql.org/message-id/20180424180054.inih6bxfspgowjuc@alap3.anarazel.de