Re: Auto-tuning work_mem and maintenance_work_mem

On Wed, Oct  9, 2013 at 08:55:33PM -0400, Robert Haas wrote:
> On Wed, Oct 9, 2013 at 4:10 PM, Bruce Momjian <bruce@momjian.us> wrote:
> > I disagree.  I think we can get a forumla that is certainly better than
> > a fixed value.  I think the examples I have shown do have better value
> > than a default fixed value.  I am open to whatever forumula people think
> > is best, but I can't see how a fixed value is a win in general.
> 
> To really do auto-tuning correctly, we need to add a GUC, or some
> platform-dependent code, or both, for the amount of memory on the
> machine, which is not and should not be assumed to have anything to do
> with shared_buffers, which is often set to very small values like
> 256MB on Windows, and even on Linux, may not be more than 2GB even on
> a very large machine.  With that, we could set a much better value for
> effective_cache_size, and it would help here, too.

If you are setting shared_buffers low, you probably want the others low
too, or can change them.

> I would like to really encourage careful reflection before we start
> making a lot of changes in this area.  If we're going to make a change
> here, let's take the time to try to do something good, rather than
> slamming something through without real consideration.  I still want

We get into the "it isn't perfect so let's do nothing" which is where we
have been for years.  I want to get out of that.

I am not sure how much consideration you want, but I am willing to keep
inproving it.  No value is going to be perfect, even for users who know
their workload.

> to know why this is better than setting work_mem to 4MB and calling it
> good.  I accept that the current default is too low; I do not accept

For servers that are not dedicated, a fixed value can easily be too
large, and for a larger server, the value can easily be too small.   Not
sure how you can argue that a fixed value could be better.

> that the correct value has anything to do with the size of
> shared_buffers.

Well, an open item is to add an available_memory GUC and base everything
on that, including shared_buffers.  That would allow Windows-specific
adjustments for the default.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +