X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 1FFDF475F16 for ; Thu, 10 Apr 2003 10:26:42 -0400 (EDT) Received: from beamish.nsd.ca (beamish.nsd.ca [205.150.156.194]) by postgresql.org (Postfix) with ESMTP id 9EB0D475CEE for ; Thu, 10 Apr 2003 10:26:34 -0400 (EDT) Received: (from smap@localhost) by beamish.nsd.ca (8.9.3/8.9.3) id KAA12467; Thu, 10 Apr 2003 10:26:12 -0400 X-Authentication-Warning: beamish.nsd.ca: smap set sender to using -f Received: from reddog.nsd.ca(192.168.101.30) by beamish.nsd.ca via smap (V2.1/2.1+anti-relay+anti-spam) id xma012460; Thu, 10 Apr 03 10:25:42 -0400 Received: from nsd.ca (jllachan-linux.nsd.ca [192.168.101.148]) by reddog.nsd.ca (8.8.7/8.8.7) with ESMTP id KAA01073; Thu, 10 Apr 2003 10:20:26 -0400 Message-ID: <3E957F44.A1106F6A@nsd.ca> Date: Thu, 10 Apr 2003 10:27:16 -0400 From: Jean-Luc Lachance X-Mailer: Mozilla 4.77 [en] (X11; U; Linux 2.4.18-24.7.x i686) X-Accept-Language: en MIME-Version: 1.0 To: Tom Lane Cc: jim@nasby.net, "scott.marlowe" , Matthew Nuzum , "'Josh Berkus'" , "'Pgsql-Performance'" Subject: Re: Caching (was Re: choosing the right platform) References: <00a001c2febb$631141d0$6900a8c0@mattspc> <20030409184744.W31861@flake.decibel.org> <11732.1049934037@sss.pgh.pa.us> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Spam-Status: No, hits=-30.7 required=5.0 tests=BAYES_01,EMAIL_ATTRIBUTION,QUOTED_EMAIL_TEXT,REFERENCES, REPLY_WITH_QUOTES,X_AUTH_WARNING autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/107 X-Sequence-Number: 1613 Tom, What appends when PG scans a table that is is too big to fit in the cache? Won't the whole cache get trashed and swapped off to disk? Shouldn't there be a way to lock some tables in PG cache? Who about caracterizing some of the RAM like: scan, index, small frequently used tables. JLL Tom Lane wrote: > [...] > PG is *not* any smarter about the usage patterns of its disk buffers > than the kernel is; it uses a simple LRU algorithm that is surely no > brighter than what the kernel uses. (We have looked at smarter buffer > recycling rules, but failed to see any performance improvement.) So the > notion that PG can do a better job of cache management than the kernel > is really illusory. About the only advantage you gain from having data > directly in PG buffers rather than kernel buffers is saving the CPU > effort needed to move data across the userspace boundary --- which is > not zero, but it's sure a lot less than the time spent for actual I/O. > > So my take on it is that you want shared_buffers fairly small, and let > the kernel do the bulk of the heavy lifting for disk cache. That's what > it does for a living, so let it do what it does best. You only want > shared_buffers big enough so you don't spend too many CPU cycles shoving > data back and forth between PG buffers and kernel disk cache. The > default shared_buffers setting of 64 is surely too small :-(, but my > feeling is that values in the low thousands are enough to get past the > knee of that curve in most cases.