Re: Auto-tuning work_mem and maintenance_work_mem

From: "Robert Haas" <robertmhaas(at)gmail(dot)com>
> On Thu, Oct 10, 2013 at 1:23 AM, Magnus Hagander <magnus(at)hagander(dot)net>
> wrote:
>> I think it would be even simpler, and more reliable, to start with the
>> parameter to initdb - I like that. But instead of having it set a new
>> variable based on that and then autotune off that, just have *initdb*
>> do these calculations you're suggesting, and write new defaults to the
>> files (preferably with a comment).
>>
>> That way if the user *later* comes in and say changes shared_buffers,
>> we don't dynamically resize the work_mem into a value that might cause
>> his machine to die from swapping which would definitely violate the
>> principle of least surprise..
>
> +1 for all of that. I completely agree.

I vote for this idea completely, too. It's nice to be able to specify
usable RAM with something like "initdb --system-memory 8GB", because it
provides flexibility for memory allocation --- use the whole machine for one
PostgreSQL instance, or run multiple instances on one machine with 50% of
RAM for instance-A and 25% of RAM for instance B and C, etc. But what is
the default value of --system-memory? I would like it to be the whole RAM.

I hope something like pgtune will be incorporated into the core, absorbing
the ideas in:

- pgtune
- https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
- the book "PostgreSQL 9.0 High Performance" by Greg Smith

Then initdb calls the tool. Of course, DBAs can use the tool later. Like
pgtune, the tool would be nice if it and initdb can accept "--system-type"
or "--workload" with arguments {OLTP | DW | mixed}.

Regards
MauMau