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 40F8DD1B1A5 for ; Sun, 11 Jul 2004 14:59:22 -0300 (ADT) 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 05286-02 for ; Sun, 11 Jul 2004 17:59:19 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 35D1CD1B1CE for ; Sun, 11 Jul 2004 14:59:18 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i6BHxHi7080425 for ; Sun, 11 Jul 2004 17:59:17 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i6BHswf6079680 for pgsql-performance@postgresql.org; Sun, 11 Jul 2004 17:54:58 GMT From: Christopher Browne X-Newsgroups: comp.databases.postgresql.performance Subject: Re: Working on huge RAM based datasets Date: Sun, 11 Jul 2004 13:04:44 -0400 Organization: cbbrowne Computing Inc Lines: 35 Message-ID: References: <6EE64EF3AB31D5448D0007DD34EEB34101AEC2@Herge.rcsinc.local> <40F14ADE.4080402@Yahoo.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org X-message-flag: Outlook is rather hackable, isn't it? X-Home-Page: http://www.cbbrowne.com/info/ X-Affero: http://svcs.affero.net/rm.php?r=cbbrowne User-Agent: Gnus/5.1006 (Gnus v5.10.6) XEmacs/21.4 (Security Through Obscurity, linux) Cancel-Lock: sha1:iJbLjk/dfq2t2ce8PF7GBsuPEGE= To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200407/60 X-Sequence-Number: 7451 Martha Stewart called it a Good Thing when JanWieck@Yahoo.com (Jan Wieck) wrote: > On 7/9/2004 10:16 AM, Merlin Moncure wrote: >>> What is it about the buffer cache that makes it so unhappy being >>> able to hold everything? I don't want to be seen as a cache hit >>> fascist, but isn't it just better if the data is just *there*, >>> available in the postmaster's address space ready for each backend >>> process to access it, rather than expecting the Linux cache >>> mechanism, optimised as it may be, to have to do the caching? >> The disk cache on most operating systems is optimized. Plus, >> keeping shared buffers low gives you more room to bump up the sort >> memory, which will make your big queries run faster. > Plus, the situation will change dramatically with 7.5 where the disk > cache will have less information than the PG shared buffers, which > will become sequential scan resistant and will know that a block was > pulled in on behalf of vacuum and not because the regular database > access pattern required it. It'll be very curious how this changes things. I _think_ it means that shared buffer usage becomes more efficient both for small and large buffers, since vacuums and seq scans shouldn't "eviscerate" the shared buffers the way they can in earlier versions. What would be most interesting to see is whether this makes it wise to increase shared buffer size. It may be more effective to bump down the cache a little, and bump up sort memory; hard to tell. -- let name="cbbrowne" and tld="ntlug.org" in String.concat "@" [name;tld];; http://cbbrowne.com/info/spreadsheets.html "But life wasn't yes-no, on-off. Life was shades of gray, and rainbows not in the order of the spectrum." -- L. E. Modesitt, Jr., _Adiamante_