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 B074D8BA16F for ; Tue, 15 Feb 2005 08:11:12 +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 99361-07 for ; Tue, 15 Feb 2005 08:10:53 +0000 (GMT) Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162]) by svr1.postgresql.org (Postfix) with ESMTP id 99D508BA151 for ; Tue, 15 Feb 2005 08:10:51 +0000 (GMT) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1D0xn5-0000rN-00; Tue, 15 Feb 2005 03:10:39 -0500 To: Tom Lane Cc: Greg Stark , Mark Aufflick , pgsql-performance@postgresql.org Subject: Re: seq scan cache vs. index cache smackdown References: <87d5v247z8.fsf@stark.xeocode.com> <9326.1108452962@sss.pgh.pa.us> In-Reply-To: <9326.1108452962@sss.pgh.pa.us> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 15 Feb 2005 03:10:39 -0500 Message-ID: <87vf8u2qgw.fsf@stark.xeocode.com> Lines: 41 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.056 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200502/235 X-Sequence-Number: 10564 Tom Lane writes: > Greg Stark writes: > > 8.0, on the other hand, has a new algorithm that specifically tries to > > protect against the shared buffers being blown out by a sequential > > scan. But that will only help if it's the shared buffers being > > thrashed that's hurting you, not the entire OS file system cache. > > Something we ought to think about sometime: what are the performance > implications of the real-world situation that we have another level of > caching sitting underneath us? 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. Every other database eventually goes this direction, and for good reason. Having two layers of caching and buffering is inherently inefficient. It also makes it impossible for Postgres to offer any application-specific hints to the caching replacement algorithms. In that world you would configure Postgres much like you configure Oracle, with shared_buffers taking up as much of your memory as you can afford. And the OS file system cache is kept entirely out of the loop. > AFAIK all the theoretical studies we've looked at consider only a single > level of caching. 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. Hum. Is it clear that that's bad? By the same logic it's the ones on the fringe that you're likely to have to read again anyways. The ones that are being heavily used are likely not to have to be read again anyways. -- greg