Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id CDBA36504F4 for ; Tue, 9 Dec 2008 09:08:04 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 84936-04-6 for ; Tue, 9 Dec 2008 09:07:59 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from ey-out-2122.google.com (ey-out-2122.google.com [74.125.78.26]) by mail.postgresql.org (Postfix) with ESMTP id BEEBF650531 for ; Tue, 9 Dec 2008 09:07:08 -0400 (AST) Received: by ey-out-2122.google.com with SMTP id 6so645236eyi.61 for ; Tue, 09 Dec 2008 05:07:05 -0800 (PST) Received: by 10.210.126.18 with SMTP id y18mr122310ebc.44.1228827725692; Tue, 09 Dec 2008 05:02:05 -0800 (PST) Received: from oxford.xeocode.com.enterprisedb.com ([82.16.16.210]) by mx.google.com with ESMTPS id h7sm12885954nfh.65.2008.12.09.05.02.03 (version=TLSv1/SSLv3 cipher=RC4-MD5); Tue, 09 Dec 2008 05:02:04 -0800 (PST) To: Greg Smith Cc: Merlin Moncure , Scott Marlowe , Josh Berkus , pgsql-performance@postgresql.org Subject: Re: Need help with 8.4 Performance Testing In-Reply-To: (Greg Smith's message of "Mon\, 8 Dec 2008 23\:00\:38 -0500 \(EST\)") User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux) X-Draft-From: ("nnimap+imap.gmail.com:PG" 99691) References: <200812071138.01215.josh@agliodbs.com> From: Gregory Stark Organization: EnterpriseDB Date: Tue, 09 Dec 2008 13:02:02 +0000 Message-ID: <87y6ypijsl.fsf@oxford.xeocode.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200812/66 X-Sequence-Number: 32102 Greg Smith writes: > On Mon, 8 Dec 2008, Merlin Moncure wrote: > >> I wonder if shared_buffers has any effect on how far you can go before >> you hit the 'tipping point'. > > If your operating system has any reasonable caching itself, not so much at > first. As long as the index on the account table fits in shared_buffers, even > the basic sort of caching logic an OS uses is perfectly functional for swapping > the individual pages of the account table in and out, the main limiting factor > on pgbench performance. I would expect higher shared_buffers to raise the curve before the first breakpoint but after the first breakpoint make the drop steeper and deeper. The equilibrium where the curve becomes flatter should be lower. That is, as long as the database fits entirely in RAM having more of the buffers be immediately in shared buffers is better. Once there's contention for the precious cache stealing some of it for duplicated buffers will only hurt. > There is a further out tipping point I've theorized about but not really > explored: the point where even the database indexes stop fitting in memory > usefully. As you get closer to that, I'd expect that the clock sweep algorithm > used by shared_buffers should make it a bit more likely that those important > blocks would hang around usefully if you put them there, rather than giving > most of the memory to the OS to manage. Hm, sounds logical. At that point the slow drop-off should become even shallower and possibly become completely flat. Greater shared_buffers might start helping again at that point. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services!