Re: Sequential Scan Read-Ahead - Mailing list pgsql-hackers

On Thu, 25 Apr 2002, Bruce Momjian wrote:

> Well, we are guilty of trying to push as much as possible on to other
> software.  We do this for portability reasons, and because we think our
> time is best spent dealing with db issues, not issues then can be deal
> with by other existing software, as long as the software is decent.

That's fine. I think that's a perfectly fair thing to do.

It was just the wording (i.e., "it's this other software's fault
that blah de blah") that got to me. To say, "We don't do readahead
becase most OSes supply it, and we feel that other things would
help more to improve performance," is fine by me. Or even, "Well,
nobody feels like doing it. You want it, do it yourself," I have
no problem with.

> Sure, that is certainly true.  However, it is hard to know what the
> future will hold even if we had perfect knowledge of what was happening
> in the kernel.  We don't know who else is going to start doing I/O once
> our I/O starts.  We may have a better idea with kernel knowledge, but we
> still don't know 100% what will be cached.

Well, we do if we use raw devices and do our own caching, using
pages that are pinned in RAM. That was sort of what I was aiming
at for the long run.

> We have free-behind on our list.

Uh...can't do it, if you're relying on the OS to do the buffering.
How do you tell the OS that you're no longer going to use a page?

> I think LRU-K will do this quite well
> and be a nice general solution for more than just sequential scans.

LRU-K sounds like a great idea to me, as does putting pages read
for a table scan at the LRU end of the cache, rather than the MRU
(assuming we do something to ensure that they stay in cache until
read once, at any rate).

But again, great for your own cache, but doesn't work with the OS
cache. And I'm a bit scared to crank up too high the amount of
memory I give Postgres, lest the OS try to too aggressively buffer
all that I/O in what memory remains to it, and start blowing programs
(like maybe the backend binary itself) out of RAM. But maybe this
isn't typically a problem; I don't know.

> There may be validity in this.  It is easy to do (I think) and could be
> a win.

It didn't look to difficult to me, when I looked at the code, and
you can see what kind of win it is from the response I just made
to Tom.

> >     1. It is *not* true that you have no idea where data is when
> >     using a storage array or other similar system. While you
> >     certainly ought not worry about things such as head positions
> >     and so on, it's been a given for a long, long time that two
> >     blocks that have close index numbers are going to be close
> >     together in physical storage.
>
> SCSI drivers, for example, are pretty smart.  Not sure we can take
> advantage of that from user-land I/O.

Looking at the NetBSD ones, I don't see what they're doing that's
so smart. (Aside from some awfully clever workarounds for stupid
hardware limitations that would otherwise kill performance.) What
sorts of "smart" are you referring to?

> Yes, but we are seeing some db's moving away from raw I/O.

Such as whom? And are you certain that they're moving to using the
OS buffer cache, too? MS SQL server, for example, uses the filesystem,
but turns off all buffering on those files.

> Our performance numbers beat most of the big db's already, so we must
> be doing something right.

Really? Do the performance numbers for simple, bulk operations
(imports, exports, table scans) beat the others handily? My intuition
says not, but I'll happily be convinced otherwise.

> Yes, but do we spend our time doing that.  Is the payoff worth it, vs.
> working on other features.  Sure it would be great to have all these
> fancy things, but is this where our time should be spent, considering
> other items on the TODO list?

I agree that these things need to be assesed.

> Jumping in and doing the I/O ourselves is a big undertaking, and looking
> at our TODO list, I am not sure if it is worth it right now.

Right. I'm not trying to say this is a critical priority, I'm just
trying to determine what we do right now, what we could do, and
the potential performance increase that would give us.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org   Don't you know, in this new Dark Age, we're
alllight.  --XTC