pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Timothy D. Witham <wookie@osdl.org>
To: josh@agliodbs.com
Cc: pgsql-performance@postgresql.org, Simon Riggs <simon@2ndquadrant.com>
Cc: testperf-general@pgfoundry.org
Subject: Re: [Testperf-general] Re: First set of OSDL Shared Mem
Date: Thu, 14 Oct 2004 17:09:05 -0700
Message-ID: <1097798945.6387.86.camel@wookie-zd7> (raw)
In-Reply-To: <200410141657.45415.josh@agliodbs.com>
References: <NOEFLCFHBPDAFHEIPGBOEEDKCGAA.simon@2ndquadrant.com>
	<200410141657.45415.josh@agliodbs.com>

On Thu, 2004-10-14 at 16:57 -0700, Josh Berkus wrote:
> Simon,
> 
> <lots of good stuff clipped>
> 
> > 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)




Message-ID: <1097798945.6387.86.camel@wookie-zd7>
Permalink:  ../1097798945.6387.86.camel@wookie-zd7/
Also on:    postgresql.org/message-id/1097798945.6387.86.camel@wookie-zd7

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: wookie@osdl.org, josh@agliodbs.com, simon@2ndquadrant.com, testperf-general@pgfoundry.org
  Subject: Re: [Testperf-general] Re: First set of OSDL Shared Mem
  In-Reply-To: <1097798945.6387.86.camel@wookie-zd7>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox