PostgreSQL pain points

by sciurus • 12 years ago

113 14 12 years ago

batbomb• 12 years ago

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

dbrower4• 12 years ago

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.

jwatte• 12 years ago

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...

fiatmoney• 12 years ago

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.

dbrower4• 12 years ago

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.

vii• 12 years ago

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.

dbrower4• 12 years ago

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.

spudlyo2• 12 years ago

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?

JSno• 12 years ago

how can you change mount option online?

jandrewrogers2• 12 years ago

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.

dbrower4• 12 years ago

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.

spudlyo2• 12 years ago

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?

bananas• 12 years ago

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.

PostgreSQL pain points

Related Stories