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(at)momjian(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +