Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id D5E86476CC6 for ; Mon, 27 Jan 2003 16:10:56 -0500 (EST) Received: from candle.pha.pa.us (momjian.navpoint.com [207.106.42.251]) by postgresql.org (Postfix) with ESMTP id C8DA1476B62 for ; Mon, 27 Jan 2003 16:09:04 -0500 (EST) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.10.1) id h0RL8xA05911; Mon, 27 Jan 2003 16:08:59 -0500 (EST) From: Bruce Momjian Message-Id: <200301272108.h0RL8xA05911@candle.pha.pa.us> Subject: Re: 7.3.1 New install, large queries are slow In-Reply-To: <1043680648.9896.24.camel@haggis> To: Ron Johnson Date: Mon, 27 Jan 2003 16:08:59 -0500 (EST) Cc: PgSQL Performance ML X-Mailer: ELM [version 2.4ME+ PL99 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/359 X-Sequence-Number: 1006 Detecting sequential scan and increasing read-ahead is a standard OS capability, and most/all do that already. Solaris has code to detect when a sequential scan is wiping the cache and adjusting the buffer frees, called "free-behind." --------------------------------------------------------------------------- Ron Johnson wrote: > On Mon, 2003-01-27 at 04:34, Curt Sampson wrote: > > On Mon, 27 Jan 2003, Sean Chittenden wrote: > > > > > The FreeBSD VM caching system does prevent one process from exhausting > > > another process's cached data due to a sequential access, but the > > > FreeBSD VM cache does not try to outsmart sequential data accesses to > > > datasets which are larger then available cache space because it's an > > > insanely difficult (impossible) problem to solve properly without > > > foreknowledge of what data elements will be accessed when. > > > > This is not impossible; Solaris does just this. I'm a little short of > > Quite. One way to do it is: > - the OS notices that process X has been sequentially reading thru > file Y for, say, 3 seconds. > - the OS knows that X is currently at the mid-point of file Y > - OS says, "Hey, I think I'll be a bit more agressive about, when I > have a bit of time, trying to read Y faster than X is requesting > it > > It wouldn't work well, though, in a client-server DB like Postgres, > which, in a busy multi-user system, is constantly hitting different > parts of different files. > > The algorithm, though, is used in the RDBMS Rdb. It uses the algorithm > above, substituting "process X" for "client X", and passes the agressive > reads of Y on to the OS. It's a big win when processing a complete > table, like during a CREATE INDEX, or "SELECT foo, COUNT(*)" where > there's no index on foo. > > -- > +---------------------------------------------------------------+ > | Ron Johnson, Jr. mailto:ron.l.johnson@cox.net | > | Jefferson, LA USA http://members.cox.net/ron.l.johnson | > | | > | "Fear the Penguin!!" | > +---------------------------------------------------------------+ > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073