Re: Auto-tuning work_mem and maintenance_work_mem

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.

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
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
that the correct value has anything to do with the size of
shared_buffers.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company