Re: Auto-tuning work_mem and maintenance_work_mem

On Thu, Oct 17, 2013 at 7:22 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Wed, Oct 16, 2013 at 5:14 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> On 10/16/2013 01:25 PM, Andrew Dunstan wrote:
>>> Andres has just been politely pointing out to me that my knowledge of
>>> memory allocators is a little out of date (i.e. by a decade or two), and
>>> that this memory is not in fact likely to be held for a long time, at
>>> least on most modern systems. That undermines completely my reasoning
>>> above.
>>
>> Except that Opensolaris and FreeBSD still have the old memory allocation
>> behavior, as do older Linux kernels, many of which will remain in
>> production for years. I have no idea what Windows' memory management
>> behavior is.
>>
>> So this is a case of needing to know considerably more than the
>> available RAM to determine a good setting.
>
> I agree, but I still think my previous proposal of increasing the
> defaults for work_mem and maintenance_work_mem by 4X would serve many
> more people well than it would serve poorly. I haven't heard anyone
> disagree with that notion. Does anyone disagree? Should we do it?

One source of hesitation for me is that I have a hunch that the stock
assumptions that go into shared_buffers will probably change, possibly
by a lot. For a lot of (I think very good-) reasons current policy is
to set s_b to max 2GB. After we nail some of the outstanding
contention issues and make other optimizations I expect that the
optimal setting may be 50% of ram or higher.

Point being: I like the idea of an initdb time setting that takes in
the estimated amount of RAM that is going to be dedicated to the
database. From there, we then estimate best settings for the various
configurations (perhaps taking in extra hypothetical parameters to
help that job along). So the anchor should not be s_b, but user
specified.

merlin