PostgreSQL pain points | Hacker News Reader

PostgreSQL pain points

lwn.net

113 pointssciurus14 comments

I must be missing something. Why doesn't PostgreSQL use O_DIRECT when doing non-journaled writes to the data blocks? I know there are alignment restrictions on the userspace buffers used to do the reads and writes, but these don't seem insurmountable, and the payoff is huge. Is it because it's not POSIX?

how can you change mount option online?

Using O_DIRECT well requires taking on a surprising amount of additional resource management responsibility. Also, the implementation is not portable and, combined with the additional responsibility previously mentioned, adds a ton of conditional compilation. The alignment requirements are not an issue at all; anyone using O_DIRECT is also going to be used properly aligned I/O buffers in any case.

O_DIRECT can have a lot of benefits. However, while O_DIRECT may be simple to use by itself, the other design and implementation requirements that are indirectly dragged along are not trivial by any means. Consequently, you should not be using O_DIRECT unless you can manage the substantial indirect overhead in implementation. It turns off a lot of OS features most developers take for granted.

Boo hoo. If that's what it takes to perform, then it should be done. Ingres had it in the 90's. A win for OLTP, sometimes a lose for DSS queries with scans.

You've essentially just waved your hands. Conditional complication is not an issue at all; anyone working on a large portable program is going to have large swaths of conditionally compiled code. Phrases like "resource management responsibility" and "substantial indirect overhead" are pretty vague, and aren't really helping me understand why O_DIRECT can't be used.

It turns off a lot of OS features most developers take for granted.

Yeah, like OS buffering. What else?

I use O_DIRECT almost exclusively in the software I write; I am familiar with the overhead. You can use O_DIRECT very easily without a lot of overhead if you wish but the performance will be much worse than not using it unless you competently reimplement many other parts of the OS functionality at the same time.

To use O_DIRECT effectively in Linux, at a minimum you need to write a buffer cache that is both fast and adaptive to workloads with proper cache replacement schedulers (no LRU crap), you need to write a complete I/O scheduler replacement (e.g. using io_submit and related interfaces) which is not portable at all, and you need to stop using almost all file system APIs (it interferes with the I/O scheduling bypass).

O_DIRECT is easy to use for very narrow cases. Taking advantage of it for anything slightly complicated in terms of file I/O usage and you bite off a lot more low-level implementation or the performance will actually be worse.

I'd love to see an objective real world comparison of Linux kernel vs FreeBSD kernel and ZFS with postgresql. I've got a (not terribly busy) box to build for someone and I'm torn between the two.

Why does the Linux kernel let the disk sit idle before the fsync? Why does fsync cause such a storm, if the kernel could write as it goes? Also, why does PostgreSQL do buffered writes instead of unbuffered? Seems like two wrongs make a wrong here...

Write-as-you-go isn't necessarily an optimal strategy for general purpose workloads with intermittent IO - batching writes lets you do more linear writes, do a better job of allocating blocks, hit the journal less often...

There is a need for an alternate API, but it's not like fsync is "broken" for the general case.

fsync has been broken since it was introduced in BSD4.x. It's never worked well, usually flushing the whole machine cache in one huge batch of i/o. I'm told current versions on Linux will restrict it to the one open file, but it's still a shotgun instead of a rifle.

The disk is almost certainly not idle, but instead the iops are consumed by read traffic.

Ideally, the kernel would know the deadline by which the data should be committed, manage accordingly, and the fsync would be simply acknowledged rather than inspiring extra IO.

This is pretty hard to work towards and the clearly specified narrow syscall interface leads to a great deal of difficulty in sharing this information with the kernel IO scheduling -- the way it is shared would naturally depend on the details of the kernel implementation, which would imply a tighter link between application and kernel version than is normally desirable.

The things that want sync don't usually want a promise for future time, they want positive ack the data is on the storage. So a deadline approach is pointless.

Async w/O_DIRECT is the correct approach, despite the complexity. The problem is handling table scans that exploit system buffer cache for read-ahead. The answer to that is to issue more i/os to bigger buffers, or to a set of buffers. Scatter-gather disk i/o would help with that.

It might be particularly sweet if well-behaved applications could rely on mixing O_DIRECT with bufferd i/o on the same files. That is, all writes done O_DIRECT on pages read with O_DIRECT, and reads might be done against page-cached buffered files.

I wonder if running on systems with NV cache controllers experience the issues with the slow fsync.

Yes, fsync typically chews a boatload of CPU running down the pages in the cache to see what pages need to be written, then all the i/o path to enqueue the writes.

Reply on news.ycombinator.com