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 9198C32A90A for ; Fri, 15 Oct 2004 01:09:03 +0100 (BST) 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 08770-03 for ; Fri, 15 Oct 2004 00:08:56 +0000 (GMT) Received: from mail.osdl.org (fw.osdl.org [65.172.181.6]) by svr1.postgresql.org (Postfix) with ESMTP id 3589332A828 for ; Fri, 15 Oct 2004 01:08:57 +0100 (BST) Received: from localhost (osdlab.pdx.osdl.net [172.20.1.28]) by mail.osdl.org (8.11.6/8.11.6) with ESMTP id i9F08t916459; Thu, 14 Oct 2004 17:08:55 -0700 Subject: Re: [Testperf-general] Re: First set of OSDL Shared Mem From: "Timothy D. Witham" To: josh@agliodbs.com Cc: pgsql-performance@postgresql.org, Simon Riggs , testperf-general@pgfoundry.org In-Reply-To: <200410141657.45415.josh@agliodbs.com> References: <200410141657.45415.josh@agliodbs.com> Content-Type: text/plain Organization: Open Source Development Lab, Inc. Date: Thu, 14 Oct 2004 17:09:05 -0700 Message-Id: <1097798945.6387.86.camel@wookie-zd7> Mime-Version: 1.0 X-Mailer: Evolution 2.0.0 Content-Transfer-Encoding: 7bit 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: 200410/200 X-Sequence-Number: 8676 On Thu, 2004-10-14 at 16:57 -0700, Josh Berkus wrote: > Simon, > > > > > If you draw a graph of speedup (y) against cache size as a > > % of total database size, the graph looks like an upside-down "L" - i.e. > > the graph rises steeply as you give it more memory, then turns sharply at a > > particular point, after which it flattens out. The "turning point" is the > > "sweet spot" we all seek - the optimum amount of cache memory to allocate - > > but this spot depends upon the worklaod and database size, not on available > > RAM on the system under test. > > Hmmm ... how do you explain, then the "camel hump" nature of the real > performance? That is, when we allocated even a few MB more than the > "optimum" ~190MB, overall performance stated to drop quickly. The result is > that allocating 2x optimum RAM is nearly as bad as allocating too little > (e.g. 8MB). > > The only explanation I've heard of this so far is that there is a significant > loss of efficiency with larger caches. Or do you see the loss of 200MB out > of 3500MB would actually affect the Kernel cache that much? > In a past life there seemed to be a sweet spot around the applications working set. Performance went up until you got just a little larger than the cache needed to hold the working set and then went down. Most of the time a nice looking hump. It seems to have to do with the additional pages not increasing your hit ratio but increasing the amount of work to get a hit in cache. This seemed to be independent of the actual database software being used. (I observed this running Oracle, Informix, Sybase and Ingres.) > Anyway, one test of your theory that I can run immediately is to run the exact > same workload on a bigger, faster server and see if the desired quantity of > shared_buffers is roughly the same. I'm hoping that you're wrong -- not > because I don't find your argument persuasive, but because if you're right it > leaves us without any reasonable ability to recommend shared_buffer settings. > -- Timothy D. Witham - Chief Technology Officer - wookie@osdl.org Open Source Development Lab Inc - A non-profit corporation 12725 SW Millikan Way - Suite 400 - Beaverton OR, 97005 (503)-626-2455 x11 (office) (503)-702-2871 (cell) (503)-626-2436 (fax)