Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 88650475E1E for ; Mon, 27 Jan 2003 20:42:41 -0500 (EST) Received: from lakemtao02.cox.net (lakemtao02.cox.net [68.1.17.243]) by postgresql.org (Postfix) with ESMTP id 7BFBD475D64 for ; Mon, 27 Jan 2003 20:42:40 -0500 (EST) Received: from [192.168.1.1] ([68.11.66.83]) by lakemtao02.cox.net (InterMail vM.5.01.04.05 201-253-122-122-105-20011231) with ESMTP id <20030128014242.RREX6744.lakemtao02.cox.net@[192.168.1.1]> for ; Mon, 27 Jan 2003 20:42:42 -0500 Subject: Re: 7.3.1 New install, large queries are slow From: Ron Johnson To: PgSQL Performance ML In-Reply-To: <200301272108.h0RL8xA05911@candle.pha.pa.us> References: <200301272108.h0RL8xA05911@candle.pha.pa.us> Content-Type: text/plain Organization: Message-Id: <1043718161.9896.120.camel@haggis> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.1 Date: 27 Jan 2003 19:42:41 -0600 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/365 X-Sequence-Number: 1012 On Mon, 2003-01-27 at 15:08, Bruce Momjian wrote: > 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." Ah, didn't know that. > --------------------------------------------------------------------------- > > 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!!" | +---------------------------------------------------------------+