Re: [Testperf-general] Re: First set of OSDL Shared Memscalability results, some wierdness ... - Mailing list pgsql-performance

>Timothy D. Witham
> 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).

Two ways of explaining this:
1. Once you've hit the optimum size of shared_buffers, you may not yet have
hit the optimum size of the OS cache. If that is true, every extra block
given to shared_buffers is wasted, yet detracts from the beneficial effect
of the OS cache. I don't see how the small drop in size of the OS cache
could have the effect you have measured, so I suggest that this possible
explanation doesn't fit the results well.

2. There is some algorithmic effect within PostgreSQL that makes larger
shared_buffers much worse than smaller ones. Imagine that each extra block
we hold in cache has the positive benefit from caching, minus a postulated
negative drag effect. With that model we would get: Once the optimal size of
the cache has been reached the positive benefit tails off to almost zero and
we are just left with the situation that each new block added to
shared_buffers acts as a further drag on performance. That model would fit
the results, so we can begin to look at what the drag effect might be.

Speculating wildly because I don't know that portion of the code this might
be:
CONJECTURE 1: the act of searching for a block in cache is an O(n)
operation, not an O(1) or O(log n) operation - so searching a larger cache
has an additional slowing effect on the application, via a buffer cache lock
that is held while the cache is searched - larger caches are locked for
longer than smaller caches, so this causes additional contention in the
system, which then slows down performance.

The effect might show up by examining the oprofile results for the test
cases. What we would be looking for is something that is being called more
frequently with larger shared_buffers - this could be anything....but my
guess is the oprofile results won't be similar and could lead us to a better
understanding.

> >
> > 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.)

Good, our experiences seems to be similar.

>
> > 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 agree that you could test this by running on a bigger or smaller server,
i.e. one with more or less RAM. Running on a faster/slower server at the
same time might alter the results and confuse the situation.

> 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.
>

For the record, what I think we need is dynamically resizable
shared_buffers, not a-priori knowledge of what you should set shared_buffers
to. I've been thinking about implementing a scheme that helps you decide how
big the shared_buffers SHOULD BE, by making the LRU list bigger than the
cache itself, so you'd be able to see whether there is beneficial effect in
increasing shared_buffers.

...remember that this applies to other databases too, and with those we find
that they have dynamically resizable memory.

Having said all that, there are still a great many other performance tests
to run so that we CAN recommend other settings, such as the optimizer cost
parameters, bg writer defaults etc.

Best Regards,

Simon Riggs
2nd Quadrant