X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 211C68BA1DD for ; Tue, 15 Feb 2005 17:23:37 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 84949-03 for ; Tue, 15 Feb 2005 17:23:33 +0000 (GMT) Received: from davinci.ethosmedia.com (server227.ethosmedia.com [209.128.84.227]) by svr1.postgresql.org (Postfix) with ESMTP id A4E9D8BA0F3 for ; Tue, 15 Feb 2005 17:23:32 +0000 (GMT) X-EthosMedia-Virus-Scanned: no infections found Received: from [63.195.55.98] (account josh@agliodbs.com HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.1.8) with ESMTP id 7031340; Tue, 15 Feb 2005 09:25:17 -0800 From: Josh Berkus Organization: Aglio Database Solutions To: Greg Stark Subject: Re: seq scan cache vs. index cache smackdown Date: Tue, 15 Feb 2005 09:22:59 -0800 User-Agent: KMail/1.6.2 Cc: Tom Lane , Mark Aufflick , pgsql-performance@postgresql.org References: <9326.1108452962@sss.pgh.pa.us> <87vf8u2qgw.fsf@stark.xeocode.com> In-Reply-To: <87vf8u2qgw.fsf@stark.xeocode.com> MIME-Version: 1.0 Content-Disposition: inline Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Message-Id: <200502150922.59176.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.012 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200502/239 X-Sequence-Number: 10568 Tom, Greg, Merlin, > But for example, > if our buffer management algorithm recognizes an index page as being > heavily hit and therefore keeps it in cache for a long time, then when > it does fall out of cache you can be sure it's going to need to be read > from disk when it's next used, because the OS-level buffer cache has not > seen a call for that page in a long time. Contrariwise a page that we > think is only on the fringe of usefulness is going to stay in the OS > cache because we repeatedly drop it and then have to ask for it again. Now you can see why other DBMSs don't use the OS disk cache. There's other= =20 issues as well; for example, as long as we use the OS disk cache, we can't= =20 eliminate checkpoint spikes, at least on Linux. No matter what we do with= =20 the bgwriter, fsyncing the OS disk cache causes heavy system activity. > It seems inevitable that Postgres will eventually eliminate that redundant > layer of buffering. Since mmap is not workable, that means using O_DIRECT > to read table and index data. Why is mmap not workable? It would require far-reaching changes to our c= ode=20 =2D- certainly -- but I don't think it can be eliminated from consideration. > What about going the other way and simply letting the o/s do all the > caching? =A0How bad (or good) would the performance really be? =A0 Pretty bad. You can simulate this easily by turning your shared_buffers w= ay=20 down ... =2D-=20 Josh Berkus Aglio Database Solutions San Francisco