Skip site navigation (1) Skip section navigation (2)

Re: proposal: Set effective_cache_size to greater of .conf value, shared_buffers

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Claudio Freire <klaussfreire(at)gmail(dot)com>,Merlin Moncure <mmoncure(at)gmail(dot)com>,PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: proposal: Set effective_cache_size to greater of .conf value, shared_buffers
Date: 2013-01-08 22:48:55
Message-ID: 20130108224855.GE8789@momjian.us (view raw or flat)
Thread:
Lists: pgsql-hackers
On Tue, Jan  8, 2013 at 05:23:36PM -0500, Robert Haas wrote:
> > Rather, I'd propose the default setting should be "-1" or something
> > "default" and "automagic" that works most of the time (but not all).
> 
> +1.  I've found that a value of three-quarters of system memory works
> pretty well most of the time.  Of course, there's not a single,
> portable way of getting that on every platform we support.  If I
> remember my last investigation into this area, to use that particular
> rule we would probably need at least three paths - one for Windows,
> one for System V descendents, and one for BSD descendents.  And there
> might still be obscure things that wouldn't be covered.  Of course
> this also makes the admittedly unwarranted assumption that the
> database owns the box, which could be wrong too, but purposely
> lowballing effective_cache_size to discourage index-scan plans seems
> unlikely to be a winning strategy.
> 
> A cruder heuristic that might be useful is 3 * shared_buffers.  If
> people follow the guidance to set shared_buffers around 25% of RAM,
> then this will work out to around 75% again.  Of course, many people,
> for valid reasons, use smaller values of shared_buffers than that, and
> a few use larger ones.  It might still be better than no auto-tuning,
> though I wouldn't swear to it.

Agreed.  This is similar to the fudge we have about random_page_cost:

        Random access to mechanical disk storage is normally much more expensive
        than four-times sequential access.  However, a lower default is used
        (4.0) because the majority of random accesses to disk, such as indexed
        reads, are assumed to be in cache.  The default value can be thought of
        as modeling random access as 40 times slower than sequential, while
        expecting 90% of random reads to be cached.

effective_cache_size is impossible to set accurately because you have no
idea what other things might be in the cache, or what other concurrent
queries might be filling the cache.  Going with something at least
partly reasonable makes a lot more sense.  While we don't know the size
of RAM, we do know the size of shared_buffers, and keying on that for a
default seems like a no-brainer, rather than using 128MB.

-- 
  Bruce Momjian  <bruce(at)momjian(dot)us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +


In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2013-01-08 23:23:30
Subject: Re: [PATCH 2/5] Make relpathbackend return a statically result instead of palloc()'ing it
Previous:From: Andres FreundDate: 2013-01-08 22:40:29
Subject: Re: [PATCH 2/5] Make relpathbackend return a statically result instead of palloc()'ing it

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group