Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id F358C64FCE9 for ; Tue, 9 Dec 2008 00:00:46 -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 99953-05 for ; Tue, 9 Dec 2008 00:00:43 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from westnet.com (westnet.com [216.187.52.2]) by mail.postgresql.org (Postfix) with ESMTP id 05E4C64FC02 for ; Tue, 9 Dec 2008 00:00:41 -0400 (AST) Received: from westnet.com (localhost [127.0.0.1]) by westnet.com (8.14.0/8.14.0) with ESMTP id mB940cwq019842; Mon, 8 Dec 2008 23:00:38 -0500 (EST) Received: from localhost (gsmith@localhost) by westnet.com (8.14.0/8.13.2/Submit) with ESMTP id mB940cdr019837; Mon, 8 Dec 2008 23:00:38 -0500 (EST) X-Authentication-Warning: westnet.com: gsmith owned process doing -bs Date: Mon, 8 Dec 2008 23:00:38 -0500 (EST) From: Greg Smith X-X-Sender: gsmith@westnet.com To: Merlin Moncure cc: Scott Marlowe , Josh Berkus , pgsql-performance@postgresql.org Subject: Re: Need help with 8.4 Performance Testing In-Reply-To: Message-ID: References: <200812071138.01215.josh@agliodbs.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed 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/62 X-Sequence-Number: 32098 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. 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. Since the data is about 7.5X as large as the indexes, that point is way further out than the basic bottlenecks. And if you graph pgbench results on a scale that usefully shows the results for in-memory TPS scores, you can barely see that part of the chart a well. One day I may get to mapping that out better, and if I do it will be interesting to see if the balance of shared_buffers to OS cache works the way I expect. I was waiting until I finished the pgtune program for that, that's building some of the guts I wanted to make it easier to tweak postgresql.conf settings programmatically in between pgbench runs. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD