Re: Auto-tuning work_mem and maintenance_work_mem

On Wed, Oct 9, 2013 at 9:11 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> 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,

I don't think that's true. People set shared_buffers low because when
they set it high, they get write I/O storms that cripple their system
at checkpoint time, or because they need to minimize double-buffering.

> or can change them.

That is obviously true, but it's true now, too.

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

But your auto-tuned value can easily be too low or too high, too.
Consider someone with a system that has 64GB of RAM. EnterpriseDB
has had customers who have found that with, say, a 40GB database, it's
best to set shared_buffers to 40GB so that the database remains fully
cached. Your latest formula will auto-tune work_mem to roughly 100MB.
On the other hand, if the same customer has a 400GB database, which
can't be fully cached no matter what, a much lower setting for
shared_buffers, like maybe 8GB, is apt to perform better. Your
formula will auto-tune shared_buffers to roughly 20MB.

In other words, when there's only 24GB of memory available for
everything-except-shared-buffers, your formula sets work_mem five
times higher than when there's 48GB of memory available for
everything-except-shared-buffers. That surely can't be right.

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

That seems considerably more principled than this patch.

On a more pedestrian note, when I try this patch with shared_buffers =
8GB, the postmaster won't start. It dies with:

FATAL: -20203 is outside the valid range for parameter "work_mem" (-1
.. 2147483647)

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